skip to content »

Oracle triggers when updating

oracle triggers when updating-54

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 ( AFTER EACH ROW - INSERT ( 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.

oracle triggers when updating-45oracle triggers when updating-25oracle triggers when updating-43oracle triggers when updating-3

SQL From this we can see there is a single statement level before and after timing point, regardless of how many rows the individual statement touches, as well as a row level timing point for each row touched by the statement.The triggers from the previous section will reveal the timing points that are triggered.SET SERVEROUTPUT ON DECLARE TYPE t_trigger_test_tab IS TABLE OF trigger_test%ROWTYPE; l_tt_tab t_trigger_test_tab := t_trigger_test_tab(); BEGIN FOR i IN 1 ..5 LOOP l_tt_tab.extend; l_tt_tab(l_tt_tab.last):= i; l_tt_tab(l_tt_tab.last).description := 'Description for ' || 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 same is true for an " In the previous section we've seen what the timing points look like for individual statements. That depends on whether you are doing bulk inserts, updates or deletes using the statement.

The following code builds a collection of 5 records, then uses that to drive bulk inserts, updates and deletes on the TRIGGER_TEST table.

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.

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.

The workaround for this is to use variables defined in packages to store information that must be in scope for all timing points.