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
beforeTriggerOldNewValues.java
Description: Binary data
