Order of trigger firing. : Trigger Firing « Trigger « Oracle PL / SQL






Order of trigger firing.

 
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );

Table created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('HIS', 101, 'History 101', 30, 11, 4, 20000);

1 row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('HIS', 301, 'History 301', 30, 0, 4, 20004);

1 row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('CS', 101, 'Computer Science 101', 50, 0, 4, 20001);

1 row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('ECN', 203, 'Economics 203', 15, 0, 3, 20002);

1 row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('CS', 102, 'Computer Science 102', 35, 3, 4, 20003);

1 row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('MUS', 410, 'Music 410', 5, 4, 3, 20005);

1 row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('ECN', 101, 'Economics 101', 50, 0, 4, 20007);

1 row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('NUT', 307, 'Nutrition 307', 20, 2, 4, 20008);

1 row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('MUS', 100, 'Music 100', 100, 0, 3, NULL);

1 row created.

SQL>
SQL>
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL> CREATE SEQUENCE trig_seq
  2    START WITH 1
  3    INCREMENT BY 1;

Sequence created.

SQL>
SQL> CREATE OR REPLACE PACKAGE TrigPackage AS
  2    v_Counter NUMBER;
  3  END TrigPackage;
  4  /

Package created.

SQL>
SQL> CREATE OR REPLACE TRIGGER sessionBeforeUpdate
  2    BEFORE UPDATE ON session
  3  BEGIN
  4    TrigPackage.v_Counter := 0;
  5
  6    INSERT INTO MyTable (num_col, char_col)
  7      VALUES (trig_seq.NEXTVAL,
  8        'Before Statement: counter = ' || TrigPackage.v_Counter);
  9
 10    TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
 11  END sessionBeforeUpdate;
 12  /

Trigger created.

SQL>
SQL> CREATE OR REPLACE TRIGGER sessionAfterStatement1
  2    AFTER UPDATE ON session
  3  BEGIN
  4    INSERT INTO MyTable (num_col, char_col)
  5      VALUES (trig_seq.NEXTVAL,
  6        'After Statement 1: counter = ' || TrigPackage.v_Counter);
  7
  8    TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
  9  END sessionAfterStatement1;
 10  /

Trigger created.

SQL>
SQL> CREATE OR REPLACE TRIGGER sessionAfterStatement2
  2    AFTER UPDATE ON session
  3  BEGIN
  4    INSERT INTO MyTable (num_col, char_col)
  5      VALUES (trig_seq.NEXTVAL,'After Statement 2: counter = ' || TrigPackage.v_Counter);
  6
  7    TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
  8  END sessionAfterStatement2;
  9  /

Trigger created.

SQL>
SQL> CREATE OR REPLACE TRIGGER sessionBeforeRow1
  2    BEFORE UPDATE ON session
  3    FOR EACH ROW
  4  BEGIN
  5    INSERT INTO MyTable (num_col, char_col)
  6      VALUES (trig_seq.NEXTVAL,
  7        'Before Row 1: counter = ' || TrigPackage.v_Counter);
  8
  9    -- Increment for the next trigger.
 10    TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
 11  END sessionBeforeRow1;
 12  /

Trigger created.

SQL>
SQL> CREATE OR REPLACE TRIGGER sessionBeforeRow2
  2    BEFORE UPDATE ON session
  3    FOR EACH ROW
  4  BEGIN
  5    INSERT INTO MyTable (num_col, char_col)
  6      VALUES (trig_seq.NEXTVAL,
  7        'Before Row 2: counter = ' || TrigPackage.v_Counter);
  8
  9    TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
 10  END sessionBeforeRow2;
 11  /

Trigger created.

SQL>
SQL> CREATE OR REPLACE TRIGGER sessionBeforeRow3
  2    BEFORE UPDATE ON session
  3    FOR EACH ROW
  4  BEGIN
  5    INSERT INTO MyTable (num_col, char_col)
  6      VALUES (trig_seq.NEXTVAL,
  7        'Before Row 3: counter = ' || TrigPackage.v_Counter);
  8
  9    TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
 10  END sessionBeforeRow3;
 11  /

Trigger created.

SQL>
SQL> CREATE OR REPLACE TRIGGER sessionAfterRow
  2    AFTER UPDATE ON session
  3    FOR EACH ROW
  4  BEGIN
  5    INSERT INTO MyTable (num_col, char_col)
  6      VALUES (trig_seq.NEXTVAL,
  7        'After Row: counter = ' || TrigPackage.v_Counter);
  8
  9    TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
 10  END sessionAfterRow;
 11  /

Trigger created.

SQL>
SQL> DELETE FROM MyTable;

0 rows deleted.

SQL>
SQL> UPDATE session
  2    SET num_credits = 4
  3    WHERE department IN ('HIS', 'CS');

4 rows updated.

SQL>
SQL> SELECT *
  2    FROM MyTable
  3    ORDER BY num_col;

   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
         1 Before Statement: counter = 0
         2 Before Row 3: counter = 1
         3 Before Row 2: counter = 2
         4 Before Row 1: counter = 3
         5 After Row: counter = 4
         6 Before Row 3: counter = 5
         7 Before Row 2: counter = 6
         8 Before Row 1: counter = 7
         9 After Row: counter = 8
        10 Before Row 3: counter = 9
        11 Before Row 2: counter = 10

   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
        12 Before Row 1: counter = 11
        13 After Row: counter = 12
        14 Before Row 3: counter = 13
        15 Before Row 2: counter = 14
        16 Before Row 1: counter = 15
        17 After Row: counter = 16
        18 After Statement 2: counter = 17
        19 After Statement 1: counter = 18

19 rows selected.

SQL>
SQL>
SQL> DROP SEQUENCE trig_seq;

Sequence dropped.

SQL>
SQL> drop table mytable;

Table dropped.

SQL>
SQL> drop table session;

Table dropped.

SQL>
SQL>

 








Related examples in the same category

1.Avoid the mutating table error.