We help IT Professionals succeed at work.


sam15 asked
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?
Watch Question

Triggers. They are so used for your specific requirement that they are commonly named "audit triggers". No DDL (CTAS or anything), just inserts much like you provided, probably adding at least three fields: USER and SYSDATE and WHAT (which can be populated asking within the trigger "IF INSERTING THEN...", "IF UPDATING THEN", etc., or just creating separate triggers for INSERT, UPDATE, and DELETE).

For additional audit-related fields, look into SYS_CONTEXT. If your scenario is client/server, SYS_CONTEXT ('USERENV', 'IP_ADDRESS') is a good example.

Finally, consider using PRAGMA AUTONOMOUS_TRANSACTION on your audit triggers, so that your audit records get written no matter whether the user rolls back the changes (you may want this or not). A nice example (precisely with audit triggers!) is here.
I would suggest to use FLASHBACK for this purposes. Alternatively you can use Oracle built-in auditing.


cant use flashback with 9i.

So i need to do "before update" trigger to capture the OLD values only? and then compare to the current last record in table to see the difference between old and current. right?

I am only interested in committed data too. If transaction rooed back i do not need to audit.

do you have a good link that shows that too?
Yes, do BEFORE UPDATE FOR EACH ROW and capture :OLD values (or compare with :NEW etc.). You can also add another trigger BEFORE INSERT and yet another BEFORE DELETE if you are so inclined :-)

If you are just interested in committed data, then do NOT use autonomous transactions... in other words, don't do anything special. Whatever your audit trigger did will be rolled back with the transaction if it is cancelled etc.

The link is the same one... just don't say PRAGMA AUTONOMOUS_TRANSACTION and remove the COMMIT at the end. Again, when the main transaction gets committed/rolled back, your trigger's changes will also get committed/rolled back with it.