今天在寫一個觸發器時遇到一個死鎖問題，但不知道問題出在哪兒，這個觸發器主要是用處是在T_BS_ORDERDETAIL表做增刪改操作時給主表T_BS_ORDERINFO重新合計重量體積及數量CREATE OR REPLACE TRIGGER TRIGGER_ORDERDETAIL
AFTER DELETE OR INSERT OR UPDATE ON T_BS_ORDERDETAIL FOR EACH ROWDECLARE
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;新增時沒報錯，但修改時就報以下錯誤
1 回答
長風秋雁
TA貢獻1757條經驗 獲得超7個贊
CREATE OR REPLACE TRIGGER TRIGGER_ORDERDETAIL AFTER DELETE OR INSERT OR UPDATE ON T_BS_ORDERDETAIL FOR EACH ROWBEGIN 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 關注
- 1182 瀏覽
添加回答
舉報
0/150
提交
取消