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?
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();