• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Devaka Cooray
  • Ron McLeod
  • Jeanne Boyarsky
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Martijn Verburg
  • Frits Walraven
  • Himai Minh

Trigger after transaction

 
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Today's lesson is that you can't wear a jetpack AND a cape. I should have read this tiny ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic