Board index » delphi » Rollback in Trigger versus Rollback in Application

Rollback in Trigger versus Rollback in Application

Dear,

to execute a query I open a transaction using TDataBase.StartTransaction.

Some rules I verify using triggers. In the trigger I do ROLLBACK, but the
property TDataBase.InTransaction continue with TRUE. When I try COMMIT and
ROLLBACK, Delphi raises an exception.

I am working with MS SQL Server and Delphi 3.0.

I try disconnect and connect the TDataBse but I get other problems.

How would I do to set the property TDataBase.InTransaction to False without
use TDataBase.COMMIT or TDataBase.ROLLBACK ? In fact, there isnt the
transaction because the trigger have already finished it.

Thanks,

Luis Filipe.

 

Re:Rollback in Trigger versus Rollback in Application


I don't known why MS SQL allows transaction control statements in triggers.
Triggers must be atomic operations even if fires another triggers,
regardless of the controlling transaction. If you want standard behaviour
you must remove trnsaction control statements from your triggers.

On the other hand, client applications via BDE can't tell if there is an
active transaction since was finished (with commit or rollback) internally
by the server, not by the client application. May be there is a MS SQL Api
witch can tell you if there is an active transaction but you must down from
Delphi to BDE APIs to get native DB handle an then call a MS SQL Api. Take a
look at your DBLib documentation.

Regards.

CAC escribi en mensaje <7mitq0$3i...@forums.borland.com>...

Quote
>Dear,

>to execute a query I open a transaction using TDataBase.StartTransaction.

>Some rules I verify using triggers. In the trigger I do ROLLBACK, but the
>property TDataBase.InTransaction continue with TRUE. When I try COMMIT and
>ROLLBACK, Delphi raises an exception.

>I am working with MS SQL Server and Delphi 3.0.

>I try disconnect and connect the TDataBse but I get other problems.

>How would I do to set the property TDataBase.InTransaction to False without
>use TDataBase.COMMIT or TDataBase.ROLLBACK ? In fact, there isnt the
>transaction because the trigger have already finished it.

>Thanks,

>Luis Filipe.

Re:Rollback in Trigger versus Rollback in Application


Hi CAC!

On Wed, 14 Jul 1999 18:13:09 -0300, "CAC"

Quote
<cacce...@rio.nutecnet.com.br> wrote:
>Some rules I verify using triggers. In the trigger I do ROLLBACK, but the
>property TDataBase.InTransaction continue with TRUE. When I try COMMIT and
>ROLLBACK, Delphi raises an exception.

If you started transaction from the application than you should also
roll it back from the application. That's a common logic. If you roll
back transaction in a triger, how can you be sure that the transaction
is running. This triger my be fired from the ISQL when you manualy
issue some update on the database.

IMHO, better approach for your problem would be to let triger fires an
exception (with raiserror in MSSQL) and than in the application side
you can catch that exception and rollback transaction.

tomi.

Other Threads