Solution to track DML statements on schema level
Posted by Kamran Agayev A. on April 3rd, 2009
As I’m active user in Oracle forums, in one of the posts, OP asked a question about DML triggers to be written for all schemas. OP 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 to 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 OP wanted it to be solved in triggering way, I provided 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 LOOP
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 LOOP;
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
June 24th, 2009 at 8:54 pm
Hi,
Will this have large overhead for an OLTP DB? Seriously considering to implement this at our prod DB
Thanks
DKwan
June 25th, 2009 at 9:18 am
Hi DKwan. Why you don’t want to implement AUDIT to your database? AUDIT is the best option. This option is for athe database which you don’t want to implement AUDIT (don’t know why )
Try firstly implement AUDIT. If you don’t want to implement AUDIT, the only way to track DML statement is to create triggers “for each table” and track all changes
Good Luck
April 11th, 2011 at 7:00 pm
This is not feasible in a busy OLTP database. actually, in real prod, once there is an issue, we care about the value change in the query, the bind value used to analyze the data issue.
April 14th, 2011 at 6:50 pm
Sure Hank, the best option is using Audit to get the changed value, but as OP didn’t want to use audit without providing any reason, I hope this way can help him
Thanks for feedback
April 23rd, 2012 at 1:17 pm
We had a user request to track few tables in few schemas in 9i database. They wanted to audit all the activities(Select/Insert/Update/Delete).
In 9i db_extended option is not there by FGA policies we can audit SELECT but not insert/update/delete, Please help me out how this can be achieved for a 9i database.