MySQL 触发器为 NEW 行设置值并更新同一表中的另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11247590/
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 trigger set values for NEW row and update another in the same table
提问by donbyte
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:
我有一张表格，用于记录特定项目的费用。这些费用会随着时间的推移而变化，所以我有两列（startDate、endDate），当前的费用在遥远的将来总是有一个 endDate。我已经有一个触发器，用于对输入的新行进行一些计算，但我还希望发生的是，如果我输入一个已经有条目的项目，我想将前一个条目的 endDate 设置为前一天新条目的 startDate 和新的 endDate 到预定的遥远日期。这是我首先尝试的代码：
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$$
The error I get is:
我得到的错误是：
ERROR 1442: Can't update table 'split' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
回答by Eugen Rieck
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 tableas 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.
这样做的典型方法是创建一个存储过程，插入/更新目标表，然后更新其他行，所有这些都在一个事务中。
回答by RolandoMySQLDBA
If you have a UNIQUE KEY defined on (procKey,EndDate), then perhaps you can remove the second line of the trigger. Also remove the hardcoded date from the trigger.
如果您在 (procKey,EndDate) 上定义了唯一键，那么也许您可以删除触发器的第二行。还要从触发器中删除硬编码的日期。
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;
END$$
and do an INSERT ON DUPLICATE KEY UPDATElike this:
并像这样在 DUPLICATE KEY UPDATE 上插入：
INSERT INTO im.split ...
ON DUPLICATE KEY UPDATE
endDate = ADDDATE(startDate, -1);
You may also want to define endDate in im.split as follows
您可能还想在 im.split 中定义 endDate 如下
enddate DATE DEFAULT '2050-12-31'
回答by user3008781
I managed to get it to work, by creating a "wrapper table" which is defined with the same fields as your target table, but by using the FEDERATED storage engine. The federated server I defined to target the same mysql server/itself, so at "localhost". The trigger I then made change a row in the wrapper table. Of course you have to be very sure what you do in the trigger, to avoid a recursive loop. Also potentially the performance isn't that good; I haven't tested performance, but it has been working for years in a production environment.
我设法通过创建一个“包装表”来使其工作，该表使用与目标表相同的字段定义，但使用 FEDERATED 存储引擎。我定义的联合服务器以相同的 mysql 服务器/本身为目标，所以在“localhost”。然后我所做的触发器更改了包装表中的一行。当然，您必须非常确定您在触发器中做什么，以避免递归循环。也可能性能不是那么好；我还没有测试过性能，但它已经在生产环境中工作了多年。
回答by Brian Driscoll
From the MySQL Docs:
来自MySQL 文档：
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
在存储的函数或触发器中，不允许修改已被调用函数或触发器的语句使用（用于读取或写入）的表。
You will need to find some other way of doing what you're trying to do.
您将需要找到一些其他方式来做您想做的事情。
