Re: SQL BEFORE puzzle
Date: Mon, 04 Aug 2008 03:33:13 -0500
Walter Mitty wrote:
> "Roy Hann" <specially_at_processed.almost.meat> wrote in message
>> 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)?
> 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
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...
-- RoyReceived on Mon Aug 04 2008 - 10:33:13 CEST