Triggers igen igen

Klassiks problem, at Oracle ikke tør lade dig opdatere data af hensyn til uendelige løkker.

Her er en workaround:

Problem Description
-------------------

Oracle does not allow you to read a mutating table in a row trigger because
if you can read it, the information may be incorrect (not read consistent). 
If you attempt this, the following error is returned:

    ORA-04091  Table %s.%s is mutating, trigger/function may not see it

However, you can perform this operation in a statement trigger. 
 
One way to handle this situation is to use a package PL/SQL table to store 
ROWIDs of updated records in a row trigger, and reprocess the updated 
records in a statement trigger.  Below is an example. 


Important Note
--------------

Note that there are concurrency issues with this if more than one
session tries to perform operations simultaneously.  This is not intended
as a total solution, but as the framework to help show one option.


Example Workaround
------------------

create or replace package emp_pkg as 
  type      emp_tab_type is table of rowid index by binary_integer; 
  emp_tab  emp_tab_type; 
  emp_index binary_integer; 
end emp_pkg; 
/ 
 
create or replace trigger emp_bef_stm_all 
before insert or update or delete on emp 
begin 
  /* 
  Remember to reset the pl/sql table before each statement 
  */ 
  emp_pkg.emp_index := 0; 
end; 
/ 
 
create or replace trigger emp_aft_row_all 
after insert or update or delete on emp 
for each row 
begin 
  /* 
  Store the rowid of updated record into global pl/sql table 
  */ 
  emp_pkg.emp_index := emp_pkg.emp_index + 1; 
  emp_pkg.emp_tab(emp_pkg.emp_index) := :new.rowid; 
end; 
/ 
 
create or replace trigger emp_aft_stm_all 
after  insert or update or delete on emp 
begin 
  for i in 1 .. emp_pkg.emp_index loop 
      /* 
      Re-process the updated records. 
      There is no restriction here. 
      */ 
      dbms_output.put_line(emp_pkg.emp_tab(i)); 
  end loop; 
  emp_pkg.emp_index := 0; 
end; 
/ 

I din after all kan du lave opdateringerne!

Prøv denne trigger på pligt.p14. Hvis du vil opdatere i P01 tabellen men med p14’en gamle værdi, må det nødvendigvis ske inden opdatering af p14 ellers er der jo igen :old at referere til:

CREATE OR REPLACE TRIGGER pligt.p14_trigger_1
BEFORE UPDATE ON pligt.p14
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
  UPDATE pligt.p01 SET p01.p01_sup_cat = :NEW.p14_sup_cat
  WHERE p01.p01_sup_cat = :OLD.p14_sup_cat;
END;

Denne vil dog kun virke hvis der er en p01.p01_sup_cat som er = :OLD.p14_sup_cat.

ellers så prøv denne:

CREATE OR REPLACE TRIGGER pligt.p14_trigger_1
BEFORE UPDATE ON pligt.p14
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN

  UPDATE pligt.p01 SET p01.p01_sup_cat = :NEW.p14_sup_cat
  WHERE p01.p01_sup_cat = :OLD.p14_sup_cat;
 
  IF SQL%NOTFOUND THEN
  -- Husk også at indsætte øvrige krævet værdier
  INSERT INTO pligt.p01(p01.p01_sup_cat) VALUES (:OLD.p14_sup_cat);
  END IF;
END;

CREATE OR REPLACE TRIGGER PLIGT.P14_TRIGGER_1
AFTER UPDATE OF P14_SUP_CAT
ON PLIGT.P14
FOR EACH ROW
declare
  Mutating_table  EXCEPTION;
  PRAGMA          EXCEPTION_INIT (Mutating_table, -4091);
BEGIN
  UPDATE P01 set P01_SUP_CAT=:NEW.P14_SUP_CAT
  where P01.P01_SUP_CAT = :OLD.P14_SUP_CAT;
EXCEPTION
  WHEN Mutating_table THEN
      NULL;
END;