插入或删除后Oracle触发器 [英] Oracle trigger after insert or delete
问题描述
对不起，我的英语.
我有2张桌子:
Table1
id
table2_id
num
modification_date
和
Table2
id
table2num
我想创建一个触发器，该触发器在Table1中插入或删除后更新Table2.table1lastnum中的最后一个值num.
我的触发器
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'
我在做什么错了?
您遇到的是经典的变异表"异常.在ROW触发器中，Oracle不允许您对在其上定义了触发器的表运行查询-因此，对触发器中DELETING部分中的TABLE1的SELECT造成了此问题.
有两种方法可以解决此问题.在这种情况下，最好的方法是使用复合触发器，该触发器类似于:
CREATE OR REPLACE TRIGGER TABLE1_NUM_TRG
FOR INSERT OR DELETE ON TABLE1
COMPOUND TRIGGER
TYPE NUMBER_TABLE IS TABLE OF NUMBER;
tblTABLE2_IDS NUMBER_TABLE;
BEFORE STATEMENT IS
BEGIN
tblTABLE2_IDS := NUMBER_TABLE();
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF INSERTING THEN
UPDATE TABLE2 t2
SET t2.TABLE2NUM = :new.NUM
WHERE t2.ID = :new.TABLE2_ID;
ELSIF DELETING THEN
tblTABLE2_IDS.EXTEND;
tblTABLE2_IDS(tblTABLE2_IDS.LAST) := :new.TABLE2_ID;
END IF;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
IF tblTABLE2_IDS.COUNT > 0 THEN
FOR i IN tblTABLE2_IDS.FIRST..tblTABLE2_IDS.LAST LOOP
UPDATE TABLE2 t2
SET t2.TABLE2NUM = (SELECT NUM
FROM (SELECT t1.NUM
FROM TABLE1 t1
WHERE t1.TABLE2_ID = tblTABLE2_IDS(i)
ORDER BY modification_date DESC)
WHERE ROWNUM = 1)
WHERE t2.ID = tblTABLE2_IDS(i);
END LOOP;
END IF;
END AFTER STATEMENT;
END TABLE1_NUM_TRG;
复合触发器允许处理每个定时点(BEFORE STATEMENT，BEFORE ROW，AFTER ROW和AFTER STATEMENT).请注意，始终按给定的顺序调用时间点.当执行适当的SQL语句(即INSERT INTO TABLE1或DELETE FROM TABLE1)并触发此触发器时，要调用的第一个计时点将是BEFORE STATEMENT，并且BEFORE STATEMENT处理程序中的代码将分配一个PL/SQL表中容纳一堆数字.在这种情况下，要存储在PL/SQL表中的数字将是TABLE1中的TABLE2_ID值. (例如，使用PL/SQL表代替数组，因为表可以容纳不同数量的值，而如果使用数组，则必须事先知道需要存储多少个数字.我们无法预先知道特定语句将影响多少行，因此我们使用PL/SQL表).
到达AFTER EACH ROW计时点时，我们发现正在处理的语句是INSERT，触发器将继续执行并对TABLE2执行必要的UPDATE，因为这不会引起问题.但是，如果正在执行DELETE，则触发器将TABLE1.TABLE2_ID保存到之前分配的PL/SQL表中.当最终到达AFTER STATEMENT定时点时，将循环访问先前分配的PL/SQL表，并为找到的每个TABLE2_ID执行适当的更新.
Sorry for my english.
I have 2 tables:
Table1
id
table2_id
num
modification_date
and
Table2
id
table2num
I want to make a trigger which after insert or delete in Table1 updates the last value num in Table2.table1lastnum.
My trigger:
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;
But after delete in Table1 I have error:
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'
What am I doing wrong?
What you've run into is the classic "mutating table" exception. In a ROW trigger Oracle does not allow you to run a query against the table which the trigger is defined on - so it's the SELECT against TABLE1 in the DELETING part of the trigger that's causing this issue.
There are a couple of ways to work around this. Perhaps the best in this situation is to use a compound trigger, which would look something like:
CREATE OR REPLACE TRIGGER TABLE1_NUM_TRG
FOR INSERT OR DELETE ON TABLE1
COMPOUND TRIGGER
TYPE NUMBER_TABLE IS TABLE OF NUMBER;
tblTABLE2_IDS NUMBER_TABLE;
BEFORE STATEMENT IS
BEGIN
tblTABLE2_IDS := NUMBER_TABLE();
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF INSERTING THEN
UPDATE TABLE2 t2
SET t2.TABLE2NUM = :new.NUM
WHERE t2.ID = :new.TABLE2_ID;
ELSIF DELETING THEN
tblTABLE2_IDS.EXTEND;
tblTABLE2_IDS(tblTABLE2_IDS.LAST) := :new.TABLE2_ID;
END IF;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
IF tblTABLE2_IDS.COUNT > 0 THEN
FOR i IN tblTABLE2_IDS.FIRST..tblTABLE2_IDS.LAST LOOP
UPDATE TABLE2 t2
SET t2.TABLE2NUM = (SELECT NUM
FROM (SELECT t1.NUM
FROM TABLE1 t1
WHERE t1.TABLE2_ID = tblTABLE2_IDS(i)
ORDER BY modification_date DESC)
WHERE ROWNUM = 1)
WHERE t2.ID = tblTABLE2_IDS(i);
END LOOP;
END IF;
END AFTER STATEMENT;
END TABLE1_NUM_TRG;
A compound trigger allows each timing point (BEFORE STATEMENT, BEFORE ROW, AFTER ROW, and AFTER STATEMENT) to be handled. Note that the timing points are always invoked in the order given. When an appropriate SQL statement (i.e. INSERT INTO TABLE1 or DELETE FROM TABLE1) is executed and this trigger is fired the first timing point to be invoked will be BEFORE STATEMENT, and the code in the BEFORE STATEMENT handler will allocate a PL/SQL table to hold a bunch of numbers. In this case the numbers to be stored in the PL/SQL table will be the TABLE2_ID values from TABLE1. (A PL/SQL table is used instead of, for example, an array because a table can hold a varying number of values, while if we used an array we'd have to know in advance how many numbers we would need to store. We can't know in advance how many rows will be affected by a particular statement, so we use a PL/SQL table).
When the AFTER EACH ROW timing point is reached and we find that the statement being processed is an INSERT, the trigger just goes ahead and performs the necessary UPDATE to TABLE2 as this won't cause a problem. However, if a DELETE is being performed the trigger saves the TABLE1.TABLE2_ID into the PL/SQL table allocated earlier. When the AFTER STATEMENT timing point is finally reached, the PL/SQL table allocated earlier is iterated through, and for each TABLE2_ID found the appropriate update is performed.
这篇关于插入或删除后Oracle触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
