猿问

插入或删除后的Oracle触发器

对不起我的英语不好。


我有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'

我究竟做错了什么?


慕沐林林
浏览 1299回答 2
2回答
随时随地看视频慕课网APP

相关分类

Oracle
我要回答