如何在 Oracle 中定义触发器 ON COMMIT?

2021-12-24 00:00:00 sql oracle triggers plsql

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.

相关文章