Solution to track DML statements on schema level

In one of the posts, user asked a question about DML triggers to be written for all schemas. user wanted to write a schema level DML trigger. As we now, it’s impossible to write schema level DML trigger. We can write DDL trigger to be fired on schema level.

Now, we have two choices.









1. To track all DML statements using Audit
2. To create one (INSERT, UPDATE, DELETE) trigger for all tables of the schema automatically

Its better to create audit for that tables, but as the user wanted it to be solved in triggering way, I found this type of solution:

In order to create trigger for each object, I followed below steps:



–I create a new user

SQL> create user test_trigger identified by kamran;
User created.

–Give it dba role (Don’t do it in production database)

SQL> grant dba to test_trigger;
Grant succeeded.

– Connect with that user

SQL> conn test_trigger/kamran
Connected.

– Create a new table

SQL> create table tab1 (id number);
Table created.

– Create a second table

SQL> create table tab2 (id number);
Table created.

– Create another table to log information which UPDATED, DELETED and INSERTED on two other tables

SQL> create table tab1_2_log (information varchar2(20));
Table created.

– Write a PL/SQL block to create a trigger for each table of the newly created user automatically

SQL> DECLARE
2 CURSOR all_tables
3 IS
4 SELECT table_name
5 FROM user_tables
6 WHERE table_name ‘TAB1_2_LOG’;
7
8
9 BEGIN
10 FOR rec_cur IN all_tables
11 LOuser
12 EXECUTE IMMEDIATE ‘create or replace trigger trg_’
13 || rec_cur.table_name
14 || ‘
15 before insert or update or delete on ‘
16 || rec_cur.table_name
17 || ‘
18 declare
19 begin
20 if UPDATING then
21 insert into tab1_2_log values(”UPDATING on ‘||rec_cur.table_name||”’);
22 elsif DELETING then
23 insert into tab1_2_log values(”DELETING on ‘||rec_cur.table_name||”’);
24 elsIF INSERTING then
25 insert into tab1_2_log values(”INSERTING on ‘||rec_cur.table_name||”’);
26 end if;
27 end;’;
28 END LOuser;
29 END;
30 /

PL/SQL procedure successfully completed.

1. Here, I create a cursor to take all tables in newly created schema
2. Open the cursor and get name of tables in my schema
3. Create EXECUTE IMMEDIATE statement and run creation of trigger by passing it the name of each table

Now, I begin to test my triggers by inserting to, updating and deleting data from tables

SQL> insert into tab1 values(1);
1 row created.

SQL> update tab1 set id=1;
1 row updated.

SQL> delete from tab1;
1 row deleted.

SQL> insert into tab2 values(1);
1 row created.

SQL> update tab2 set id=1;
1 row updated.

SQL> delete from tab2 ;
1 row deleted.

– Now, let’s check what’s in our log table
SQL> select * from tab1_2_log;

INFORMATION
——————–
INSERTING on TAB1
UPDATING on TAB1
DELETING on TAB1
INSERTING on TAB2
UPDATING on TAB2
DELETING on TAB2
6 rows selected.

– As it seen, all information related DELETING, UPDATING and INSERTING on tables have been gathered. Now, check name of triggers which was created on that schema to track all DML statement of each table
SQL> SELECT trigger_name, table_name FROM all_triggers WHERE table_owner=’TEST_TRIGGER’;

TRIGGER_NAME TABLE_NAME
—————————— ——————————
TRG_TAB1 TAB1
TRG_TAB2 TAB2

To get more information related Triggers, please refer to documentation :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm#i6061


Thanks.
NJ