如何在Oracle中定义ON COMMIT触发器? [英] How to define a trigger ON COMMIT in Oracle?

问题描述

oracle数据库中是否有任何方法可以定义触发器，以防在更改指定表时在COMMIT(如果引发异常，则返回ROLLBACK)之前同步触发?

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?

推荐答案

Oracle中没有ON COMMIT触发机制.但是，有一些解决方法:

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

1. 您可以将物化视图与ON COMMIT REFRESH一起使用，并将触发器添加到此MV.当在提交时修改了基表时，这将允许您触发逻辑.如果触发器引发错误，则事务将回滚(您将丢失所有未提交的更改).

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).

您可以使用DBMS_JOB将操作推迟到提交之后.这将是一个异步操作，在某些情况下(例如，当您希望在交易成功后发送电子邮件时)可能是理想的.如果您回滚主要事务，则该作业将被取消.作业和主会话是独立的:如果作业失败，主事务将不会回滚.

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.

在您的情况下，您可能可以使用选项(1).我个人不喜欢在触发器中编写业务逻辑代码，因为它增加了很多复杂性，但是从技术上讲，我认为这是可行的.

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.

这篇关于如何在Oracle中定义ON COMMIT触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！