Oracle updating trigger

22-Feb-2017 22:51 by 6 Comments

Oracle updating trigger - 100 dating somalian site nl

SQL For a more practical use of compound triggers, we can take the example quoted in the Mutating Table Exceptions article and replace the two triggers and the package with a single compound trigger, as shown below.

After that, you can just set the :new.[column name] values and allow the driving transaction to perform it's own commit or rollback.

NEXTVAL; IF INSERTING THEN g_change_tab(g_change_tab.last).action := 'INSERT'; ELSE g_change_tab(g_change_tab.last).action := 'UPDATE'; END IF; g_change_tab(g_change_tab.last).tab1_id := :new.id; g_change_tab(g_change_tab.last).created_time := SYSTIMESTAMP; END AFTER EACH ROW; AFTER STATEMENT IS l_count NUMBER(10); BEGIN FOR i IN g_change_tab.first ..

g_change_LOOP SELECT COUNT(*) INTO g_change_tab(i).record_count FROM tab1; END LOOP; FORALL i IN g_change_tab.first ..

l_LOOP DBMS_OUTPUT.put_line(l_tab(i)); END LOOP; l_tab.delete; END AFTER STATEMENT; END compound_trigger_test_trg; / UPDATE compound_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.

SQL DELETE FROM compound_trigger_test; BEFORE STATEMENT - DELETE BEFORE EACH ROW - DELETE (old.id=2) AFTER EACH ROW - DELETE (old.id=2) BEFORE EACH ROW - DELETE (old.id=1) AFTER EACH ROW - DELETE (old.id=1) AFTER STATEMENT - DELETE 2 rows deleted.

END AFTER EACH ROW; AFTER STATEMENT IS BEGIN NULL; -- Do something here.

END AFTER STATEMENT; END The following code creates a test table and a compound trigger that fires for each timing point associated with insert, update and delete statements.It does not "see" the new record because the record has not been committed so the update comes back with 0 rows updated.It might be better include the logic in the trigger itself.END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN NULL; -- Do something here.END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN NULL; -- Do something here.CREATE TABLE trigger_follows_test ( id NUMBER, description VARCHAR2(50) ); CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1 BEFORE INSERT ON trigger_follows_test FOR EACH ROW BEGIN DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_1 - Executed'); END; / CREATE OR REPLACE TRIGGER trigger_follows_test_trg_2 BEFORE INSERT ON trigger_follows_test FOR EACH ROW BEGIN DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_2 - Executed'); END; / CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1 BEFORE INSERT ON trigger_follows_test FOR EACH ROW FOLLOWS trigger_follows_test_trg_2 BEGIN DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_1 - Executed'); END; / A compound trigger allows code for one or more timing points for a specific object to be combined into a single trigger.