RE: Exceptions in Triggers

[Waqas Zafar Khan <a....@gmail.com>]

Hello,

 

I was just wondering what the syntax was for raising an exception in a
trigger, I did a search in the developers manual and only saw this
information:

 

Aborting statements and transactions

You might want a trigger action to be able to abort the triggering statement
or even the entire transaction.

Triggers that use the current connection are not permitted to commit or roll
back the connection, so how do you do that? The answer is: have the trigger
throw an exception, which is by default a statement-level exception (which
rolls back the statement). The application-side code that contains the
statement that caused the trigger to fire can then roll back the entire
connection if desired. Programming triggers in this respect is no different
from programming any database-side JDBC method.

Could the above please be expanded on particularly the point where it
mentions the programming of the exception.

 

Regards,

 

Waqas aka Zafar

 

Re: Exceptions in Triggers

[Rick Hillegas <Ri...@Sun.COM>]

Hi Waqas,

This section of the Developer's Guide is talking about triggers which 
fire database procedures. A Derby database procedure is just a public 
static Java method which you declared using the CREATE PROCEDURE 
statement. Like any Java method, that triggered method can throw a plain 
old Java exception. No magic here, nothing fancy. If your public static 
method throws an exception, then Derby  does the following:

1) Catches the exception
2) Rolls back the transaction to the point just before the triggering 
statement. The triggering statement is the INSERT or UPDATE which fired 
the trigger which, in turn, called your public static method.
3) Wraps your original exception in a SQLException and re-throws it.

Hope this helps,
-Rick

Waqas Zafar Khan wrote:
>
> Hello,
>
>  
>
> I was just wondering what the syntax was for raising an exception in a 
> trigger, I did a search in the developers manual and only saw this 
> information:
>
>  
>
> *Aborting statements and transactions *
>
> You might want a trigger action to be able to abort the triggering 
> statement or even the entire transaction.
>
> Triggers that use the current connection are not permitted to commit 
> or roll back the connection, so how do you do that? The answer is: 
> have the trigger throw an exception, which is by default a 
> statement-level exception (which rolls back the statement). The 
> application-side code that contains the statement that caused the 
> trigger to fire can then roll back the entire connection if desired. 
> Programming triggers in this respect is no different from programming 
> any database-side JDBC method.
>
> Could the above please be expanded on particularly the point where it 
> mentions the programming of the exception.
>
>  
>
> Regards,
>
>  
>
> Waqas aka Zafar
>
>  
>