I've got a situation where I've 2 tables by which I'm able to do Place,Update,Remove.I am presenting a table audit_trail to keep a log of changes of the two tables.How to enter values in audit_trail table I've written place claims after any Update,Remove or Place on either on the table.If an modification happened on among the table and also the application crashed then according to my method the Audit table place won't happen.And So I desired to determine if I write a trigger around the condition after place or update or remove about this table then will this result in an place into audit even when the applying crashes.DBMS is Oracle
This is among the couple of, probably the only, valid ways to use the AUTONOMOUS_TRANSACTION pragma. This enables us to problem SQL inside a discrete transaction, meaning stuff will get committed without having affected the wider transaction. Consequently your audit messages is going to be committed even when the database crashes prior to the user issues an explicit commit (or whatever).
Clearly I'm not sure what data you need to log, but write a process such as this and refer to it as out of your triggers:
procedure write_audit (p_table_name in audit_table.table_name%type , p_action in audit_table.action%type ) is pragma autonomous_transaction; begin insert into audit_table (id, table_name, ts, action) values (audit_id.nextval, p_table_name, systimestamp, p_action); commit; end write_audit;
You have to move your place right into a saved procedure and call that from inside the audit trail trigger.
Within the saved procedure enable "autonomous transaction", and you can commit within the saved procedure without having affected the "outer" transaction that fired the trigger.
For particulars begin to see the manual: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1514