How to track column level value change in oracle using procedures
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