Re: SQL BEFORE puzzle
Date: Fri, 1 Aug 2008 06:02:03 -0400
Message-ID: <vaBkk.17847$89.1733_at_nlpi069.nbdc.sbc.com>
"Roy Hann" <specially_at_processed.almost.meat> wrote in message
news:_f-dnW4TR-JWeAzVnZ2dneKdnZydnZ2d_at_pipex.net...
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 would think that the restriction is supposed to prevent something like:
update T ....
where the pending updates would pile up. Note that:
update T ....
> 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
beforeT: update T ....
beforeT: update T ....
beforeT: update T ....
...and so on....
...and so on....
...and so on....
afterT: update T ....
afterT: update T ....
afterT: update T ....
...and so on....
...and so on....
...and so on....
wouldn't cause the updates to pile up (even though the transaction controlling them would remain outstanding) because instead of being held waiting to be applied to the table as in the case of before triggers, they would have already been applied before each subsequent afterT is executed, making the recursive afterT more like just an iteration of updates within a transaction. Received on Fri Aug 01 2008 - 12:02:03 CEST