CODEWITHSUNDEEP
mysqlsql
Ashis Biswas
Ashis Biswas

Reputation: 767

Time stamp automatically update when update any field in mysql

suppose i have a table called user like this

╔════╦══════════╦═════════════════════╗
║ id ║   key    ║       keytime       ║
╠════╬══════════╬═════════════════════╣
║  1 ║ somekey1 ║ 2016-01-01 15:39:43 ║
║  2 ║ somekey2 ║ 2016-01-02 15:39:43 ║
╚════╩══════════╩═════════════════════╝

the keytime field type is TIMESTAMP and default value is CURRENT_TIMESTAMP

How to update keytime field automatically when I update key field

UPDATE `user` 
SET `Key` = '2' 
WHERE `id` = 1;

Upvotes: 1

Views: 37

Answers (2)

juergen d
juergen d

Reputation: 204924

Use

ALTER TABLE `user` 
   MODIFY keytime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

to make it update automatically.

See the doc

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176284

You could use BEFORE UPDATE trigger:

CREATE TABLE `user`(id INT AUTO_INCREMENT PRIMARY KEY,
                   `key` VARCHAR(100),
                   keytime TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

INSERT INTO `user`(`key`, keytime) VALUES ('key', CURRENT_TIMESTAMP - 10000);

CREATE TRIGGER user_update 
BEFORE UPDATE ON `user` 
FOR EACH ROW 
   SET NEW.keytime = CURRENT_TIMESTAMP;

UPDATE `user` 
SET `Key` = '2' 
WHERE `id` = 1;

SqlFiddleDemo

Upvotes: 1

Related Questions