aspose file tools*
The moose likes JDBC and the fly likes Trigger after transaction Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trigger after transaction" Watch "Trigger after transaction" New topic
Author

Trigger after transaction

chandana sapparapu
Ranch Hand

Joined: Sep 28, 2002
Posts: 63
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.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
Ranch Hand

Joined: Sep 28, 2002
Posts: 63
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 ]
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trigger after transaction
 
Similar Threads
How to make the trigger work in hibernate?
A doubt with triggers
Help me in creating a Trigger for Insert and Update Options
Trigger
inserting data into two tables simultaniously