Oracle: simulating a "post-commit" trigger.

Question

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

Answer 1

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

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;
/

Answer 2

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

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;

Answer 3

Using Oracle Advanced Queueing you can enqueue an array of records with a listener on the queue table.

The records will load, and the listener can then kick of any process you wish, even a web service call

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_aq.htm#i1001754