Bupp,

>> I actually think it would be a bad thing for SPs to support
>> such a feature. I mean imagine if you thought you could
>> rollback your transaction, but one of the SPs you relied on
>> had committed it.

> That's you just not undertsnding your database design, it's
> not a fault of the database.

Oh right. Just when I thought you had turned reasonable and
would try to be at least a little understanding - you turn
around and say something like that!

Perhaps, while we are at it, we should do away with type
checking in all our programming languages - after all it must
only be necessary if we fail to understand our own design,
none of us ever make mistakes... do we?

> SQL Server supports it, and after you do a commit or rollback,
> it automatically starts you with a new (implicit) transaction.

And what, just because MS does it that makes it correct without
question?


If I issue a statement like:

INSERT INTO ATABLE (AFIELD, BFIELD)
VALUES ('A', 'B');

And get an exception, what should be the state of the database?

If we follow your advice and allow stored procedures to issue
commits (or even commit retaining) then the state is
indeterminate - some things may or may not have changed as a
result of internal commits. Not exactly a recipe for data
integrity!

Had you forgotten that triggers can call stored procedures?
Can you imagine the mess a database could get into if commits
could occur right in the middle of other DML?

Perhaps the developers can build in some exception processing
when they detect procedures executing commit inside DML. I
dont know, I am not even sure that it matters given the
applications that already exist based on existing behaviour
(selectable stored procedures, procedures that perform DML
on behalf of applications etc etc).

I would suggest that a better solution would be something like
of a "stored script" - as Adam suggested. Like a stored
procedure but an object that manages its own transactions, and
because of this would not generally be useful to call from
triggers or stored procedures.

And indeed exactly this sort of thing is possible if you write
your own script processor and client process to execute it. It
would be neat if the FB server could support it directly.


--
Geoff Worboys
Telesis Computing