How to define a trigger ON COMMIT in Oracle?

Is there any way in oracle database to define trigger which will be fired synchronously before COMMIT (and ROLLBACK if it throws exception) in case when specified table is changed?

Answers


There is no ON COMMIT trigger mechanism in Oracle. There are workarounds however:

  1. You could use a materialized view with ON COMMIT REFRESH and add triggers to this MV. This would allow you to trigger the logic when a base table has been modified at the time of commit. If the trigger raises an error, the transaction will be rolled back (you will lose all uncommited changes).

  2. You can use DBMS_JOB to defer an action to after the commit. This would be an asynchronous action and may be desirable in some cases (for example when you want to send an email after the transaction has been successful). If you roll back the primary transaction, the job will be cancelled. The job and the primary session are independent: if the job fails the main transaction will not be rolled back.

In your case, you could probably use option (1). I personnaly don't like to code business logic in triggers since it adds a lot of complexity but technically I think it would be doable.


I had a similiar problem, but option 1 was unfortunately not convenient for my case.

Another possible solution, which is also suggested by "Ask Tom", is to specify a stored procedure and simply call that procedure before executing the COMMIT. This solution is only convenient if you have access to the code which executes the COMMIT, but for my case this was the easiest solution.


Need Your Help

play audio from internet using AVAudioPlayer

ios objective-c streaming avaudioplayer

I am implementing AVAudioPlayer to play audio and it works perfectly well while playing files locally stored in the PC.

How do I correctly use setInterval and clearInterval to switch between two different functions?

javascript animation counter setinterval clearinterval

For practice I am trying to display a number that increments from 0 - 9, then decrements from 9 - 0, and infinitely repeats.The code that I have so far seems to be close, but upon the second iterat...