Re: SQL BEFORE puzzle

Brian Selzer wrote:

>
> "Roy Hann" <specially_at_processed.almost.meat> wrote in message
> news:_f-dnW4TR-JWeAzVnZ2dneKdnZydnZ2d_at_pipex.net...
>> This is not a theory question by any means, but perhaps there is someone
>> hanging out here who is able to speak with authority on SQL. We are
>> trying to figure out the justification for why a BEFORE trigger should
>> be prevented from doing any updates. The wording we're looking at from
>> the standard is:
>>
>> "If the trigger is a BEFORE trigger and if, before the completion of the
>> execution of any <SQL procedure statement> simply contained in triggered
>> SQL statements, an attempt is made to execute an SQL-data change
>> statement or an SQL-invoked routine that possibly modifies SQL-data,
>> then an exception condition is raised: prohibited statement encountered
>> during trigger execution."
>>
>> Obviously one shouldn't expect to be able to update a row that doesn't
>> exist yet, but why prevent all updates (to other rows, other tables)?
>>
>> --
>> Roy
>
> Not all implementations adhere to this restriction. For example, Oracle
> 10g's before-row and before statement triggers permit DML when they
> shouldn't while their after-row and after-statement don't when they should.

I am not overly interested in specific implementations at the moment, although I will probably come back to that topic later. I am more interested in why the standard would disallow update DML when a BEFORE trigger is fired. What is it about such updates that would result in inconsistent/undesirable behaviour versus the SQL ideal?

> I would think that the restriction is supposed to prevent something like:
>
> update T ....
> beforeT: update T ....
> beforeT: update T ....
> beforeT: update T ....
> ...and so on....
> ...and so on....
> ...and so on....
>
> where the pending updates would pile up.

Fair enough, but why would the SQL standard care about that? Why, in fact, would an implementation prefer to prevent all updates instead of merely imposing some reasonable/configurable limit on the number of "piled up" updates?

I am going to have to do more reading I can see. The most plausible conjecture I've come up with so far is that the standard either prescribes that any triggered BEFORE actions execute in a different transaction than the triggering update, or it is insufficiently precise about that, so the only safe option is to ban updates as a result of BEFORE triggers.

-- 
Roy