MySQL触发器：为新行设置值并在同一表中更新另一个

Question

我有一个表格，用于记录特定项目的费用。这些费用可能随时间而变化，因此我有两列（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$$

我收到的错误信息是：

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

- donbyte

---

@BrianDriscoll 我试过了 :-( 我得到了相同的错误。 - donbyte

问题在于你无法在触发器内显式更新触发器的目标。你可能需要找到另一种方法来完成任务。 - Brian Driscoll

请展示以下内容：1）SHOW CREATE TABLE im.split\G和2）向im.split插入样例命令。 - RolandoMySQLDBA

@RolandoMySQLDBA 我不确定这有什么帮助……根据下面的答案，使用触发器无法更新同一表中的另一行。您认为这可能吗？ - donbyte

这是一个问题：在表中，prockey和endDate形成唯一键吗？ - RolandoMySQLDBA

Answer 1

这个答案可能不是你想听的，但事实是：你无法做到这件事。

触发器不能更新调用该触发器所在行的同一张表中的另一行。

通常的方法是创建一个存储过程，在一个事务中插入/更新目标表，然后再更新其他行。

- Eugen Rieck

---

谢谢您提供的方法，让我达到了我的目标。 - donbyte

Answer 2

如果您在(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$$

并且执行这样的INSERT ON DUPLICATE KEY UPDATE：

INSERT INTO im.split ...
ON DUPLICATE KEY UPDATE
endDate = ADDDATE(startDate, -1);

您可能想要在im.split中定义endDate，方式如下：

enddate DATE DEFAULT '2050-12-31'

- RolandoMySQLDBA

Answer 3

我成功地使它工作，方法是创建一个“包装表”，该表使用FEDERATED存储引擎定义了与目标表相同的字段，但它指向的是同一台mysql服务器/本身，即“localhost”。然后我定义用于操作“包装表”的触发器。当然，在触发器中所做的操作必须非常确定，以避免递归循环。可能性能不是很好，但在生产环境中已经可靠运行多年。

- user3008781

Answer 4

来自MySQL文档：

在存储函数或触发器中，不允许修改已被调用该函数或触发器的语句所使用（读取或写入）的表。

您需要找到其他方法来完成您想要做的事情。

- Brian Driscoll