In the case of debugging, audit trails, and error logs you could use external tables as writes to them are not bound by transactions (the record will be written regardless of the issuance of a commit).

On Tue, 22 Jul 2003 07:34:30 +0200, Tim Ledgerwood wrote:
>At 06:26 PM 21/07/2003 +0200, you wrote:
>
>>Mahesh,
>>
>>MI>�&�can't to know that there can't be any DDL statements��and
>>transactional commands in stored procedures &�triggers. I
>>understand why
>>DDL statements are not allowed, but couldn''t get the reasons
>>MI>�for not allowing Begin Transaction, End transaction, commit,
>>etc.
>>MI>�Can anyone tell me why this is so..?
>>
>>I'd say that, given Firebird's ability to automatically roll back
>>the work done by a failed statement (including also stored
>>procedures),
>>transaction control in triggers and stored procedure is neither
>>useful
>>nor desirable. What do you need it for?
>>Ciao
>>--
>>Nando����������������������������mailto:nandod@...
>
>Its a very useful thing. In Sybase, you can have a stored procedure
>that
>calls other stored procedures. (As you can in many DBMSs). So what
>you can
>do is something like this :
>
>DECLARE PROCEDURE MYPROCEDURE
>(
>INPUT VARIABLES
>)
>AS
>BEGIN
>BEGIN TRANSACTION
>INSERT INTO THISTABLE
>EXECUTE THATPROCEDURE
>EXECUTE ANOTHERPROCEDURE
>DO SOMETHINGELSE
>COMMIT TRANSACTION
>
>EXCEPT
>BEGIN
>ROLLBACK
>INSERT INTO ERRORLOGTABLE
>(X, Y, Z)
>END
>END
>
>So it allows you (in the above example, anyway) to do an audit trail
>of
>what is happening. Error logging is the thing I have seen it used
>for most
>often, but I have also seen other examples. Basically, as soon as
>you do
>more than just one thing in a procedure (i.e., as you would in
>code),
>having transactions within stored procedures becomes useful.
>
>The one place I have seen it mis - used is where a developer will
>use it to
>do something that should have been done in client - side code - for
>example, where two things happen which should both be in the same
>transaction context. Because the developer has included the
>transaction
>context within two separate stored procedures, it means that they
>have
>separate transaction contexts, and so the use of transactions is
>meaningless.
>
>
>[Non-text portions of this message have been removed]
>
>
>
>Yahoo! Groups Sponsor
>
>ADVERTISEMENT
>
>
>To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
>
>
>
>Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
>