Hi Mamta,
I agree with Knut's response: OLD refers to the contents of the table
before the update. NEW refers to the contents of the table after the
update. This is discussed in part 2 of the SQL Standard, section 4.38
(Triggers). Here are the gory details:
"A collection of rows being deleted, inserted or replaced is known as a
transition table. For a delete trigger there
is just one transition table, known as an old transition table. For an
insert trigger there is just one transition
table, known as a new transition table. For an update trigger there is
both an old transition table (the rows being
replaced) and a new transition table (the replacement rows), these two
tables having the same cardinality.
...
"Special variables make the data in the transition table(s) available to
the triggered action. For a statement-level
trigger the variable is one whose value is a transition table. For a
row-level trigger, the variable is a range
variable, known as a transition variable. A transition variable ranges
over the rows of a transition table, each
row giving rise to exactly one execution of the triggered action, with
the row in question assigned to the transition
variable. A transition variable is either an old transition variable or
a new transition variable, depending on
the transition table over whose rows it ranges.
"When there are two transition tables, old and new, each row in the new
transition table is one that is derived
by an update operation applied to exactly one row in the old transition
table. Thus there is a 1:1 correspondence
between the rows of the two tables. However, this correspondence is
visible only to a row-level trigger, each
invocation of which is able to access both the old and new transition
variables, the new transition variable
representing the result of applying the update operation in question to
the row in the old transition variable."
That is, OLD refers to the "old transition table", which is the state of
the table before the updates are applied. NEW refers to the "new
transition table", which is the state of the table after the updates are
applied.
Probably the wording of the CREATE TRIGGER section could be improved.
Hope this helps,
-Rick
Mamta Satoor wrote:
Hello,
I have a question on "BEFORE UPDATE trigger and REFERENCING old and
new values".
The Derby Reference Manual under CREATE TRIGGER topics states "Before
triggers fire before the statement's changes are applied and before
any constraints have been applied. Before triggers can be either row
or statement triggers (see Statement versus row triggers).".
Based on the Reference manual blurb above, I thought that if a user
had a BEFORE UPDATE trigger defined on a (say char(1)) column then,
old and new values for that column inside the BEFORE trigger will be
both old value. Not sure how handy that behavior is ie to have both
old and new value point to the old value, but that is what Derby
reference manual seems to imply.
I wrote a test program to check the actual Derby behavior. The program
is attached to this mail. I have the BEFORE UPDATE trigger which calls
a stored procedure in it's trigger action. The stored procedure gets
the old and new value of column being updated passed to it and the
procedure just prints those values on the console. The execution of
the program shows the old and new values to be NOT same, instead they
reflect the values before the update happened and after the update
happened. Is the manual stating the behavior incorrectly or am I not
understanding it correctly? Appreciate any thoughts. The program is
attached to this mail.
thanks,
Mamta
