At 02:57 PM 13/01/2007, you wrote:

>Hi Everyone,
>
>I have multiple DELETEs, UPDATEs in some of my SPs. For example
>
>CREATE PROCEDURE DO_STUFF
>AS
>BEGIN
>
> DELETE FROM USERS WHERE ....
>
> DELETE FROM ROLES WHERE...
>
> UPDATE LOG WHERE ...
>
>END
>
>Are all these statements run in the same transaction when I can the SP?

Yes

>Or do I have to explicitly put BEGIN...COMMIT TRAN in the SP?

No. There isn't even a legal syntax to do so.

>Can someone please show me a small example of nested transactions in
>Firebird 2.0(I'm mostly interested in the SQL syntax)?

Study the way exception handling works: it provides a mechanism for
doing work in stages and taking actions to respond to various
exception conditions. Since you can create your own, named
exceptions you have a lot of scope to design conditionally nested
structures to suit your needs. (If you are in Russia you can get
hold of a copy of the Russian translation of The Firebird Book: this
mechanism is discussed in some detail.)

Bear in mind that, because all processing performed in a SP is under
transaction control, *except* writes to an external table, all work
performed by the procedure will roll back if the transaction rolls
back. You can use your exception handling blocks to write
information out to an external table and thus keep logs that will
survive even if a rollback does occur...

./heLen