SQL BEFORE puzzle

Cimode wrote:

> On 31 juil, 17:18, Roy Hann wrote:
>> 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:
> On a pure SQL perspective, I am going to play devil's advocate...
>
> The only justification I can see at midnight is that a rollback on
> transaction triggering the trigger would and should cause cascade
> rollbacks on subsequent tables to preserve integrity at (SQL) database
> level. Appart from the performance nightmare it would create on
> direct image systems, it would be necessary to *not* commit changes to
> subsequent tables before the main commit would occur. Somebody
> accessing the table modified may get wrong results if the main commit
> is rolled back or commited *after* the subsequent commits are
> committed. In any case, the safe way would force the subsequent
> commits to wait for the main transaction block to commit. On large
> transaction blocks that would be an obvious problem. Which is why in
> the first place an modification could be dangerous.
>
> Hope this helps...

I'm afraid it didn't.

Based on your response I wonder if I have a very fundamental
misunderstanding. I had assumed that the triggered updates would
execute in the context of the same transaction as whatever update caused
the trigger to execute. Is that (necessarily) wrong? Do the updates by
triggers occur in a seperate transaction?

--
Roy
 >> Stay informed about: SQL BEFORE puzzle 

On Aug 1, 7:44 am, Roy Hann wrote:
> Cimode wrote:
> > On 31 juil, 17:18, Roy Hann wrote:
> >> 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:
> > On a pure SQL perspective, I am going to play devil's advocate...
>
> > The only justification I can see at midnight is that a rollback on
> > transaction triggering the trigger would and should cause cascade
> > rollbacks on subsequent tables to preserve integrity at (SQL) database
> > level. Appart from the performance nightmare it would create on
> > direct image systems, it would be necessary to *not* commit changes to
> > subsequent tables before the main commit would occur. Somebody
> > accessing the table modified may get wrong results if the main commit
> > is rolled back or commited *after* the subsequent commits are
> > committed. In any case, the safe way would force the subsequent
> > commits to wait for the main transaction block to commit. On large
> > transaction blocks that would be an obvious problem. Which is why in
> > the first place an modification could be dangerous.
>
> > Hope this helps...
>
> I'm afraid it didn't.
>
> Based on your response I wonder if I have a very fundamental
> misunderstanding. I had assumed that the triggered updates would
> execute in the context of the same transaction as whatever update caused
> the trigger to execute. Is that (necessarily) wrong? Do the updates by
> triggers occur in a seperate transaction?
Some DBMS do open that possibility by creating separate transactional
context. A lot of DBMS's also make heavy use of dirty reads which
make the principle somehow moot.

My two cents...
> Roy
 >> Stay informed about: SQL BEFORE puzzle 

"Roy Hann" 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

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 ....
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. Note that:

update T ....
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.
 >> Stay informed about: SQL BEFORE puzzle 

"Roy Hann" 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.
 >> Stay informed about: SQL BEFORE puzzle 

On Jul 31, 12:18 pm, Roy Hann wrote:
> 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

Roy,

I won't claim authority but I can't find the sentence you refer to in
SQL:2003 nor the latest draft of SQL:2006+ that I have (which is
probably not the latest). I haven't looked in the original SQL:1999
standard but I did look up in SQL-99 Complete from Gulutzan & Pelzer
and didn't see it either. What are you working from?

Eric
 >> Stay informed about: SQL BEFORE puzzle 

Brian Selzer wrote:

>
> "Roy Hann" 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
>
> 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?

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
 >> Stay informed about: SQL BEFORE puzzle 

Walter Mitty wrote:

>
> "Roy Hann" 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...

--
Roy
 >> Stay informed about: SQL BEFORE puzzle 

"Roy Hann" wrote in message

> Brian Selzer wrote:
>
>>
>> "Roy Hann" 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
>>
>> 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
 >> Stay informed about: SQL BEFORE puzzle 

On Aug 1, 7:47 am, "Walter Mitty" wrote:
>
> 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.

But you have the same issue with AFTER triggers. If an update fires
multiple
AFTER triggers, and some alter (other table) data and others read the
same,
the result is indeterminate if there's no way to somehow order the
trigger
firing. The answer with after triggers is to simply state that such
things
produce indeterminate results. I haven't been able to come up with an
equivalent scenario for BEFORE triggers that can't be dealt with
in the same way.
 >> Stay informed about: SQL BEFORE puzzle 

"kschendel" wrote in message

On Aug 1, 7:47 am, "Walter Mitty" wrote:

quote:
>
> 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.

But you have the same issue with AFTER triggers. If an update fires
multiple
AFTER triggers, and some alter (other table) data and others read the
same,
the result is indeterminate if there's no way to somehow order the
trigger
firing. The answer with after triggers is to simply state that such
things
produce indeterminate results. I haven't been able to come up with an
equivalent scenario for BEFORE triggers that can't be dealt with
in the same way.

end quote.

I contend that the issue isn't the same with AFTER triggers, although I see
the problem with indeterminate serialization that you raise.

What led me to question allowing some updates before a BEFORE trigger runs
was not indeterminancy and serialization, but the fact that a BEFORE
trigger can presumably rely on the data its looking at as being identical to
what the data looked like before the event that set off the trigger. The
second BEFORE trigger isn't seeing the BEFORE state anymore.

AFTER triggers expect to look at data that's been updated by the original
action, and therefore might not be disrupted by running after another AFTER
trigger.

But your comment makes me wonder.... Is there an issue in not letting an
AFTER trigger run last?
 >> Stay informed about: SQL BEFORE puzzle 

Walter Mitty wrote:

> "kschendel" wrote in message
>
> On Aug 1, 7:47 am, "Walter Mitty" wrote:
>
> quote:
>
>>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.
>
>
> But you have the same issue with AFTER triggers. If an update fires
> multiple
> AFTER triggers, and some alter (other table) data and others read the
> same,
> the result is indeterminate if there's no way to somehow order the
> trigger
> firing. The answer with after triggers is to simply state that such
> things
> produce indeterminate results. I haven't been able to come up with an
> equivalent scenario for BEFORE triggers that can't be dealt with
> in the same way.
>
> end quote.
>
> I contend that the issue isn't the same with AFTER triggers, although I see
> the problem with indeterminate serialization that you raise.
>
> What led me to question allowing some updates before a BEFORE trigger runs
> was not indeterminancy and serialization, but the fact that a BEFORE
> trigger can presumably rely on the data its looking at as being identical to
> what the data looked like before the event that set off the trigger. The
> second BEFORE trigger isn't seeing the BEFORE state anymore.
>
> AFTER triggers expect to look at data that's been updated by the original
> action, and therefore might not be disrupted by running after another AFTER
> trigger.
>
> But your comment makes me wonder.... Is there an issue in not letting an
> AFTER trigger run last?

I would expect both the BEFORE and AFTER triggers to have a view of both
the before and after images of the data. I don't really see the need for
before and after triggers. Perhaps I am just missing it.

Was it perhaps a case where someone intended the BEFORE trigger to act
as a read-only constraint that could halt the update before it happened
and the AFTER trigger as a way to cascade changes?
 >> Stay informed about: SQL BEFORE puzzle 

On Aug 4, 4:32 pm, Bob Badour wrote:

> I would expect both the BEFORE and AFTER triggers to have a view of both
> the before and after images of the data. I don't really see the need for
> before and after triggers. Perhaps I am just missing it.

I think it's largely for practical reasons.
An AFTER trigger operates on a row that has already been
physically placed somewhere in the table/index. If the trigger
wants to alter a key value, the row probably has to move,
and you end up with physical storage issues. BEFORE
triggers avoid this, so they are preferred if part of the
action is to calculate or update primary or secondary
key columns.

Of course this is all implementation dependent, but
I suspect most implementations would work that way.
 >> Stay informed about: SQL BEFORE puzzle 

kschendel wrote:

> On Aug 4, 4:32 pm, Bob Badour wrote:
>
>>I would expect both the BEFORE and AFTER triggers to have a view of both
>>the before and after images of the data. I don't really see the need for
>>before and after triggers. Perhaps I am just missing it.
>
> I think it's largely for practical reasons.
> An AFTER trigger operates on a row that has already been
> physically placed somewhere in the table/index. If the trigger
> wants to alter a key value, the row probably has to move,
> and you end up with physical storage issues. BEFORE
> triggers avoid this, so they are preferred if part of the
> action is to calculate or update primary or secondary
> key columns.
>
> Of course this is all implementation dependent, but
> I suspect most implementations would work that way.

Given that a rollback could undo the work, I would expect only the log
to reflect updates in any case so I am not sure what the practical
benefit would be even with the above considerations.
 >> Stay informed about: SQL BEFORE puzzle