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

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

• Dozens more answers to tough Oracle questions from Brian Peasland are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.