MySQL: How to create trigger for setting creation date for new rows

Question

I ran into a problem as I tried to create two TIMESTAMP columns in my database. One called created and one called updated. I figured it would be easy to set the default value of both to CURRENT_TIMESTAMP and then ON UPDATE CURRENT_TIMESTAMP for the updated column. But for some reason MySQL means that's a bad idea... so I have been looking for ways to do this without having to set one of them in the insert query.

I found one way by using a trigger in this answer, but I keep getting errors. I just managed to actually create the trigger, but now I get errors when I try to insert new rows claiming that

1442 - Can't update table 'tasks' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

And I don't get what that means at all. So, I was hoping someone here could shed some light up this subject.

The SQL I used to create the table and the trigger is as follows:

CREATE TABLE `tasks` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` DATETIME,
  `updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `title` VARCHAR(255) NOT NULL,
  `notes` TEXT,
  `status_id` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `status_id` (`status_id`),
  CONSTRAINT `fk_tasks_statuses` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TRIGGER task_creation_timestamp AFTER INSERT ON tasks 
FOR EACH ROW
UPDATE tasks SET created = updated WHERE id = NEW.id;

What am I doing wrong here?

Answer 1

Your trigger needs to be "before insert", and you need to use SET instead of UPDATE:

CREATE TRIGGER task_creation_timestamp BEFORE INSERT ON tasks 
FOR EACH ROW
SET NEW.created = NOW();