MySQL

SQL – trigger, update timestamp when row is updated.

Ref

Sometime we need to update timestamp-column when row is changed. there are a lot of solution that you do. figure out about it…
Like this.
one way to just do updating sql or other way to use trigger.
I am going to tell you about trigger is good sometime is not.

I got following post from stackoverflow, some people uploaded
In Mysql, do like the following thing.

create table ab (
  id int,
  changeTimestamp timestamp
    NOT NULL
    default CURRENT_TIMESTAMP
    on update CURRENT_TIMESTAMP
);

but In PostgreSQL is not worked. so there is other solution i got from stackoverflow. you can refer the following thing.
comment
Create a function that updates the changetimestamp column of a table like so:

CREATE OR REPLACE FUNCTION update_changetimestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.changetimestamp = now();
   RETURN NEW;
END;
$$ language 'plpgsql';

Create a trigger on the table that calls the update_changetimestamp_column() function whenever an update occurs like so:

CREATE TRIGGER update_ab_changetimestamp BEFORE UPDATE
ON ab FOR EACH ROW EXECUTE PROCEDURE
update_changetimestamp_column();

That’s the solution i got. if you need more information, just refer the top of url. thanks for reading.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다

Back To Top