Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to track column level value change in oracle using procedures

user4485803May 15 2012 — edited May 16 2012
I would need to write the below code using oracle stored procedures & packages.
DBA hates triggers so i cant enforce the same code.
Audit table would be emp_audit.
Please guide me to write some generic procedure to handle the audit , what ever insert/update/delete.
Please do the needful. thanks in advance.
I have created below trigger but oracle DBA dont want to use triggers. Please guide me.

Master table : emp
===================
columns:
=======
emp_id
salary
created_ts,
created_user,
modified_ts,
modified_user,

Audit Table : emp_audit
=======================

columns:
=======
emp_id ,
salary,
created_ts,
created_user,
modified_ts,
modified_user,
archived_ts,
event_cd

trigger: Need in the form of stored procedures.
========

create or replace TRIGGER test
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;

thanks,

bala
This post has been answered by Stew Ashton on May 16 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 13 2012
Added on May 15 2012
9 comments
1,658 views