Jeg skal have opdateret tabel P01 med den nye værdi fra P14 troede jeg kunne klare det med en update og følgende kode: CREATE OR REPLACE TRIGGER PLIGT.P14_TRIGGER_1 AFTER UPDATE OF P14_SUP_CAT ON PLIGT.P14 REFERENCING OLD AS OLD1 NEW AS NEW1 FOR EACH ROW BEGIN UPDATE P01 set P01_SUP_CAT = :NEW1.P14_SUP_CAT where P01.P01_SUP_CAT = :OLD1.P14_SUP_CAT; END;
Men fik den der mutating ting i hovedet - og nu aner jeg ikke hvad jeg skal stille op da jeg ikke er nogen ørn til PL/SQL
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; /
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;
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.