Trigger based on transaction

Triggers can be statement-level as well as row-level. Row-level triggers fire for each row, while statement level fires once for the statement. But in statement level, you will not be able to obtain the row values (eg tableA.id). We can solve this problem using two triggers: one statement level, and one row-level.


CREATE TABLE t1 (id number, name varchar2(10));
INSERT INTO t1 VALUES(1,'A1');
INSERT INTO t1 VALUES(2,'A2');
INSERT INTO t1 VALUES(3,'A3');
INSERT INTO t1 VALUES(4,'A4');
INSERT INTO t1 VALUES(5,'A5');

CREATE TABLE my_audit(seq NUMBER, id NUMBER);

CREATE SEQUENCE my_seq START WITH 1;

CREATE OR REPLACE PACKAGE pkg1 AS
  seq_no NUMBER;
END;
/

CREATE OR REPLACE TRIGGER bef_del_t1
BEFORE DELETE ON t1
BEGIN
  SELECT my_seq.nextval INTO pkg1.seq_no FROM DUAL;
END;
/


CREATE OR REPLACE TRIGGER after_del_t1
AFTER DELETE ON t1
FOR EACH ROW
BEGIN
  INSERT INTO my_audit VALUES(pkg1.seq_no, :old.id);
END;
/


SQL> select * from t1;

        ID NAME
---------- ----------
         1 A1
         2 A2
         3 A3
         4 A4
         5 A5

SQL> delete from t1 where id in (1,2);

2 rows deleted.

SQL> select * from my_audit;

       SEQ         ID
---------- ----------
         1          1
         1          2

SQL> delete from t1 where id in (3,5);

2 rows deleted.

SQL> select * from my_audit;

       SEQ         ID
---------- ----------
         1          1
         1          2
         2          3
         2          5

HTH
Ali