插入或删除后的Oracle触发器

2021-12-05 00:00:00 oracle11g oracle triggers

对不起我的英语.

我有两张桌子:

Table1IDtable2_id数量修改日期

和

Table2ID表2号

我想做一个触发器，在Table1中插入或删除后更新Table2.table1lastnum中的最后一个值num.

我的触发器:

创建或替换触发器 TABLE1_NUM_TRG在 table1 上插入或删除之后每行开始如果插入然后更新表2SET table2num = :new.num哪里 table2.id = :new.table2_id;别的更新表2SET table2num = (SELECT num FROM (SELECT num FROM table1 WHERE table2_id = :old.table2_id ORDER BY modify_date DESC) WHERE ROWNUM <= 1)哪里 table2.id = :old.table2_id;万一;END TABLE1_NUM_TRG;

但是在 Table1 中删除后我有错误:

ORA-04091:表 BD.TABLE1 正在发生变化，触发器/函数可能看不到它ORA-06512:在BD.TABLE1_NUM_TRG"，第 11 行ORA-04088: 执行触发器 'BD.TABLE1_NUM_TRG' 期间出错

我做错了什么?

解决方案

您遇到的是经典的变异表"异常.在 ROW 触发器中，Oracle 不允许您对定义触发器的表运行查询 - 所以它是 SELECT 针对触发器的 DELETING 部分中的 TABLE1这导致了这个问题.

有几种方法可以解决这个问题.也许在这种情况下最好的方法是使用复合触发器，它看起来像:

创建或替换触发器 TABLE1_NUM_TRG用于在 TABLE1 上插入或删除复合触发器TYPE NUMBER_TABLE 是数字表；tblTABLE2_IDS NUMBER_TABLE;在声明之前是开始tblTABLE2_IDS := NUMBER_TABLE();语句前结束；在每一行之后开始如果插入那么更新表2 t2SET t2.TABLE2NUM = :new.NUM哪里 t2.ID = :new.TABLE2_ID;ELSIF 删除然后tblTABLE2_IDS.EXTEND;tblTABLE2_IDS(tblTABLE2_IDS.LAST) := :new.TABLE2_ID;万一;在每一行之后结束；声明之后是开始如果 tblTABLE2_IDS.COUNT >0 那么FOR i 在 tblTABLE2_IDS.FIRST..tblTABLE2_IDS.LAST 循环中更新表2 t2SET t2.TABLE2NUM = (SELECT NUM从(选择 t1.NUM从表 1 t1WHERE t1.TABLE2_ID = tblTABLE2_IDS(i)ORDER BY 修改日期 DESC)其中 RowNUM = 1)WHERE t2.ID = tblTABLE2_IDS(i);结束循环；万一;语句后结束；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 的必要更新，因为这不会导致问题.但是，如果正在执行 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.

Documentation here.

相关文章