对不起我的英语不好。
我有2张桌子:
Table1
id
table2_id
num
modification_date
和
Table2
id
table2num
我想打一个触发器,它插入或删除操作后在Table1更新的最后一个值num的Table2.table1lastnum。
我的触发器:
CREATE OR REPLACE TRIGGER TABLE1_NUM_TRG
AFTER INSERT OR DELETE ON table1
FOR EACH ROW
BEGIN
IF INSERTING then
UPDATE table2
SET table2num = :new.num
WHERE table2.id = :new.table2_id;
ELSE
UPDATE table2
SET table2num = (SELECT num FROM (SELECT num FROM table1 WHERE table2_id = :old.table2_id ORDER BY modification_date DESC) WHERE ROWNUM <= 1)
WHERE table2.id = :old.table2_id;
END IF;
END TABLE1_NUM_TRG;
但删除后Table1我有错误:
ORA-04091: table BD.TABLE1 is mutating, trigger/function may not see it
ORA-06512: at "BD.TABLE1_NUM_TRG", line 11
ORA-04088: error during execution of trigger 'BD.TABLE1_NUM_TRG'
我究竟做错了什么?
相关分类