MySQL 触发器为 NEW 行设置值并更新同一表中的另一个 [英] MySQL trigger set values for NEW row and update another in the same table

问题描述

我有一张表格，用于记录特定项目的费用.这些费用会随着时间的推移而变化，所以我有两列(startDate、endDate)，当前的费用在遥远的将来总是有一个 endDate.我已经有一个触发器，我用它来对输入的新行进行一些计算，但我还希望发生的是，如果我输入一个已经有条目的项目，我想将前一个条目的 endDate 设置为前一天新条目的 startDate 和新的 endDate 到预定的遥远日期.这是我首先尝试的代码:

I have a table that I keep track of fees for a specific item. These fees can change over time so I have two columns (startDate, endDate) with the current set of fees always having an endDate in the far future. I already have a trigger that I use to do some calculations on the new row being entered but what I also want to have happen is if I enter an item that already has an entry I want to set the previous entry's endDate to the day before the new entry's startDate and the new endDate to a predetermined far-away date. Here is the code for what I tried first:

CREATE
DEFINER=`root`@`%`
TRIGGER `im`.`splitBeforeIns`
BEFORE INSERT ON `im`.`split`
FOR EACH ROW
BEGIN
    SET NEW.tcPercent = (NEW.tcOfficeFee / NEW.globalFee) * 100 , NEW.proPercent = 100 - NEW.tcPercent, NEW.endDate = 20501231;
    UPDATE im.split set endDate = ADDDATE(NEW.startDate, -1) where procKey = NEW.procKey AND endDate = 20501231;
END$$

我得到的错误是:

ERROR 1442: Can't update table 'split' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

推荐答案

这个问题的答案可能不受欢迎，但它是:你不能那样做.

The answer to this might be unwelcome, but it is: You can't do that.

触发器不能更新同一表的另一行，因为该行是从该行调用的.

A Trigger can't update another row of the same table as the row, the trigger was called from.

这样做的典型方法是创建一个存储过程，插入/更新目标表，然后更新其他行，所有这些都在一个事务中.

The typical way to do that, is to create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

这篇关于MySQL 触发器为 NEW 行设置值并更新同一表中的另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！