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
