oracle执行触发器时报死锁错误
0
[已解决问题]
解决于 2018-08-30 09:23
今天在写一个触发器时遇到一个死锁问题，但不知道问题出在哪儿，这个触发器主要是用处是在
T_BS_ORDERDETAIL表做增删改操作时给主表
T_BS_ORDERINFO重新合计重量体积及数量
CREATE OR REPLACE TRIGGER TRIGGER_ORDERDETAIL AFTER DELETE OR INSERT OR UPDATE ON T_BS_ORDERDETAIL FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN UPDATE T_BS_ORDERINFO A SET A.N_ZL = (SELECT SUM(B.N_ZL) FROM T_BS_ORDERDETAIL B WHERE B.I_ID_ORDER = :NEW.I_ID_ORDER), A.N_TJ = (SELECT SUM(B.N_TJ) FROM T_BS_ORDERDETAIL B WHERE B.I_ID_ORDER = :NEW.I_ID_ORDER), A.I_ALLCOUNT = (SELECT SUM(B.I_COUNT) FROM T_BS_ORDERDETAIL B WHERE B.I_ID_ORDER = :NEW.I_ID_ORDER) WHERE A.ID = :NEW.I_ID_ORDER; COMMIT; END;
新增时没报错，但修改时就报以下错误
最佳答案
0
对数据库方面知识还是太菜，最后发现问题并没有那么复杂，只是自己触发器语句没写对而已，惭愧啊，改成这样就没问题了
CREATE OR REPLACE TRIGGER TRIGGER_ORDERDETAIL AFTER DELETE OR INSERT OR UPDATE ON T_BS_ORDERDETAIL FOR EACH ROW BEGIN IF INSERTING THEN UPDATE T_BS_ORDERINFO A SET A.N_ZL=A.N_ZL+:NEW.N_ZL, A.N_TJ=A.N_TJ+:NEW.N_TJ, A.I_ALLCOUNT=A.I_ALLCOUNT+:NEW.I_COUNT WHERE A.ID=:NEW.I_ID_ORDER; END IF; IF UPDATING THEN UPDATE T_BS_ORDERINFO A SET A.N_ZL=A.N_ZL-:OLD.N_ZL+:NEW.N_ZL, A.N_TJ=A.N_TJ-:OLD.N_TJ+:NEW.N_TJ, A.I_ALLCOUNT=A.I_ALLCOUNT-:OLD.I_COUNT+:NEW.I_COUNT WHERE A.ID=:NEW.I_ID_ORDER; END IF; IF DELETING THEN UPDATE T_BS_ORDERINFO A SET A.N_ZL=A.N_ZL-:OLD.N_ZL, A.N_TJ=A.N_TJ-:OLD.N_TJ, A.I_ALLCOUNT=A.I_ALLCOUNT-:OLD.I_COUNT WHERE A.ID=:OLD.I_ID_ORDER; END IF; END;
其他回答(1)
0
