Trigger Oracle dopo l’inserimento o l’eliminazione

Mi scusi per il mio inglese.

Ho 2 tavoli:

Table1 id table2_id num modification_date 

e

 Table2 id table2num 

Voglio fare un trigger che dopo l’inserimento o l’eliminazione in Table1 aggiorna l’ultimo valore num in Table2.table1lastnum .

Il mio grilletto:

 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; 

Ma dopo l’eliminazione in Table1 ho errore:

 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' 

Che cosa sto facendo di sbagliato?

Quello che hai incontrato è la classica eccezione “tabella mutante”. In un trigger ROW, Oracle non consente di eseguire una query sulla tabella su cui è definito il trigger, quindi è SELECT rispetto a TABLE1 nella parte DELETING del trigger che causa questo problema.

Ci sono un paio di modi per aggirare questo problema. Forse il migliore in questa situazione è usare un trigger composto, che assomiglierebbe a qualcosa:

 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; 

Un trigger composto consente di gestire ogni punto temporale ( BEFORE STATEMENT , BEFORE ROW , AFTER ROW e AFTER STATEMENT ). Si noti che i punti di temporizzazione sono sempre invocati nell’ordine indicato. Quando viene eseguita un’istruzione SQL appropriata (ad esempio INSERT INTO TABLE1 o DELETE FROM TABLE1 ) e viene triggersto questo trigger, il primo punto di timing da richiamare sarà BEFORE STATEMENT e il codice nel gestore BEFORE STATEMENT assegnerà una tabella PL / SQL a tenere un mucchio di numeri. In questo caso i numeri da memorizzare nella tabella PL / SQL corrisponderanno ai valori TABLE2_ID di TABLE1. (Una tabella PL / SQL viene utilizzata al posto di, ad esempio, una matrice perché una tabella può contenere un numero variabile di valori, mentre se usassimo una matrice dovremmo sapere in anticipo quanti numeri dovremmo memorizzare. Non possiamo sapere in anticipo quante righe saranno interessate da una particolare istruzione, quindi usiamo una tabella PL / SQL). Quando viene raggiunto il punto di sincronizzazione AFTER EACH ROW e scopriamo che l’istruzione in elaborazione è INSERT, il trigger procede e compie l’UPDATE necessario a TABLE2 in quanto ciò non causerà alcun problema. Tuttavia, se viene eseguito un DELETE, il trigger salva TABLE1.TABLE2_ID nella tabella PL / SQL allocata in precedenza. Quando viene raggiunto il punto di sincronizzazione AFTER STATEMENT , la tabella PL / SQL allocata in precedenza viene iterata e, per ogni TABLE2_ID trovato, viene eseguito l’aggiornamento appropriato.

Documentazione qui .

Condividi e divertiti.

Devi definire un trigger prima per delete.Try utilizzando due trigger

 CREATE OR REPLACE TRIGGER INS_TABLE1_NUM_TRG AFTER INSERT ON table1 FOR EACH ROW BEGIN UPDATE table2 SET table2num = :new.num WHERE table2.id = :new.table2_id; END INS_TABLE1_NUM_TRG; CREATE OR REPLACE TRIGGER DEL_TABLE1_NUM_TRG BEFORE DELETE ON table1 FOR EACH ROW BEGIN 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 DEL_TABLE1_NUM_TRG;