tag:blogger.com,1999:blog-697307722356110163.post3539244846341785711..comments2023-10-19T08:14:15.629-04:00Comments on Halimdba: How to track column level value change in oracle by a generic triggerhalimdbahttp://www.blogger.com/profile/06064045814557676169noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-697307722356110163.post-5296279682159334722012-05-15T11:45:23.610-04:002012-05-15T11:45:23.610-04:00MY DBA ALWAYS HATS ORACLE TRIGGERS SO WANTED TO CO...MY DBA ALWAYS HATS ORACLE TRIGGERS SO WANTED TO CONVERT TRIGGER TO PROCEDURE.<br />THANKS FOR ANY HELP.balahttps://www.blogger.com/profile/15329231188581677775noreply@blogger.comtag:blogger.com,1999:blog-697307722356110163.post-47837065992314035792012-05-15T11:42:07.867-04:002012-05-15T11:42:07.867-04:00WANT TO CONVERT THE SAME CODE IN ORACLE PROCEDURES...WANT TO CONVERT THE SAME CODE IN ORACLE PROCEDURES , THANKS IN ADVANCE FOR YOUR HELP.<br /><br />create or replace TRIGGER <br />AFTER INSERT OR UPDATE OR DELETE ON emp<br /> FOR EACH ROW<br />BEGIN<br /> IF INSERTING THEN<br />INSERT INTO emp_audit<br />(emp_id , salary,created_ts,CREATED_user,MODIFIED_TS,MODIFIED_usER, archived_ts, event_cd)<br />VALUES (<br />:NEW.emp_id, <br />:NEW.salary, <br />:NEW.CREATED_TS,<br />:NEW.CREATED_user, <br />:NEW.MODIFIED_TS,<br />:NEW.MODIFIED_usER, <br /> systimestamp, 'C'); -- INSERT <br /> ELSIF UPDATING THEN <br />INSERT INTO emp_audit<br />(emp_id , salary,created_ts,CREATED_user,MODIFIED_TS,MODIFIED_usER, archived_ts, event_cd)<br />VALUES (<br />:OLD.emp_id, <br />:OLD.salary, <br />:OLD.CREATED_TS,<br />:OLD.CREATED_user, <br />:OLD.MODIFIED_TS,<br />:OLD.MODIFIED_usER, <br /> systimestamp, 'U'); -- UPDATE<br /> ELSIF DELETING THEN<br />INSERT INTO emp_audit<br />(emp_id , salary,created_ts,CREATED_user,MODIFIED_TS,MODIFIED_usER, archived_ts, event_cd)<br />VALUES (<br />:OLD.emp_id, <br />:OLD.salary, <br />:OLD.CREATED_TS,<br />:OLD.CREATED_user, <br />:OLD.MODIFIED_TS,<br />:OLD.MODIFIED_usER, <br /> systimestamp, 'D'); -- DELETE<br />END IF;<br /><br />END;balahttps://www.blogger.com/profile/15329231188581677775noreply@blogger.comtag:blogger.com,1999:blog-697307722356110163.post-4163882355394819362012-05-15T11:34:21.986-04:002012-05-15T11:34:21.986-04:00thank you so much for your posting.
I would like t...thank you so much for your posting.<br />I would like to keep track of table level column changes like insert /update/delete for a table<br /><br />table : emp<br />column1: emp_id<br />column 2: emp_name<br />column 3 : emp_increment<br />column 4 : archive_ts<br />column 5 : event_cd ( i.e I/U/D I - INSERT / U - UPDATE / D - DELETE<br /><br />audit should be <br /><br />ID NAME increment ARCHIEVE_TS EVENET_CD<br />100 X 10 21-JAN-2012 I<br />100 X 15 22-JAN-2012 U<br /><br />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<br /><br />PLEASE DO THE NEEDFULbalahttps://www.blogger.com/profile/15329231188581677775noreply@blogger.com