Inserting updating deleting trigger events

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.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.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.The workaround for this is to use variables defined in packages to store information that must be in scope for all timing points.The following code demonstrates the order in which the timing points are fired.

CASE WHEN INSERTING THEN -- Include any code specific for when the trigger is fired from an INSERT. WHEN UPDATING THEN -- Include any code specific for when the trigger is fired from an UPDATE. WHEN DELETING THEN -- Include any code specific for when the trigger is fired from a DELETE. DML triggers have four basic timing points for a single table. With the exception of Compound Triggers, the triggers for the individual timing points are self contained and can't automatically share state or variable information.

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 article represents the bare minimum you should understand about triggers before you consider writing one.

clause define the timing point for the trigger, which is explained below.

Leave a Reply