Mohamed Houri’s Oracle Notes

March 16, 2012

Auditing Updates and Deletes

Filed under: Oracle,PL/SQL — hourim @ 2:26 pm

I found myself very often answering question like “I want to track any update or delete done on my table. I want to store the complete record before modification and want also to know who modified this record, when it has been modified and by what program”. I decided finally to write this blog article in order to use it as an answer to this kind of question.

So let’s say we have an employee table defined as follows

mhouri.world> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

When I want to audit modifications on this table, the first thing to do is to create a resembling historical table


mhouri.world> create table emp_history as select * from emp where 1 = 2;

Table created.

mhouri.world> alter table emp_history add( dml_usr varchar2(48)
  2                                       ,dml_pgm  varchar2(48)
  3                                       ,dml_dat  date
  4                                       ,dml_operation varchar2(1)
  5                                     );

Table altered.

Then I will create a special trigger on this historical table

mhouri.world> create or replace trigger emp_his_b_i_r_trg
  2  before insert on emp_history
  3  for each row
  4  declare
  5     lv_module VARCHAR2(48);
  6     lv_action VARCHAR2(48);
  7     ld_current_date DATE := SYSDATE;
  8     lv_current_user VARCHAR2(48) := USER;
  9  begin
 10     dbms_application_info.read_module(lv_module, lv_action);
 11  
 12     :new.dml_usr  := lv_current_user;
 13     :new.dml_dat  := ld_current_date;
 14     :new.dml_pgm  := lv_module;
 15  end emp_his_b_i_r_trg;
 16  /

Trigger created.

And finally, I will create a trigger on the target table i.e. the table I want to audit

mhouri.world> create or replace trigger emp_b_u_d_r_trg
  2  before update or delete on emp
  3  for each row
  4  declare
  5   lv_dml_operation emp_history.dml_operation%type;
  6  begin
  7  if updating then
  8    lv_dml_operation := 'U';
  9  elsif deleting then
 10    lv_dml_operation := 'D';
 11  end if;
 12  
 13     insert into emp_history(empno
 14                            ,ename
 15                            ,job
 16                            ,mgr
 17                            ,hiredate
 18                            ,sal
 19                            ,comm
 20                            ,deptno
 21                            ,dml_operation
 22                            )
 23                      values(:old.empno
 24                            ,:old.ename
 25                            ,:old.job
 26                            ,:old.mgr
 27                            ,:old.hiredate
 28                            ,:old.sal
 29                            ,:old.comm
 30                            ,:old.deptno
 31                            ,lv_dml_operation
 32                            );
 33  end emp_b_u_d_r_trg;
 34  /

Trigger created.

Ok, we are done now. Our new historical table is empty for the moment

mhouri.world> select * from emp_history;
no rows selected

Let’s update and delete from target table

mhouri.world> update emp set sal = 4500 where empno = 7369;
1 row updated.                                                    
mhouri.world> delete from emp where empno = 9999;
1 row deleted.
mhouri.world> commit;
Commit complete.

What will look like our emp_history table?

d102@o102d00d.world> select  empno
  2        , ename
  3        , job
  4        , sal
  5        , substr(dml_usr,1,10) usr
  6        , substr(dml_pgm,1,10) pgm
  7        , substr(dml_dat,1,12) dat
  8        , dml_operation        dml
  9  from emp_history
 10  ;

     EMPNO ENAME      JOB              SAL USR        PGM        DAT       D
---------- ---------- --------- ---------- ---------- ---------- --------- -
      7369 allen      clerk             35 SX2_Z      SQL*Plus   16-MAR-12 U
      9999 Miloud     Expert          5600 SX2_Z      SQL*Plus   16-MAR-12 D

Where we can see that we have already done two DML operations on our emp table an update followed by a delete. For that we used SQL*Plus and the user who did the operation was SX2_Z.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Oracle SQL Tuning Tools and Tips

SQLTXPLAIN (SQLT), TRCANLZR (TRCA), SQL Health-Check (SQLHC) and SQL Tuning Topics

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: