If I run the following in Oracle
delete * from tableA where id in (1,2).
I want to run a trigger which audits these deletes, but I want it to audit them as part of the same transaction, so the audit table would have
transaction idDeleted
----------- --
. .
. .
5 1
5 2
If I then ran the following
delete * from tableA where id in (3,5).
Then the audit table would then have
transaction idDeleted
----------- --
. .
. .
5 1
5 2
6 3
6 5
But I have think Oracle triggers may be row based so I am not sure how to do this.
Thanks
Damien
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