CODEWITHSUNDEEP
mysqltimestampsql-update
ThomasP
ThomasP

Reputation: 93

mysql: how to update automatically TimeStamp when a field has been updated

I have a simple DB-table with few fields for articles database among that is a TimeStamp containing the time of the last update made on the article.

I use "ON UPDATE CURRENT_TIMESTAMP"

Problem is that I want to update only if some fields of this record have changed, not all.

i.e. For each article, I have a field giving the number of time the article has been read by visitors. must not be updated after each web visit... only if I change the title, authors, etc...

Ideally with a nice mysql command...

I have coded the following:

CREATE TRIGGER `actu_lastupdate_date` BEFORE UPDATE ON `actuality`
 FOR EACH ROW BEGIN
    if NEW.title <> OLD.title OR NEW.chapeau = OLD.chapeau OR NEW.content = OLD.content
    then
        SET NEW.LastUpdate_date = current_timestamp;
    end if;
END

it seems that even if another record field is changing, the Timestamp is updated. There should be something bad somewhere. I'll look into it

Upvotes: 2

Views: 369

Answers (1)

juergen d
juergen d

Reputation: 204746

You could write a trigger to add the timestamp on updates 

delimiter |
CREATE TRIGGER actu_lastupdate_date AFTER UPDATE on actuality
FOR EACH ROW
BEGIN
    if (NEW.title <> OLD.title 
         OR NEW.chapeau <> OLD.chapeau 
         OR NEW.content <> OLD.content)
        AND (NEW.other_column = OLD.other_column 
         OR NEW.other_column2 = OLD.other_column2)
    then
        SET NEW.LastUpdate_date = current_timestamp;
    end if;
END
|
delimiter ;

Upvotes: 2

Related Questions