Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Trigger after transaction

 
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic