Oracle trigger if not updating
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.TRIGGER_TEST_AR_TRG' SQL This is because the after statement trigger did not fire.
So once again, make sure you understand how the timing points are triggered, or you could get unexpected behavior.The CREATE TRIGGER statement has a lot of permutations, but the vast majority of the questions I'm asked relate to basic DML triggers.Of those, the majority are related to people misunderstanding the order of the timing points and how they are affected by bulk-bind operations and exceptions.There are optional declaration and exception sections, like any other PL/SQL block, if required.The "dml-event" can be one or more of the following.
Search for oracle trigger if not updating:
CREATE OR REPLACE TRIGGER my_test_trg BEFORE INSERT OR UPDATE OR DELETE ON my_table FOR EACH ROW BEGIN -- Flags are booleans and can be used in any branching construct. If you need some code to perform an operation that needs to commit, regardless of the current transaction, you should put it in a stored procedure defined as an autonomous transaction, shown here.