in 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.

Write a Comment

Comment