Re: SQL BEFORE puzzle

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... Received on Fri Aug 01 2008 - 01:28:45 CEST