Re: SQL BEFORE puzzle

"Roy Hann" <specially_at_processed.almost.meat> wrote in message news:PKGdnaGAlcUKIAvVnZ2dnUVZ8tbinZ2d_at_pipex.net...
> 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?
>

Expecting a committee to come up with something sensible correlates closely with one defnition of insanity: doing the same thing over and over and expecting different results.

> 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
Received on Mon Aug 04 2008 - 14:19:54 CEST