We have a web application that updates several DEPARTMENTS/EMPLOYEES records in DEPT and EMP table in one transaction.,
I am concerned about insufficient testing and some bugs may arise later so i want to take a snapshot of the old record before the data is updated so i can copy the record if an address got erased or any other issues arise later. A nightly backup would not be helpful because it it co0ies the current state of the record which might be corrupt.
What would be the best and simplest way to do it.
I am thinking of adding a CTAS statement before the update statement in the application like
after i create a mirror audit table and add a few columns to track who, when, what
INSERT into DEPARTMENT_AUDIT select col1,col2,col3 from DEPRATMENT where dept_id='HIST';
INSERT into EMPLOYEE_AUDIT select col1,col2, col3 from EMPLOYEES where emp_id=73222;
My other option is to do a before insert trigger. This might be better because it would be separate from application and would audit any statement externally too.
I am not sure if i can use CTAS in the trigger. I think i may need to use the :OLD values and have a regular INSERT statement.
ANy ideas or recommendations?