CODEWITHSUNDEEP
oracle-database
Mark Harrison
Mark Harrison

Reputation: 304444

Oracle: simulating a "post-commit" trigger

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

Upvotes: 4

Views: 7289

Answers (4)

Stephen Bealer
Stephen Bealer

Reputation: 31

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 off any process you wish, even a web service call

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

Upvotes: 1

HAL 9000
HAL 9000

Reputation: 3985

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.

Upvotes: 3

Gary Myers
Gary Myers

Reputation: 35401

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;

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36807

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

Upvotes: 7

Related Questions