MySQL:如何创建触发器以设置新行的创建日期 [英] MySQL: How to create trigger for setting creation date for new rows
问题描述
尝试在数据库中创建两个TIMESTAMP列时遇到问题.一个称为created
,另一个称为updated
.我认为将updated
列的默认值都分别设置为CURRENT_TIMESTAMP
和ON UPDATE CURRENT_TIMESTAMP
会很容易.但是出于某种原因,MySQL意味着这是一个坏主意……因此,我一直在寻找无需在插入查询中设置其中一种方法的方法.
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-无法更新存储的函数/触发器中的表任务",因为调用该存储的函数/触发器的语句已使用该表.
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.
我用来创建表和触发器的SQL如下:
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;
我在做什么错了?
推荐答案
您的触发器需要在插入之前",并且您需要使用SET
而不是UPDATE
:
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();
这篇关于MySQL:如何创建触发器以设置新行的创建日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!