Problem solve Get help with specific problems with your technologies, process and projects.

Creating a trigger that is executed only after table insert has been committed

I wish to create a trigger in Oracle that is only executed once an insert to a table has been committed. The trigger...

executes a procedure, which in turn calls a Java process that notifies a listener that there is data available. However the listener is querying the table before the commit has taken place and hence no data is found.

A trigger's work is committed or rolled back depending on the transaction that called the trigger. For example, let's suppose that a user inserts a row into Table_A. A trigger on this table forces an insert into Table_B. Now if the original transaction commits, then both inserts into A & B commit. If the original transaction is rolled back, then both inserts are rolled back. So your insert into the other table will only be successfull if your original transaction is committed.

But it sounds like the real item you need to pull off your requirements is to use Advanced Queueing (AQ). AQ let's you put messages for other processes into tables. Other processes can read these messages and then take appropriate action, depending on the message content.

For More Information

Dig Deeper on Oracle database design and architecture