CODEWITHSUNDEEP
mysqltriggers
Rüdiger
Rüdiger

Reputation: 943

Trigger for inserting/updating current date running into error MySQL

I'd like to create an update/insert trigger for the current date (to save a timestamp when it got modified last). So, since I read that you cannot define an update/insert trigger, but I need to define two triggers for this, I started off with an insert-trigger.

I do have the following code:

drop table if exists test;
CREATE TABLE IF NOT EXISTS test (
    id int,
    mydate datetime not null default now(),
    ts datetime
);
DELIMITER $$
DROP TRIGGER IF EXISTS update_test_timestamp;
CREATE TRIGGER `update_test_timestamp` 
AFTER INSERT ON `test` 
FOR EACH ROW  
BEGIN  
    UPDATE `test` SET `ts` = CURRENT_TIMESTAMP WHERE `id` = NEW.id;
END $$
DELIMITER ;
INSERT INTO test (id) VALUES (1);

Basically, I want to save a timestamp on creation and an "lastupdate"-timestamp (ts). Sadly he error:

Error Code: 1442. Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

What did I do wrong? When I try to modify it to an update-trigger (instead of insert) I get the same problem.

Upvotes: 2

Views: 2497

Answers (1)

Krish
Krish

Reputation: 5917

You don't need the UPDATE clause. You can just use

SET NEW.ts= CURRENT_TIMESTAMP();

Ideally if you would like to have automatic timestamps for each updates, you could modify your table like this.

ALTER TABLE `test` 
CHANGE COLUMN `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;

which converts your ts field to a timestamp field and saves the timestamp whenever you update any field in your table.

Upvotes: 2

Related Questions