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

由于 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 BeforeInsertOrderDetailBEFORE INSERT ON OrderDetailFOR EACH ROWBEGINDECLAREv_nowCount INTSELECTAmount INTO v_nowCountFROMGoodsWHEREID = new.GoodsIDIF v_nowCount - new.Amount <0 THEN-- 由于 MySQL 没有直接抛出异常的语句-- 因此这里通过在触发器里面，插入/删除自己这个表-- 导致 MySQL 发生异常DELETE FROMOrderDetailWHEREID = new.GoodsIDELSEUPDATEGoodsSETAmount = Amount - new.AmountWHEREID = new.GoodsIDEND IFEND//DELIMITER 处理前mysql>select * from goods+------+--------+| id | Amount |+------+--------+| 1 | 100 |+------+--------+1 row in set (0.00 sec)mysql>select * from OrderDetailEmpty 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/triggerbecause it is already used by statement which invoked this stored function/trigger.处理后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)

MYSQL没有TRIGGER回滚 create trigger update_exceed BEFORE INSERT on A

for each row

begin

select count(*) into @ee from A where UserID=new.UserID

if @ee>=5 then

insert into A(id) values(0)

end if

end

加粗部分,就是拦截部分。由于MYSQL在触发器内不允许对自身的修改，所以会产生一个1442的错误,插入失败。

MYSQL没有TRIGGER回滚 create trigger update_exceed BEFORE INSERT on Afor each rowbegin select count(*) into @ee from A where UserID=new.UserID if @ee>=5 then insert into A(id) values(0) end ifend 加粗部分,就是拦截部分。由于MYSQL在触发器内不允许对自身的修改，所以会产生一个1442的错误,插入失败。</SPAN>

---