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> 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> create table emp_history as select * from emp where 1 = 2;

Table created.> 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> 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);
 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> 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;
 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> select * from emp_history;
no rows selected

Let’s update and delete from target table> update emp set sal = 4500 where empno = 7369;
1 row updated.                                           > delete from emp where empno = 9999;
1 row deleted.> commit;
Commit complete.

What will look like our emp_history table?> 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.


  1. Hourim,


    Can you come with post for auditing entire schema ?.


    Comment by Shadab — October 28, 2014 @ 1:19 pm | Reply

  2. What do you mean by entire schema? all tables? What I very often do is to implement this auditing in tables I know they are critics and they allow explaining many things in the application

    Comment by hourim — October 29, 2014 @ 1:03 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

You are commenting using your 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

Blog at

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

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

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog


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

%d bloggers like this: