Question

mysql load data 后，触发器对重复插入的数据进行删除操作，可是出现1442 - Can't update，如何解决？

触发器报错，1442 - Can't update table 'consum' in stored function/trigger because it is already used by statement

Answer 1

由于 MySQL 没有直接抛出异常的语句
因此这里通过在触发器里面，插入/删除自己这个表
导致 MySQL 发生异常

发生异常了, 就会自动回滚掉 删除数据的处理了.

一个例子

货物表
CREATE TABLE Goods(
id INT,
Amount INT
);

订单表
CREATE TABLE OrderDetail(
ID INT,
GoodsID INT,
Amount INT
);

库存测试数据：
INSERT INTO Goods VALUES (1, 100);

要求：当订单表插入数据的时候，自动去货物表检查，是否有足够的库存
如果有，那么更新货物表的库存 = 原库存– 本次订单数量
如果库存不足，抱错返回.

DELIMITER //

CREATE TRIGGER BeforeInsertOrderDetail
BEFORE INSERT ON OrderDetail
FOR EACH ROW
BEGIN
DECLARE
v_nowCount INT;

SELECT
Amount INTO v_nowCount
FROM
Goods
WHERE
ID = new.GoodsID;

IF v_nowCount - new.Amount < 0 THEN
-- 由于 MySQL 没有直接抛出异常的语句
-- 因此这里通过在触发器里面，插入/删除自己这个表
-- 导致 MySQL 发生异常
DELETE FROM
OrderDetail
WHERE
ID = new.GoodsID;
ELSE
UPDATE
Goods
SET
Amount = Amount - new.Amount
WHERE
ID = new.GoodsID;
END IF;
END;
//
DELIMITER ;

处理前
mysql> select * from goods;
+------+--------+
| id | Amount |
+------+--------+
| 1 | 100 |
+------+--------+
1 row in set (0.00 sec)

mysql> select * from OrderDetail;
Empty set (0.00 sec)

mysql> INSERT INTO OrderDetail VALUES(1, 1, 90);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO OrderDetail VALUES(1, 1, 20);
ERROR 1442 (HY000): Can't update table 'orderdetail' in stored function/trigger
because it is already used by statement which invoked this stored function/trigg
er.

处理后

mysql> select * from goods;
+------+--------+
| id | Amount |
+------+--------+
| 1 | 10 |
+------+--------+
1 row in set (0.00 sec)

mysql> select * from OrderDetail;
+------+---------+--------+
| ID | GoodsID | Amount |
+------+---------+--------+
| 1 | 1 | 90 |
+------+---------+--------+
1 row in set (0.00 sec)

Answer 2

触发器不能对本表触发，insert、update操作。但操作前可以用set赋值。参考我的CSDN博客