How to define a trigger ON COMMIT in Oracle?

Question

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?

Answer 1

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.

Answer 2

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.