At 04:13 AM 23-02-02 +0000, abizafu wrote:

>I want [rollback] to happen inside the trigger I need to be able to
>encapsulate all behaviour on the server and just return a sucess/no
>success code. I want to rollback/end the transaction on the server,
>inside the trigger. The calling app us just waiting for a result of
>failure or success as a message.

It cannot be done as you visualise it. A transaction is a conversation
between the client and the server. The client is responsible for both
starting and ending (by commit or rollback) the transaction. You can't
commit or rollback a transaction from inside a stored procedure/trigger.

Paul's suggestion is your solution to achieve what you want.

If an SQL exception occurs during the execution of the Before Delete
trigger, the engine's exception-trapping mechanisms will prevent the
trigger from firing and return from the Commit call without committing.

Now, if your situation is that you want to have an exception occur in the
as the result of some test that is NOT an SQL error, then create an
Exception object in your database for it, e.g.

create exception E_Validate 'FAILURE';

then, in your trigger code, test the conditions and raise this exception:
...
if ( exists (select aCode from othertable where aCode = old.aCode)) then
Exception E_Validate;

You want this:
"The calling app is just waiting for a result of failure or success as a
message."

If the test fails, the Commit will fail and control will return to the
client. The text of the exception message will be passed across the API in
the status_vector. You could have your application read this message if
you want.

However, a failed Commit doesn't cause a Rollback. The transaction remains
active until it either commits successfully or is rolled back by the
client. If you want to roll back the transaction regardless of any error
that occurred, simply have your application test whether the transaction is
still active after Commit was called, and just call Rollback to end it.

So your "failure or success message" could be as simple as the result you
get from testing whether the transaction is still alive. If you want to,
you can read the status_vector to isolate the error that caused the
exception and report it to the user; and you could include some logic to
roll back in the case of certain exceptions or just abort the Commit in the
case of others.

regards,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________