Re: SQL BEFORE puzzle

"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. Received on Fri Aug 01 2008 - 13:47:56 CEST