OraFAQ Forum: SQL & PL/SQL » Audit Table with Primary keys

Home » SQL & PL/SQL » SQL & PL/SQL » Audit Table with Primary keys (Oracle 10G (10.2.0.1.0 ), Windows 2003)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Audit Table with Primary keys [message #577612]

Tue, 19 February 2013 01:39

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Dear Experts,
I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.

DROP TABLE emp;
CREATE TABLE emp AS (SELECT * FROM scott.emp);

CREATE TABLE audit_table
(
  ent_date    DATE                              DEFAULT SYSDATE,
  ip_address  VARCHAR2(15)                      DEFAULT SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), 1, 15),
  os_term     VARCHAR2(15)                      DEFAULT SUBSTR (SYS_CONTEXT ('USERENV', 'TERMINAL'), 1, 15),
  os_user     VARCHAR2(15)                      DEFAULT SUBSTR (SYS_CONTEXT ('USERENV', 'OS_USER'), 1, 15),
  tname       VARCHAR2(30),
  cname       VARCHAR2(30),
  OLD         VARCHAR2(2000),
  NEW         VARCHAR2(2000),
  status      VARCHAR2(6),
  seq         NUMBER(6)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
/

create sequence x#_seq;
/

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,
			 l_status in varchar2,
			 l_seq in number
			);

    procedure check_val( l_tname in varchar2, 
                         l_cname in varchar2, 
                         l_new in date, 
                         l_old in date,
			 l_status in varchar2,
			 l_seq in number
			);

    procedure check_val( l_tname in varchar2, 
                         l_cname in varchar2, 
                         l_new in number, 
                         l_old in number, 
			 l_status in varchar2,
			 l_seq in number
			);

end;
/

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,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
    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
	     VALUES (SYSDATE, SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), 1, 15),
        	     SUBSTR (SYS_CONTEXT ('USERENV', 'TERMINAL'), 1, 15),
	             SUBSTR (SYS_CONTEXT ('USERENV', 'OS_USER'), 1, 15),
        	     UPPER (l_tname), UPPER (l_cname), l_old, l_new,l_status,l_seq);
    end if;
end;

procedure check_val( l_tname in varchar2, 
		     l_cname in varchar2,
	             l_new in date, 
		     l_old in date, 
		     l_status in varchar2,
		     l_seq in number
		   )

is
begin
    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 values
        ( sysdate, SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), 1, 15),
        	     SUBSTR (SYS_CONTEXT ('USERENV', 'TERMINAL'), 1, 15),
	             SUBSTR (SYS_CONTEXT ('USERENV', 'OS_USER'), 1, 15),
	  upper(l_tname), upper(l_cname),
          to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
          to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ),l_status,l_seq );
    end if;
end;

procedure check_val( l_tname in varchar2, 
		     l_cname in varchar2,
             	     l_new in number, 
	             l_old in number,
		     l_status in varchar2,
		     l_seq in number
		   )

is
begin
    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 values
        ( sysdate, SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), 1, 15),
        	     SUBSTR (SYS_CONTEXT ('USERENV', 'TERMINAL'), 1, 15),
	             SUBSTR (SYS_CONTEXT ('USERENV', 'OS_USER'), 1, 15),
	 upper(l_tname), upper(l_cname),
                                 l_old, l_new,l_status,l_seq );
    end if;
end;

end audit_pkg;

/

create or replace trigger TRIG_EMP
after update on emp
for each row
declare
l_seq   number;
begin
select x#_seq.nextval into l_seq from dual;
	if updating
	then
	    audit_pkg.check_val( 'emp', 'EMPNO', :new.EMPNO, :old.EMPNO,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'JOB', :new.JOB, :old.JOB,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'MGR', :new.MGR, :old.MGR,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'HIREDATE', :new.HIREDATE, :old.HIREDATE,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'SAL', :new.SAL, :old.SAL,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'COMM', :new.COMM, :old.COMM,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'DEPTNO', :new.DEPTNO, :old.DEPTNO,'UPDATE',l_seq);
	elsif deleting
	then
	    audit_pkg.check_val( 'emp', 'EMPNO', :new.EMPNO, :old.EMPNO,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'JOB', :new.JOB, :old.JOB,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'MGR', :new.MGR, :old.MGR,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'HIREDATE', :new.HIREDATE, :old.HIREDATE,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'SAL', :new.SAL, :old.SAL,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'COMM', :new.COMM, :old.COMM,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'DEPTNO', :new.DEPTNO, :old.DEPTNO,'DELETE',l_seq);
	end if;
end;
/

Update the table and delete a row

SQL>UPDATE emp
   SET job = 'clerk',
       sal = 1000,
       comm = 300
 WHERE empno = 7934;

SQL>DELETE FROM emp
      WHERE empno = 7902;

SQL>commit;

SQL> Select * from audit_table;

ENT_DATE IP_ADDRESS OS_TERM OS_USER TNAM CNAM OLD NEW STATUS SEQ
--------- --------------- --------------- --------------- ---- ---- ---------- ---------- ------ ----------
19-FEB-13 192.168.1.199 MOHSIN Administrator EMP JOB CLERK clerk UPDATE 1
19-FEB-13 192.168.1.199 MOHSIN Administrator EMP SAL 1300 1000 UPDATE 1
19-FEB-13 192.168.1.199 MOHSIN Administrator EMP COMM 300 UPDATE 1

[/code]

I want to log updated columns with primary keys.e.g empno with same SEQ 21. Old values for deleted rows.

Required output will be

ENT_DATE  IP_ADDRESS      OS_TERM         OS_USER         TNAM CNAM OLD        NEW        STATUS        SEQ
--------- --------------- --------------- --------------- ---- ---- ---------- ---------- ------ ----------
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  JOB  CLERK      clerk      UPDATE         1
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  SAL  1300       1000       UPDATE         1
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  COMM            300        UPDATE         1
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  EMPNO 7934     7934        UPDATE         1
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  EMPNO  7902           DELETED         2
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  ENAME  FORD              DELETED         2
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  JOB  ANALYST                 DELETED         2
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  MGR 7566             DELETED         2
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  HIREDATE  03-DEC-1981            DELETED         2
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  SAL  3000              DELETED        2
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  COMM                    DELETED        2
19-FEB-13 192.168.1.199   MOHSIN          Administrator   EMP  DEPTNO 20             DELETED        2

Thanks

M. Mohsin

Report message to a moderator

Re: Audit Table with Primary keys [message #577613 is a reply to message #577612]

Tue, 19 February 2013 01:43

Michel Cadot
Messages: 54650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What prevents you from inserting the primary key each time?
Copy and paste the insert statement from check_val at the beginning of trig_emp trigger for empno column.

Regards
Michel

Report message to a moderator

Re: Audit Table with Primary keys [message #577626 is a reply to message #577613]

Tue, 19 February 2013 03:58

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Thanks Michel.
I have changed the code as per your instructions and it works.

CREATE OR REPLACE TRIGGER TRIG_EMP
after update ON EMP for each row
declare
l_seq   number;
begin
select x#_seq.nextval into l_seq from dual;
	if updating
	then
		BEGIN
		        insert into audit_table values
		        ( sysdate, SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), 1, 15),
		        	     SUBSTR (SYS_CONTEXT ('USERENV', 'TERMINAL'), 1, 15),
	        		     SUBSTR (SYS_CONTEXT ('USERENV', 'OS_USER'), 1, 15),
			 upper('EMP'), upper('EMPNO'),
                		                 :old.EMPNO, :old.EMPNO,'UPDATE',l_seq );
		END;
	    audit_pkg.check_val( 'emp', 'EMPNO', :new.EMPNO, :old.EMPNO,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'JOB', :new.JOB, :old.JOB,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'MGR', :new.MGR, :old.MGR,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'HIREDATE', :new.HIREDATE, :old.HIREDATE,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'SAL', :new.SAL, :old.SAL,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'COMM', :new.COMM, :old.COMM,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'DEPTNO', :new.DEPTNO, :old.DEPTNO,'UPDATE',l_seq);
	elsif deleting
	then
	    audit_pkg.check_val( 'emp', 'EMPNO', :new.EMPNO, :old.EMPNO,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'JOB', :new.JOB, :old.JOB,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'MGR', :new.MGR, :old.MGR,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'HIREDATE', :new.HIREDATE, :old.HIREDATE,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'SAL', :new.SAL, :old.SAL,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'COMM', :new.COMM, :old.COMM,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'DEPTNO', :new.DEPTNO, :old.DEPTNO,'DELETE',l_seq);
	end if;
end;
/

For Deletion I have to insert all the columns for a row. If there are 20 columns of a table then i have to write 20 insert statements.
Alternate, please suggest a query to get all the columns of a table and insert the data into relevant column.

Thanks.
M. Mohsin

Report message to a moderator

Re: Audit Table with Primary keys [message #577630 is a reply to message #577626]

Tue, 19 February 2013 04:12

Michel Cadot
Messages: 54650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Change the check_val procedure to take an array of column names and values instead of a single one and use "FORALL ... INSERT" statement.

Regards
Michel

Report message to a moderator

Re: Audit Table with Primary keys [message #577653 is a reply to message #577630]

Tue, 19 February 2013 07:00

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Dear Michel,
I have tried the following query but it is giving me compillation error. Please help me correct it.

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,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
	declare
	 CURSOR s_cur IS 
	 Select COLUMN_NAME from user_tab_columns where table_name='EMP';
	 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
	 s_array fetch_array;
	BEGIN
	  OPEN s_cur;
	  LOOP
	    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
	    FORALL i IN 1..s_array.COUNT
	  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 (tname,cname,old,new,status,seq)
	         VALUES ( UPPER (l_tname), UPPER (l_cname), l_old, l_new,l_status,l_seq);
 	  end if;
	    EXIT WHEN s_cur%NOTFOUND;
	  END LOOP;
	  CLOSE s_cur;
	  COMMIT;
	END;
end;
procedure check_val( l_tname in varchar2,
                     l_cname in varchar2,
	             l_new in Date,
                     l_old in Date,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
	declare
	 CURSOR s_cur IS 
	 Select COLUMN_NAME from user_tab_columns where table_name='EMP';
	 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
	 s_array fetch_array;
	BEGIN
	  OPEN s_cur;
	  LOOP
	    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
	    FORALL i IN 1..s_array.COUNT
	  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 (tname,cname,old,new,status,seq)
	         VALUES ( UPPER (l_tname), UPPER (l_cname), 
				to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
	        		to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ),l_status,l_seq);
	   end if;
	    EXIT WHEN s_cur%NOTFOUND;
	  END LOOP;
	  CLOSE s_cur;
	  COMMIT;
	END;
end;
procedure check_val( l_tname in varchar2,
                     l_cname in varchar2,
	             l_new in Number,
                     l_old in Number,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
	declare
	 CURSOR s_cur IS 
	 Select COLUMN_NAME from user_tab_columns where table_name='EMP';
	 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
	 s_array fetch_array;
	BEGIN
	  OPEN s_cur;
	  LOOP
	    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
	    FORALL i IN 1..s_array.COUNT
	  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 (tname,cname,old,new,status,seq)
	         VALUES ( UPPER (l_tname), UPPER (l_cname), l_old, l_new,l_status,l_seq);
	  end if;
	    EXIT WHEN s_cur%NOTFOUND;
	  END LOOP;
	  CLOSE s_cur;
	  COMMIT;
	END;
end;
end audit_pkg;
/

SQL>show err
Errors for PACKAGE BODY AUDIT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/4 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:
. ( * @ % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge

31/4 PLS-00103: Encountered the symbol "CLOSE" when expecting one of
the following:
end not pragma final instantiable order overriding static
member constructor map

LINE/COL ERROR
-------- -----------------------------------------------------------------
33/5 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
loop

Thanks
M. Mohsin

Report message to a moderator

Re: Audit Table with Primary keys [message #577657 is a reply to message #577653]

Tue, 19 February 2013 07:24

sss111ind
Messages: 282
Registered: April 2012
Location: India

Senior Member

Hi Mohsin ,

You can use Forall only directly with insert,update ,delete with bulk collect.Inside forall all other coding is not allowed. If you want to check something then you have to use normal for loop.
Please have a look this doc also
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm#BABEDFJE

Regards,
Nathan

Report message to a moderator

Re: Audit Table with Primary keys [message #577658 is a reply to message #577653]

Tue, 19 February 2013 07:25

cookiemonster
Messages: 9273
Registered: September 2008
Location: Rainy Manchester

Senior Member

Forall must be followed by a sql statement (insert, update, delete) not an if statement.

Report message to a moderator

Re: Audit Table with Primary keys [message #577729 is a reply to message #577658]

Tue, 19 February 2013 23:46

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Thanks Nathan & Cookimonster,
I have removed if statement and now i am getting other errors.

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,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
declare
 CURSOR s_cur IS 
     Select COLUMN_NAME from user_tab_columns where table_name='EMP';
     TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
     s_array fetch_array;
    BEGIN
      OPEN s_cur;
      LOOP
        FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
        FORALL i IN 1..s_array.COUNT
            INSERT INTO audit_table (tname,cname,old,new,status,seq)
             VALUES ( UPPER (l_tname), UPPER (l_cname), l_old, l_new,l_status,l_seq);
       EXIT WHEN s_cur%NOTFOUND;
      END LOOP;
  CLOSE s_cur;
  COMMIT;
END;
end;
procedure check_val( l_tname in varchar2,
                     l_cname in varchar2,
	             l_new in Date,
                     l_old in Date,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
declare
 CURSOR s_cur IS 
 Select COLUMN_NAME from user_tab_columns where table_name='EMP';
 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
 s_array fetch_array;
BEGIN
  OPEN s_cur;
  LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
    FORALL i IN 1..s_array.COUNT
    INSERT INTO audit_table (tname,cname,old,new,status,seq)
         VALUES ( UPPER (l_tname), UPPER (l_cname), 
			to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
	        	to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ),l_status,l_seq);
    EXIT WHEN s_cur%NOTFOUND;
  END LOOP;
  CLOSE s_cur;
  COMMIT;
END;
end;
procedure check_val( l_tname in varchar2,
                     l_cname in varchar2,
	             l_new in Number,
                     l_old in Number,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
declare
 CURSOR s_cur IS 
 Select COLUMN_NAME from user_tab_columns where table_name='EMP';
 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
 s_array fetch_array;
BEGIN
  OPEN s_cur;
  LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
    FORALL i IN 1..s_array.COUNT
    INSERT INTO audit_table (tname,cname,old,new,status,seq)
         VALUES ( UPPER (l_tname), UPPER (l_cname), l_old, l_new,l_status,l_seq);
    EXIT WHEN s_cur%NOTFOUND;
  END LOOP;
  CLOSE s_cur;
  COMMIT;
END;
end;
end audit_pkg;
/

Error are:

SQL>show err
Errors for PACKAGE BODY AUDIT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/13    PLS-00435: DML statement without BULK In-BIND cannot be used
         inside FORALL

49/5     PLS-00435: DML statement without BULK In-BIND cannot be used
         inside FORALL

78/5     PLS-00435: DML statement without BULK In-BIND cannot be used
         inside FORALL

I am trying to copy only updated columns with primary key (Old Value, New Value) to audit table and all the columns
of deleted rows (Old Value). Please help me achive this.

Thanks
M. Mohsin

Report message to a moderator

Re: Audit Table with Primary keys [message #577733 is a reply to message #577729]

Wed, 20 February 2013 00:24

sss111ind
Messages: 282
Registered: April 2012
Location: India

Senior Member

It's saying that you are not using any value what you have fetched.That means the following insert is not using s_array's value.So instead of that you can use regular for loop or else you have to use s_array's value only.

INSERT INTO audit_table (tname,cname,old,new,status,seq)
VALUES ( UPPER (l_tname), UPPER (l_cname), l_old, l_new,l_status,l_seq);

[Updated on: Wed, 20 February 2013 00:24]

Report message to a moderator

Re: Audit Table with Primary keys [message #577734 is a reply to message #577729]

Wed, 20 February 2013 00:26

_jum
Messages: 459
Registered: February 2008

Senior Member

The error shows, that you don't use the s_array in FORALL.
As a start, here is a working example how to use FORALL:

DROP   TABLE temp_audit;

CREATE TABLE temp_audit AS 
  SELECT table_name tname, column_name cname 
    FROM user_tab_columns  
   WHERE 1=2;
 
DECLARE
 CURSOR s_cur IS 
   SELECT table_name, column_name FROM user_tab_columns WHERE table_name LIKE '%E%';
 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
 s_array fetch_array;
BEGIN
  OPEN s_cur;
  LOOP
  FETCH s_cur BULK COLLECT INTO s_array LIMIT 100;
    FORALL i IN 1..s_array.COUNT
    INSERT INTO temp_audit (tname, cname)
         VALUES (s_array(i).table_name, s_array(i).column_name);
    EXIT WHEN s_cur%NOTFOUND;
  END LOOP;
  CLOSE s_cur;
END;

Report message to a moderator

Re: Audit Table with Primary keys [message #577770 is a reply to message #577734]

Wed, 20 February 2013 06:18

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Thanks everyone for helping me to resolving this problem.
Here is final working i have done and get the required output. I will appreciate if anyone suggest an other or better way to do this.

here is code.

DROP TABLE emp;
CREATE TABLE emp AS (SELECT * FROM scott.emp);

CREATE TABLE audit_table
(
  ent_date    DATE                              DEFAULT SYSDATE,
  ip_address  VARCHAR2(15)                      DEFAULT SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), 1, 15),
  os_term     VARCHAR2(15)                      DEFAULT SUBSTR (SYS_CONTEXT ('USERENV', 'TERMINAL'), 1, 15),
  os_user     VARCHAR2(15)                      DEFAULT SUBSTR (SYS_CONTEXT ('USERENV', 'OS_USER'), 1, 15),
  tname       VARCHAR2(30),
  cname       VARCHAR2(30),
  OLD         VARCHAR2(2000),
  NEW         VARCHAR2(2000),
  status      VARCHAR2(6),
  seq         NUMBER(6)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
/


create sequence x#_seq;
/

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,
			 l_status in varchar2,
			 l_seq in number
			);

    procedure check_val( l_tname in varchar2, 
                         l_cname in varchar2, 
                         l_new in date, 
                         l_old in date,
			 l_status in varchar2,
			 l_seq in number
			);

    procedure check_val( l_tname in varchar2, 
                         l_cname in varchar2, 
                         l_new in number, 
                         l_old in number, 
			 l_status in varchar2,
			 l_seq in number
			);

end;
/

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,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
    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 (tname,cname,old,new,status,seq)
	     VALUES ( UPPER (l_tname), UPPER (l_cname), l_old, l_new,l_status,l_seq);
    end if;
end;
procedure check_val( l_tname in varchar2, 
		     l_cname in varchar2,
	             l_new in date, 
		     l_old in date, 
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
    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 (tname,cname,old,new,status,seq)
	     VALUES ( upper(l_tname), upper(l_cname),
        	  to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
	          to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ),l_status,l_seq );
    end if;
end;
procedure check_val( l_tname in varchar2, 
		     l_cname in varchar2,
             	     l_new in number, 
	             l_old in number,
		     l_status in varchar2,
		     l_seq in number
		   )
is
begin
    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 (tname,cname,old,new,status,seq)
	     VALUES (upper(l_tname), upper(l_cname),
                                 l_old, l_new,l_status,l_seq );
    end if;
end;
end audit_pkg;
/


create or replace trigger TRIG_EMP
after delete or update on emp
for each row
declare
l_seq   number;
begin
select x#_seq.nextval into l_seq from dual;
	if updating
	then
            BEGIN
                    insert into audit_table values
                    ( sysdate, SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), 1, 15),
                                 SUBSTR (SYS_CONTEXT ('USERENV', 'TERMINAL'), 1, 15),
                                 SUBSTR (SYS_CONTEXT ('USERENV', 'OS_USER'), 1, 15),
                     upper('EMP'), upper('EMPNO'),
                                             :old.EMPNO, :old.EMPNO,'UPDATE',l_seq );
            END;

	    audit_pkg.check_val( 'emp', 'EMPNO', :new.EMPNO, :old.EMPNO,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'JOB', :new.JOB, :old.JOB,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'MGR', :new.MGR, :old.MGR,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'HIREDATE', :new.HIREDATE, :old.HIREDATE,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'SAL', :new.SAL, :old.SAL,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'COMM', :new.COMM, :old.COMM,'UPDATE',l_seq);
	    audit_pkg.check_val( 'emp', 'DEPTNO', :new.DEPTNO, :old.DEPTNO,'UPDATE',l_seq);
	elsif deleting
	then
	    audit_pkg.check_val( 'emp', 'EMPNO', :new.EMPNO, :old.EMPNO,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'JOB', :new.JOB, :old.JOB,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'MGR', :new.MGR, :old.MGR,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'HIREDATE', :new.HIREDATE, :old.HIREDATE,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'SAL', :new.SAL, :old.SAL,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'COMM', :new.COMM, :old.COMM,'DELETE',l_seq);
	    audit_pkg.check_val( 'emp', 'DEPTNO', :new.DEPTNO, :old.DEPTNO,'DELETE',l_seq);
	end if;
end;
/

DML statement

SQL>update emp set comm=200 where empno=7902;
1 row updated.

SQL>commit;

Commit complete.

SQL>delete from emp where empno=7902;

1 row deleted.

SQL>commit;

Commit complete.

Output from audit table is that fullfill my current requirement.

SQL>select * from audit_table;

ENT_DATE  IP_ADDRESS    OS_TER OS_USER         TNAM CNAME    OLD        NEW        STATUS        SEQ
--------- ------------- ------ --------------- ---- -------- ---------- ---------- ------ ----------
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  EMPNO    7902       7902       UPDATE        101
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  COMM                200        UPDATE        101
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  EMPNO    7902                  DELETE        102
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  ENAME    FORD                  DELETE        102
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  JOB      ANALYST               DELETE        102
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  MGR      7566                  DELETE        102
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  HIREDATE 03-dec-198            DELETE        102
                                                             1 00:00:00

20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  SAL      3000                  DELETE        102
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  COMM     200                   DELETE        102

ENT_DATE  IP_ADDRESS    OS_TER OS_USER         TNAM CNAME    OLD        NEW        STATUS        SEQ
--------- ------------- ------ --------------- ---- -------- ---------- ---------- ------ ----------
20-FEB-13 192.168.1.199 MOHSIN Administrator   EMP  DEPTNO   20                    DELETE        102

10 rows selected.

Once again thanks for everyone
M. Mohsin

Report message to a moderator

Previous Topic:	How to handle NULL while inserting records in a table in PL/SQL?
Next Topic:	Removing Leading single quote in excel file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 18 00:52:50 CDT 2013

Total time taken to generate the page: 0.15263 seconds