Oracle：如何在不延迟插入响应的情况下执行插入触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1244636/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: How to execute an insert trigger without delaying the insert response?
提问by Jader Dias
The trigger below is delaying my insert response. How can I prevent this?
下面的触发器延迟了我的插入响应。我怎样才能防止这种情况？
create or replace
TRIGGER GETHTTPONINSERT
BEFORE INSERT ON TABLENAME
FOR EACH ROW
Declare
--
BEGIN
-- The inserted data is transfered via HTTP to a remote location
END;
EDITPeople are telling me to do batch jobs, but I would rather have the data earlier than having 100% consistency. The advantage of the trigger is that it happens as soon as the data arrives, but I can't afford the insert response delay.
编辑人们告诉我做批处理作业，但我宁愿拥有比 100% 一致性更早的数据。触发器的优点是数据一到就发生，但是我承受不起插入响应的延迟。
回答by dpbradley
One approach is to have the trigger create a dbms_job that runs once (each) time to perform the http transfer. The dbms_job creation is relatively quick and you can think of this as effectively spawning a new thread in parallel.
一种方法是让触发器创建一个 dbms_job，该作业（每次）运行一次以执行 http 传输。dbms_job 创建相对较快，您可以将其视为有效地并行生成新线程。
See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7267435205059for further info - his example deals with sending email, but the idea is the same.
有关 更多信息，请参阅http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7267435205059- 他的示例处理发送电子邮件，但想法是相同的。
回答by Stephanie Page
There is a perfect solution for this exact situation called Database Change Notification.
对于这种确切情况，有一个完美的解决方案，称为数据库更改通知。
You can think of it almost exactly like an async trigger.
你几乎可以把它想象成一个异步触发器。
You use the DBMS_Change_Notification package to tell oracle which tables to watch and what to do when a change occurs. You can monitor for DML and DDL, you can have Oracle batch the changes (i.e. wait for 10 changes to occur before firing). It will call a sproc with an object containing all the rowids of the changed rows... you can decide how to handle, including calling HTTP. It will not have to finish for the insert to commit. Documentation for 10gR2
您可以使用 DBMS_Change_Notification 包告诉 oracle 要监视哪些表以及发生更改时要执行的操作。您可以监视 DML 和 DDL，您可以让 Oracle 批量更改更改（即在触发之前等待 10 个更改发生）。它将调用一个 sproc，其中包含一个包含更改行的所有 rowids 的对象……您可以决定如何处理，包括调用 HTTP。不必完成插入即可提交。 10gR2 的文档
回答by Jonathan
Maybe you could create a local table that store the info do you have to transfer, and create a job that executes every X minutes. The job read from the table, transfer all the data and delete the transfered data from the table.
也许您可以创建一个本地表来存储您必须传输的信息，并创建一个每 X 分钟执行一次的作业。作业从表中读取，传输所有数据并从表中删除传输的数据。
回答by tuinstoel
Isn't it possible to use the Oracle replication options? You send your inserted data via http to a remote location in an after or before statement trigger. What will happen when there is a rollback? Your hhtp send message will not be rollbacked so you have inconsistent data.
不能使用 Oracle 复制选项吗？您可以在 after 或 before 语句触发器中通过 http 将插入的数据发送到远程位置。发生回滚时会发生什么？您的 hhtp 发送消息不会被回滚，因此您的数据不一致。
回答by guigui42
well obviously, you could prevent the delay by removing the Trigger.... Else, the trigger will ALWAYS be executed before your insert, thats what the TRIGGER BEFORE INSERT is made for.
显然，您可以通过删除触发器来防止延迟......否则，触发器将始终在插入之前执行，这就是插入前触发器的用途。
Or maybe you could give us more details on what you need exactly?
或者，也许您可以向我们提供有关您确切需要的更多详细信息？
回答by YotamWIS Constantini
If you are getting to this question after 2020, look at DBMS_CQ_NOTIFICATION:
如果您在 2020 年之后遇到这个问题，请查看 DBMS_CQ_NOTIFICATION：
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CQ_NOTIFICATION.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CQ_NOTIFICATION.html
