Re: SQL BEFORE puzzle

Walter Mitty 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
>
> I'm not speaking with authority here, but I'll offer a guess:
>
> What if a given event triggers more than one BEFORE trigger? Now, there's a
> problem if one of the triggers is allowed to do updates,
> and the other trigger is executed in the context of the same transaction,
> but later, the second trigger doesn't actually see the BEFORE state of the
> database.

That's a plausible-sounding guess. I'll have to think about it bit more before I say you must be right, but the undefined sequence of trigger executions would make doing updates risky...

-- 
Roy