Oracle：模拟“提交后”触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3765555/
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: simulating a "post-commit" trigger
提问by Mark Harrison
How can I get the equivalent of an "on commit" trigger after inserting some rows into a table?
将一些行插入表中后，如何获得相当于“提交时”触发器的效果？
After inserting several rows into a table, I would like to send a message to an external process that there are rows ready to process. Using a statement-level trigger causes one message per insert, and I would like to send just one message saying "there are rows to be processed."
在表中插入几行后，我想向外部进程发送一条消息，表明有行可以处理。使用语句级触发器会导致每次插入一条消息，我只想发送一条消息说“有行要处理”。
采纳答案by HAL 9000
As you need to trigger an externalprocess, have a look at DBMS_ALERTinstead of DBMS_JOB.
由于您需要触发外部进程，请查看DBMS_ALERT而不是 DBMS_JOB。
The external process would actively listen on the alert by calling a stored procedure. The stored procedure would return immediately after the alert has been signalled and commited.
外部进程将通过调用存储过程主动侦听警报。在发出警报并提交警报后，存储过程将立即返回。
Note that DBMS_ALERT is a serialization device. Thus, multiple sessions signalling the same alert name will block just as like they update the same row in a table.
请注意，DBMS_ALERT 是一个序列化设备。因此，发出相同警报名称的多个会话将被阻塞，就像它们更新表中的同一行一样。
回答by Jon Heller
Create a job. It won't actually be submitted until a commit occurs. (Note: DBMS_SCHEDULER is usually better than DBMS_JOB, but in this case you need to use the old DBMS_JOB package.)
创建工作。在提交发生之前，它实际上不会被提交。（注意：DBMS_SCHEDULER 通常比 DBMS_JOB 更好，但在这种情况下，您需要使用旧的 DBMS_JOB 包。）
declare
jobnumber number;
begin
dbms_job.submit(job => jobnumber, what => 'insert into test values(''there are rows to process'');');
--do some work here...
commit;
end;
/
回答by Gary Myers
You can set a flag to say "I've sent the message". To be sure you 'reset' the flag on commit, use dbms_transaction.local_transaction_id then you can simply do a
您可以设置一个标志来表示“我已发送消息”。为确保您在提交时“重置”标志，请使用 dbms_transaction.local_transaction_id 然后您可以简单地执行
IF v_flag IS NULL OR dbms_transaction.local_transaction_id != v_flag THEN
v_flag := dbms_transaction.local_transaction_id;
generate message
END IF;
回答by Stephen Bealer
Using Oracle Advanced Queueing you can enqueue an array of records with a listener on the queue table.
使用 Oracle Advanced Queuing，您可以使用队列表上的侦听器将一组记录排入队列。
The records will load, and the listener can then kick off any process you wish, even a web service call
记录将加载，然后侦听器可以启动您希望的任何进程，甚至是 Web 服务调用
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_aq.htm#i1001754
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_aq.htm#i1001754
