Sunday, March 21, 2010

How to track column level value change in oracle by a generic trigger

How to track column level value change in oracle by a generic trigger
======================================================
======================================================


1. First create a audit table
=============================

CREATE TABLE audit_table
( TIMESTAMP DATE,
user_name VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(30),
OLD_value VARCHAR2(2000),
NEW_value VARCHAR2(2000),
os_user VARCHAR2(2000),
machine VARCHAR2(2000),
program_name VARCHAR2(2000),
application_user VARCHAR2(2000),
action varchar2(200) default 'DElect'
)
/



2. Create package specification
===============================


CREATE OR REPLACE PACKAGE audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER
);
END;
/

3. Create package Body
========================


CREATE OR REPLACE PACKAGE BODY audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2
)
IS
t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;


IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE
)
IS
t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;

IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;


PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER
)
IS

t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;

IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;
END audit_pkg;


4. create trigger on specific table by runing this script
==========================================================


SQL> @create_trigger.sql
---------------------------------
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
set echo off
spool tmp.sql

prompt create or replace trigger aud#&&1
prompt after update or delete or insert on &&1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&&1')


/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp
-----------------------------------

3 comments:

bala said...

thank you so much for your posting.
I would like to keep track of table level column changes like insert /update/delete for a table

table : emp
column1: emp_id
column 2: emp_name
column 3 : emp_increment
column 4 : archive_ts
column 5 : event_cd ( i.e I/U/D I - INSERT / U - UPDATE / D - DELETE

audit should be

ID NAME increment ARCHIEVE_TS EVENET_CD
100 X 10 21-JAN-2012 I
100 X 15 22-JAN-2012 U

I WOULD NEED PROCEDURE TO ACHIEVE THE SAME RESULT , IT WOULD BE EASY TO WRITE IN TRIGGERS BUT WE WANT TO IMPLEMENT VIA STORED PROCEDURES

PLEASE DO THE NEEDFUL

bala said...

WANT TO CONVERT THE SAME CODE IN ORACLE PROCEDURES , THANKS IN ADVANCE FOR YOUR HELP.

create or replace TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO emp_audit
(emp_id , salary,created_ts,CREATED_user,MODIFIED_TS,MODIFIED_usER, archived_ts, event_cd)
VALUES (
:NEW.emp_id,
:NEW.salary,
:NEW.CREATED_TS,
:NEW.CREATED_user,
:NEW.MODIFIED_TS,
:NEW.MODIFIED_usER,
systimestamp, 'C'); -- INSERT
ELSIF UPDATING THEN
INSERT INTO emp_audit
(emp_id , salary,created_ts,CREATED_user,MODIFIED_TS,MODIFIED_usER, archived_ts, event_cd)
VALUES (
:OLD.emp_id,
:OLD.salary,
:OLD.CREATED_TS,
:OLD.CREATED_user,
:OLD.MODIFIED_TS,
:OLD.MODIFIED_usER,
systimestamp, 'U'); -- UPDATE
ELSIF DELETING THEN
INSERT INTO emp_audit
(emp_id , salary,created_ts,CREATED_user,MODIFIED_TS,MODIFIED_usER, archived_ts, event_cd)
VALUES (
:OLD.emp_id,
:OLD.salary,
:OLD.CREATED_TS,
:OLD.CREATED_user,
:OLD.MODIFIED_TS,
:OLD.MODIFIED_usER,
systimestamp, 'D'); -- DELETE
END IF;

END;

bala said...

MY DBA ALWAYS HATS ORACLE TRIGGERS SO WANTED TO CONVERT TRIGGER TO PROCEDURE.
THANKS FOR ANY HELP.