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

As you need to trigger an external process, have a look at DBMS_ALERT instead of 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.