Trigger after transaction

[chandana sapparapu]

Hi, I would like to create a trigger after a transaction. I know there are triggers for before-insert, after-insert of rows in a table, but I want a trigger to be generated after the entire transaction commits. Any ideas?

TIA.

[JulianInactive KennedyInactive]

Hi Chandana,

Am I right in thinking you're using Oracle, given before and after triggers? Is there just one table in your transaction? Is it just inserts? Why can't you use an after trigger or a combination of them?

Jules

[chandana sapparapu]

Julian,
I am working for a product development team, so I can't assume that the user will use a particular database.

I am not using just one table. I am inserting in two or more tables, and I want a trigger to be generated after the transaction is committed. I set connection's autocommit to false, and commit at the end of all the operations.
I would like to generate a trigger after the transaction is committed.

I want the trigger after the transaction because the trigger handler should read from some of the tables involved in the transaction. This is not happening if I use an after-insert trigger, since I set the autocommit to false.

Thanks.
[ August 12, 2004: Message edited by: chandana sapparapu ]

[JulianInactive KennedyInactive]

OK, which RDBMS are you testing on? What are the target platforms for your product?

I think I'm right in saying that not all RDBMS have before and after triggers. Certainly the implementations are quite different.

You could use a stored procedure and manage the transaction within it but that limits your potential target platforms and more-or-less requires you to write the code separately for each one. You get the same problem with triggers though. There are two performance advantages of the SP route: 1) the SQL is pre-compiled; 2) all processing is done on the DB minimising network latency and context-switching overhead.

The only way I can see to achieve what you want and remain totally platform-neutral is to run the query you want in your "trigger" from the Java after the commit returns. Is there an issue with doing it that way?

Regards

Jules