Re: SQL BEFORE puzzle
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Mon, 04 Aug 2008 17:32:23 -0300
Message-ID: <4897675a$0$4043$9a566e8b_at_news.aliant.net>
>
>
> 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?
Date: Mon, 04 Aug 2008 17:32:23 -0300
Message-ID: <4897675a$0$4043$9a566e8b_at_news.aliant.net>
Walter Mitty wrote:
> "kschendel" <schendel_at_kbcomputer.com> wrote in message
> news:7c285b9d-1781-4f33-8235-2f4781ccc99e_at_w7g2000hsa.googlegroups.com...
> On Aug 1, 7:47 am, "Walter Mitty" <wami..._at_verizon.net> 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?
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? Received on Mon Aug 04 2008 - 22:32:23 CEST