2

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.

Rüdiger
  • 893
  • 5
  • 27
  • 56

1 Answers1

2

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.

Krish
  • 5,917
  • 2
  • 14
  • 35
  • Thanks for that! That's a solution for MySQL 5.6.5 and later, but if I am running prior versions, [I cannot define multiple columns with default/on update statements when it comes to timestamps](https://stackoverflow.com/questions/4489548/why-there-can-be-only-one-timestamp-column-with-current-timestamp-in-default-cla) - which is why i am actually having this trigger problem. Do you also have a solution for that? – Rüdiger Feb 20 '19 at 08:39
  • 1
    what's wrong with `SET NEW.ts= now();` in your trigger. that should solve your update issue. – Krish Feb 20 '19 at 09:01
  • 1
    I am dumb, it indeed worked out, I just had to change the "AFTER" to "BEFORE" in my trigger and apply the changes you suggested. Thanks! – Rüdiger Feb 20 '19 at 09:12