MySQL - 无法更新存储函数/触发器中的表，因为它已被调用此存储函数/触发器的语句使用(选择)

Question

我是MySQL的新手，正在学习触发器。我有2个我想要的表:当一个表(detail_transaction)被插入时，另一个表(item)的'stock'字段发生了变化。

• "产品"表

<表类> id 名称 价格 股票 tbody><<tr>1Item_A159002Item_B9500

首先(而且固执己见):触发器很难调试、测试和维护。包含触发器的系统确实难以调试，因为它们引入了副作用——"我在这个表上做了X，然后在另一个表上发生了Y"。作为一名开发人员，你必须把所有的触发器都记在脑子里，才能理解单个语句可能做什么。

如果我们以你的例子为例，你可能有一个触发器"stock"字段创建一个采购订单记录，以便在库存低于阈值时补充库存。purchase order表可能有一个插入触发器来创建accounts payable中的记录，如果给定供应商的总余额超过阈值，CC_3可能有一个插入触发器来拒绝记录。该触发器链实现了有效的业务逻辑，但是当由于产品供应商超出付款限制而突然拒绝插入detail_transaction时，会导致非常复杂的调试过程。(是的，我见过这种情况!)。

触发器的挑战之一是数据库引擎不希望发生无限循环，或者让您正在选择的字段的值因触发器触发而更改。

同样，您不需要连接-您可以从NEW中获取值。

DELIMITER $$
CREATE TRIGGER update_stock
AFTER INSERT
ON detail_transaction 
FOR EACH ROW
BEGIN
UPDATE item
SET stock = stock - NEW.count
WHERE item.id = NEW.id_item;
END$$
DELIMITER ;

方法是使用一个变量:

SET @PRICE = ((SELECT price FROM item WHERE item.ID = 1) * 10);

INSERT INTO detail_transaction (id, id_item, count, total_price)
VALUES (2, 1, 10, @PRICE);
SELECT * from item;

看到小提琴。

EDIT-其他一些答案显示了一个更简单的解决方案-计算触发器中的总价格。

理性的人对如何使用触发器持不同意见——但我建议使用触发器来计算派生值——"给定商品的总库存"，或"交易的总价格";——通常是个坏主意。您实际上是在复制数据—一项商品的总库存水平是一行中的交易、和属性的总和。总价是"price * quantity"、和中的一个属性。如果有人执行total_price或total_stock的更新语句(有意或作为bug的一部分)，会发生什么?哪个值是正确的?

您不应该将insert. values和insert. select混合使用。我会将insert重写为

INSERT INTO detail_transaction (id, id_item, count, total_price)
select 2, 1, 10,  price * 10 
FROM item 
WHERE item.ID = 1;

虽然我的选择是插入前触发

DELIMITER $$
CREATE TRIGGER update_stock before INSERT  ON detail_transaction 
FOR EACH ROW
BEGIN
set new.total_price = (
select item.price * new.count 
FROM item 
WHERE item.ID = new.id
);
END$$
DELIMITER ;

插入

INSERT INTO detail_transaction (id, id_item, count, total_price)
VALUES (2, 1, 10, null);

由于使用多表更新调用触发触发器的表而导致after insert publish失败，这是不允许的，此问题的解决方法见前面的答案。

CREATE TABLE item (
`id` INTEGER AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255),
`price` INTEGER,
`stock` INTEGER
);
INSERT INTO item VALUES
('1', 'Item_A', '15', '900'),
('2', 'Item_B', '9', '500');
SELECT * FROM item;
CREATE TABLE detail_transaction (
`id` INTEGER AUTO_INCREMENT PRIMARY KEY,
`id_item` INTEGER,
`count` INTEGER,
`total_price` INTEGER,
FOREIGN KEY (`id_item`) REFERENCES `item` (`id`)
);
INSERT INTO detail_transaction VALUES
('1', '1', '5', '75');
SELECT * FROM detail_transaction;

900500