MySQL：如何创建触发器以设置新行的创建日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2906978/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: How to create trigger for setting creation date for new rows
提问by Svish
I ran into a problem as I tried to create two TIMESTAMP columns in my database. One called createdand one called updated. I figured it would be easy to set the default value of both to CURRENT_TIMESTAMPand then ON UPDATE CURRENT_TIMESTAMPfor the updatedcolumn. 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.
当我试图在我的数据库中创建两个 TIMESTAMP 列时遇到了一个问题。一个叫created一个叫updated。我认为很容易将两者的默认值设置为CURRENT_TIMESTAMP和 然后ON UPDATE CURRENT_TIMESTAMP为该updated列设置。但出于某种原因，MySQL 意味着这是一个坏主意......所以我一直在寻找方法来做到这一点，而不必在插入查询中设置其中之一。
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.
1442 - 无法更新存储函数/触发器中的表“任务”，因为它已被调用此存储函数/触发器的语句使用。
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:
我用来创建表和触发器的SQL如下：
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?
我在这里做错了什么？
回答by Ike Walker
Your trigger needs to be "before insert", and you need to use SETinstead of UPDATE:
您的触发器需要在“插入之前”，并且您需要使用SET而不是UPDATE：
CREATE TRIGGER task_creation_timestamp BEFORE INSERT ON tasks
FOR EACH ROW
SET NEW.created = NOW();
