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屋！