Pl sql trigger if updating
It creates a test table, a package to hold shared data and a trigger for each of the timing points.
Each trigger extends a collection defined in the package and stores a message with the trigger name and the current action it was triggered with.
This can become a little awkward when you have a parent-child relationship and a trigger on the parent table needs to execute some DML on the child table.
If the child table has a foreign key (FK) back to the parent table, any DML on the child table will cause a recursive SQL statement to check the constraint.
trigger_test_api.g_LOOP DBMS_OUTPUT.put_line(trigger_test_api.g_tab(i)); END LOOP; trigger_test_api.g_tab.delete; END trigger_test_as_trg; / COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- TRIGGER_TEST_API PACKAGE VALID TRIGGER_TEST TABLE VALID TRIGGER_TEST_BS_TRG TRIGGER VALID TRIGGER_TEST_BR_TRG TRIGGER VALID TRIGGER_TEST_AR_TRG TRIGGER VALID TRIGGER_TEST_AS_TRG TRIGGER VALID 6 rows selected.
SQL UPDATE trigger_test SET id = id; BEFORE STATEMENT - UPDATE BEFORE EACH ROW - UPDATE (new.id=2 old.id=2) AFTER EACH ROW - UPDATE (new.id=2 old.id=2) BEFORE EACH ROW - UPDATE (new.id=1 old.id=1) AFTER EACH ROW - UPDATE (new.id=1 old.id=1) AFTER STATEMENT - UPDATE 2 rows updated.
If the after statement trigger was responsible for anything important, like cleaning up the contents of the collection, we are in trouble.
So once again, make sure you understand how the timing points are triggered, or you could get unexpected behavior.i; END LOOP; DBMS_OUTPUT.put_line('*** FORALL - INSERT ***'); -- APPEND_VALUES hint is an 11g R2 feature, but doesn't affect timing points. l_tt_INSERT /* APPEND_VALUES */ INTO trigger_test VALUES l_tt_tab(i); DBMS_OUTPUT.put_line('*** FORALL - UPDATE ***'); -- Referencing collection columns in FORALL is only supported in 11g. l_tt_UPDATE trigger_test SET description = l_tt_tab(i).description WHERE id = l_tt_tab(i).id; DBMS_OUTPUT.put_line('*** FORALL - DELETE ***'); -- Referencing collection columns in FORALL is only supported in 11g. l_tt_DELETE FROM trigger_test WHERE id = l_tt_tab(i).id; ROLLBACK; END; / The output from this code is shown below.Notice how the statement level triggers only fire once at the start and end of the bulk insert operation, but fire on a row-by-row basis for the bulk update and delete operations.The following code will display the contents of the collection and clear it down. trigger_test_api.g_LOOP DBMS_OUTPUT.put_line(trigger_test_api.g_tab(i)); END LOOP; trigger_test_api.g_tab.delete; END; / BEFORE STATEMENT - INSERT BEFORE EACH ROW - INSERT (new.id=1) AFTER EACH ROW - INSERT (new.id=1) PL/SQL procedure successfully completed.SQL So all timing points executed as expected until the exception was raised, then the statement just stopped, without firing the after statement trigger.
This article represents the bare minimum you should understand about triggers before you consider writing one.