--- In firebird-support@yahoogroups.com, "Stephen Davey" <stephen@...>
wrote:

>
> Could someone please clarify the concept of transaction termination.
>
> In my understanding, transactions are explicitly started in Delphi

with the command 'StartTransaction' and are ended with 'Commit'.

>
> So when I write an invoice transaction to a table it is as follows,
>
> Commit ; StartTransaction ;
>
> Save data using a stored procedure ....
>
> Commit ;
>
> The invoice line item is written to the table.
>
> During this process a triggers executes, calling a stored procedure,

and the quantity on hand in the stock master file is updated.

>
> The stock masterfile in turn uses a trigger to append a cost change

history log table.

>
> The question is, are all the triggers and stored procedures that are

executed down through multiple tables all contained within the one
transaction ?

Correct. Triggers and Stored Procedure run in the context of the
transaction that was used by the query that called them or ran the
query that caused them to fire.

So your quantity on hand adjustment in the master table is in the same
transaction as the invoice item.

As far as the syntax goes, you should not need to commit before a
start transaction if no transaction is active. You should also make
sure using your programming languages exception handling that you
don't leave the transaction open if there is some exception.

eg (in Delphi, most languages have equivalent)

Con.Open;
try
Tra.StartTransaction;
try
....
run query
....
Tra.Commit;
except
Tra.RollBack;
raise;
end;
finally
Con.Close;
end;

If you follow a similar pattern, then you should never encounter an
open transaction.

Adam