Re: SQL BEFORE puzzle

On Aug 1, 7:44 am, Roy Hann <specia...@xxxxxxxxxxxxxxxxxxxxx> wrote:
Cimode wrote:
On 31 juil, 17:18, Roy Hann <specia...@xxxxxxxxxxxxxxxxxxxxx> 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

.

Relevant Pages

  • Re: SQL 7 vs. 2000 issue -trigger and nulls
    ... >We're having trouble with a trigger updating some tables. ... course be locked by the current transaction, ... locking data and updating rows when the COMMIT inside the trigger is ... I seriously hope that SQL Server 7.0 simply disregarded these two ...
    (microsoft.public.sqlserver.mseq)
  • Re: Locking and Delay in a Bottleneck
    ... that should avoid the gap issue on rollback, ... SQL Server MVP ... the saving transaction, this journal voucher is having a header table ... number in a table whereupon you commit. ...
    (microsoft.public.sqlserver.programming)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... SQL> drop trigger emp_upd_trig ... SQL> create table emp ( ... Commit complete. ...
    (comp.databases.oracle.server)
  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server Analysis Services Query in a SQL Server job step
    ... My guess is that each request (begin transaction, execute processing, ... Try out the MSDN Forums for Analysis Services at: ... I have a step (SQL Server Analysis Services Query) inside of a 2005 SQL ... begin, commit, rollback transactions all within the code. ...
    (microsoft.public.sqlserver.olap)