CODEWITHSUNDEEP
mysql
kapad
kapad

Reputation: 651

MySQL on update current_timestamp not working

I have a mysql table with a column updated_at default current_timestamp on update current_timestamp.

The value for updated_at gets updated whenever I update the row with a query (directly or through php code) but whenever the row is updated by a trigger from some other table the updated_at value does not change. I want that this also to happen.

Any suggestions? reasons for this problem?

The relevant row of table1

Field             | Type             | Null | Key | Default           | Extra                       
updated_at        | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP

The trigger in table2 updates some coloumns in table1 whenever a certain coloumn2 in table2 is updated. NOTE: the timestamp is not updated in the trigger. I expect MySQL to do that automatically.

Also I am using a Master-Slave replication and the problem is on both the master and the slave. The logging format for master slave replication is statement logging.

Upvotes: 0

Views: 3947

Answers (1)

Sampo Sarrala
Sampo Sarrala

Reputation: 4868

Here is simple example for timestamp update with triggers:

First you should have two (simple) tables:

Table named TriggerTable

#Field     #Type         #Function
 1  id      int(11)       AUTO_INCREMENT 
 2  data    varchar(52)

Table named UpdatesHere

#Field     #Type         #Function
 1  id      int(11)       AUTO_INCREMENT 
 2  time    timestamp     NOW(), on update NOW()

Then comes TRIGGER

Add trigger that executes on every TriggerTable update: 

CREATE TRIGGER `TriggerTableOnUpdate` 
    AFTER UPDATE ON `TriggerTable` 
    FOR EACH ROW 
    BEGIN 
        UPDATE `UpdatesHere` SET `time`=NOW() WHERE `id`=NEW.id; 
    END

After that if you do update in TriggerTable it should update UpdatesTable too. It simply updates column with same id.

For example:

UPDATE  `TriggerTable` SET `data`='Hello World' WHERE `id`=1;

will update column time in row with id 1 at table UpdatesHere.


Note: Change your delimiter with DELIMITER [delimiter] when defining triggers.

Upvotes: 1

Related Questions