Re: SQL BEFORE puzzle

Cimode wrote:

> On 31 juil, 17:18, Roy Hann <specia..._at_processed.almost.meat> 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