db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: BEFORE UPDATE trigger and REFERENCING old and new values
Date Tue, 02 Feb 2010 14:23:30 GMT

Knut Anders Hatlen <Knut.Hatlen@Sun.COM> writes:

> My impression after reading the section about CREATE TRIGGER in the
> reference manual, is that OLD and NEW should provide the original values
> and post-change values, respectively, regardless of whether or not the
> change has actually been made yet. But I didn't find it spelled out
> explicitly, so there may be room for improvement in the manual.

I think this is correct and in line with the SQL standard. Quoting
from Melton's book on SQL 1999:

"Perhaps more interesting is the fact that the trigger can have access
to the values of the row or rows being inserted, updated or deleted;
and, for rows being update, the values before the update takes place
and the value after the update can both be made available.
:
:
The tables that are references by those correlation names are called
transition tables. Of course they are not persistent in the data base,
but they are created and destroyed dynamically, as they are needed in
the trigger execution context."

The assymmetry I find for UPDATE is this (INSERT and DELETE has other
obvious assymmetries: no OLD or NEW row respectively): 

"If you define a BEFORE trigger, you are not allowed to specify either
OLD TABLE or NEW TABLE, nor may the trigger's triggered SQL statement
make any changes to the database. The reason for this is a bit
subtle. The transition tables implied by OLD TABLE and NEW TABLE are
too likely to be affected by referential constraints and referential
actions that are activated by the changes caused byt the triggered SQL
statement, therefore, the values of the rows in that table are not
stable or adequately predictable until after the triggering SQL
statement has been executed."

Dag

Mime
View raw message