Do triggers maintain atomicity even if the application crashes

Question

I have a situation where I have 2 tables in which I can do Insert,Update,Delete.I'm introducing a table audit_trail to maintain a log of changes of this two tables.Now to enter values in audit_trail table I have written insert statements after any Update,Delete or Insert on either of the table.Now if an modification happened on one of the table and the application crashed then based on my method the Audit table insert will not happen.So I wanted to know if I write a trigger on the condition after insert or update or delete on this table then will this lead to an insert into audit even if the application crashes.DBMS is Oracle

Answer 1

You need to move your insert into a stored procedure and call that from within the audit trail trigger.

Inside the stored procedure enable "autonomous transaction", and then you can commit inside the stored procedure without affecting the "outer" transaction that fired the trigger.

For details see the manual: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1514

Answer 2

This is one of the few, possibly the only, valid uses for the AUTONOMOUS_TRANSACTION pragma. This allows us to issue SQL in a discrete transaction, which means that stuff gets committed without affecting the wider transaction. Consequently your audit messages will be committed even if the database crashes before the user issues an explicit commit (or whatever).

Obviously I don't know what data you want to log, but write a procedure like this and call it from 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;