db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: BEFORE UPDATE trigger and REFERENCING old and new values
Date Tue, 02 Feb 2010 13:50:57 GMT
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
>   


Mime
View raw message