CODEWITHSUNDEEP
oracle-databasetriggers
bonzaikarukera
bonzaikarukera

Reputation: 1

ensure row inserted if trigger failed

I have oracle 11G trigger before insert that is not critical; the actions in it are not required. On the other hand, it is very important that the row that called the trigger is inserted correctly in the table

I wish know if there is a way to ensure that the row will be inserted in the table even if the trigger failed?

I can not configure the trigger to an after insert because it's linked in cascade to another trigger before insert that have to be fired first

Thanks in advance for your help

Upvotes: 0

Views: 403

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Three general approaches.

1) If you don't care whether the action that the trigger performs succeeds, then you don't need the trigger in the first place. If the trigger never running would be a problem, then you do care whether the trigger succeeds, you should take care that it is coded correctly, and if occasionally something goes wrong, you should accept that.

2) Let the trigger cause an asynchronous operation that can succeed or fail separately from the triggering operation. This could mean that your trigger submits a job using dbms_job that runs after the triggering transaction commits, it could mean that the trigger writes a row to a separate table/ AQ that a separate process reads from and does something. When the asynchronous process fails, that can be debugged separately while the triggering process is still running.

3) Add a when others exception handler to the trigger that does something useful to log the exception and alert people that there was an error without failing. Unfortunately, all too often, this approach ends up writing a row to a table that no one ever reads so no one ever knows that the operation failed. If no one knows that the operation failed, no one knows that it needs to be fixed, and you're basically back in the state you were in in option 1 where you really ought to just remove the code.

Upvotes: 1

Related Questions