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]