In Sybase ASE I have a few audit tables and triggers sitting on the primary tables. The audit is very simple, there is a trigger on insert/update/delete for each table and it simple inserts an identical row with additional column which marks/flags whether the operation was an insert, update or delete, the user and the time.
Now auditing is not critical, so if for any reason the audit trigger fails then we would like to avoid killing the whole operation which fired the trigger.
Say TableA has TriggerA which inserts into TableB.
We execute StatementX on TableA which fires TriggerA consisting of StatmentsY, TriggerA fails on SatementsY to insert into TableB. Now the normal pattern is to roll back both StatementX and StatementY to ensure integrity.
Is there any way to say create a save point inside the trigger so that if StatementY fails, there is no break to StatementX and StatementX is still committed (i.e. only StatementY is rolled back)?
I think that is not possible.
A trigger fires only after the data modification statement has completed and Adaptive Server has checked for any datatype, rule, or integrity constraint violations. The trigger and the statement that fires it are treated as a single transaction that can be rolled back from within the trigger. If a severe error is detected, the entire transaction is rolled back.
For more info, check THIS.
ADDED: You can also check THIS for info on rollback triggers but I think it's not exactly what you want.