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 <[email protected]> wrote: > > Knut Anders Hatlen <[email protected]> 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 >
