Re: SQL BEFORE puzzle

"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? Received on Mon Aug 04 2008 - 22:14:42 CEST