插入行后触发更新两列的操作时出现MySQL错误1442 [英] Mysql error 1442 when triggering an action to update two columns after inserting a row

问题描述

在将数据（例如，日期）插入 event_date 列后，我想要一个触发器来更新同一表的两列并为'event_quarter的相应值行'和'event_year'。

After inserting data, for example, a date into a event_date column, I want a trigger to update two columns of that same table and row the corresponding values of 'event_quarter' and 'event_year'.

我有这个测试表，其中有四列：

I have this test table with four columns:

id ， event_date ， event_quarter ， event_year

我一直在努力使以下代码起作用：

I have been struggling with this code to make it work:

DELIMITER $$

CREATE TRIGGER `fill_event_quarter_and_year`
   AFTER INSERT
   ON `test_table`
   FOR EACH ROW
BEGIN
   DECLARE event_date_   DATE;

   SELECT event_date
     INTO event_date_
     FROM `test_table`
    WHERE NEW.id = id;

   UPDATE `test_table`
      SET event_quarter = QUARTER(event_date_), event_year = YEAR(event_date_)
    WHERE NEW.id = id;
END
$$  

但是，出现以下错误：

1442-无法更新存储函数/触发器中的表 test_table，因为调用该存储语句的语句已使用该表

函数/触发器

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

function / trigger

我该如何解决？

然后看来我必须做两个触发器，一个为此 INSERT 操作，另一个为 UPDATE ing操作。对？

Then it looks like I have to make two triggers, one for this INSERTing action and another one for UPDATEing action. Right? How would that be when updating a specific row or record?

还是可能触发更新和插入动作的触发器？

Or is it possible a trigger for both updating and inserting actions?

推荐答案

在触发器中，您无法将要更改的表用于其他查询，这尤其意味着您无法更改或读取该表的其他行。但是您实际上可以更改当前正在更新/插入的行。

In a trigger, you cannot use the table you are changing for another query, this especially means that you cannot change or read other rows of that table. But you can actually change the row you are currently updating/inserting.

该行称为 new ，并且您可以对其进行读写，但是必须使用语法 set new.columnname = ... ，而无需 update 。必须在触发之前完成。

That row is refered to as new, and you can read and write to it, but you have to use the syntax set new.columnname = ..., without update. This has to be done in a before-trigger.

您不能将的触发器组合在一起更新和插入，因此您必须创建两个，但是可以对两个使用相同的代码：

You cannot combine triggers for update and insert, so you have to create two, but you can use the same code for both:

CREATE TRIGGER `trbins_fill_event_quarter_and_year`
BEFORE INSERT ON `test_table`
FOR EACH ROW
  set new.event_quarter = QUARTER(new.event_date), 
      new.event_year = YEAR(new.event_date);

CREATE TRIGGER `trbupd_fill_event_quarter_and_year`
BEFORE UPDATE ON `test_table`
FOR EACH ROW
  set new.event_quarter = QUARTER(new.event_date), 
      new.event_year = YEAR(new.event_date);

这篇关于插入行后触发更新两列的操作时出现MySQL错误1442的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！