BEFORE UPDATE trigger and REFERENCING old and new values

[Mamta Satoor <ms...@gmail.com>]

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

Re: BEFORE UPDATE trigger and REFERENCING old and new values

[Rick Hillegas <Ri...@Sun.COM>]

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
>   

Re: BEFORE UPDATE trigger and REFERENCING old and new values

[Mamta Satoor <ms...@gmail.com>]

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 <Da...@sun.com> wrote:
>
> Knut Anders Hatlen <Kn...@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
>

Re: BEFORE UPDATE trigger and REFERENCING old and new values

["Dag H. Wanvik" <Da...@Sun.COM>]

Knut Anders Hatlen <Kn...@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

Re: BEFORE UPDATE trigger and REFERENCING old and new values

[Knut Anders Hatlen <Kn...@Sun.COM>]

Mamta Satoor <ms...@gmail.com> writes:

> 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.

Hi Mamta,

The paragraph you quoted does not mention the transition variables OLD
and NEW, so I'm not sure how you came to the conclusion that OLD and NEW
should have the same value in before triggers.

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.

-- 
Knut Anders