Re: SQL BEFORE puzzle

On Aug 1, 7:44 am, Roy Hann <specia..._at_processed.almost.meat> wrote:
> 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?
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
Received on Fri Aug 01 2008 - 10:57:09 CEST