Re: SQL BEFORE puzzle

From: Roy Hann <specially_at_processed.almost.meat>
Date: Mon, 04 Aug 2008 03:33:13 -0500
Message-ID: <>

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)?
>> --
>> 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...

Received on Mon Aug 04 2008 - 10:33:13 CEST

Original text of this message