Triggers and Transaction behaviour

[Alexander Schatten <al...@gmx.at>]

I could not find proper information in the manuals about triggers and 
transactions.


If e.g., within a transaction, say, a insert statement is performed, and 
this statement fires off a trigger, and then later on, the transaction 
is rolled back: what about the activities of the trigger?


Is there some more detailed document about this behaviour?



thank you!



Alex

Re: Triggers and Transaction behaviour

[Alexander Schatten <al...@gmx.at>]

Stanley Bradbury wrote:
> Hi Alex -
> The answer depends on the type of trigger.  A transaction occurs 
> within a single connection.  For simple, single SQL statement triggers 
> the trigger activities are part of the transaction (execute within the 
> context of the current connection) and are rolled back.  Should a SQL 
> statement trigger fail it raises an exception that is passed to the 
> calling routine.  This is covered here:
> http://db.apache.org/derby/docs/dev/devguide/cdevspecial49460.html

thank you. that answers my question.

Re: Triggers and Transaction behaviour

[Stanley Bradbury <St...@gmail.com>]

Alexander Schatten wrote:
> I could not find proper information in the manuals about triggers and 
> transactions.
>
>
> If e.g., within a transaction, say, a insert statement is performed, 
> and this statement fires off a trigger, and then later on, the 
> transaction is rolled back: what about the activities of the trigger?
>
>
> Is there some more detailed document about this behaviour?
>
>
>
> thank you!
>
>
>
> Alex
>
Hi Alex -
The answer depends on the type of trigger.  A transaction occurs within 
a single connection.  For simple, single SQL statement triggers the 
trigger activities are part of the transaction (execute within the 
context of the current connection) and are rolled back.  Should a SQL 
statement trigger fail it raises an exception that is passed to the 
calling routine.  This is covered here:
http://db.apache.org/derby/docs/dev/devguide/cdevspecial49460.html

When the trigger calls a JDBC function then the answer depends on how 
the JDBC is coded.  The background for considering this type of trigger 
is laid here:
http://db.apache.org/derby/docs/dev/devguide/cdevspecial93497.html
NOTE:  I find it best to have trigger code always share the transaction 
space (#1).

You can code the JDBC two ways.
 >> 1. Coding to share the transaction space:
http://db.apache.org/derby/docs/dev/devguide/cdevspecial29620.html

 >> 2. Coding that operates outside of the transaction space:
http://db.apache.org/derby/docs/dev/devguide/cdevspecial16181.html