db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: BEFORE UPDATE trigger and REFERENCING old and new values
Date Thu, 04 Feb 2010 03:34:19 GMT
Thanks so much, Knut, Rick and Dag for taking the time to respond.
This information will be useful for any work on DERBY-1482.

Mamta

On Tue, Feb 2, 2010 at 6:23 AM, Dag H. Wanvik <Dag.Wanvik@sun.com> wrote:
>
> 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