CREATE TRIGGER UPDATEFOOD
AFTER UPDATE OF FOOD ON FOODNAMETABLE
REFERENCING OLD AS PREVIOUSROW
FOR EACH ROW MODE DB2SQL
UPDATE FREQUENCYTABLE SET FOOD = FOODNAMETABLE.FOOD
WHERE FREQUENCYTABLE.FOOD = PREVIOUSROW.FOOD;
to be:
CREATE TRIGGER UPDATEFOOD AFTER UPDATE OF FOOD ON FOODNAMETABLE REFERENCING
OLD AS PREVIOUSROW new as newrow FOR EACH ROW MODE DB2SQL UPDATE FREQUENCYTABLE
SET FOOD = newrow.FOOD WHERE FREQUENCYTABLE.FOOD = PREVIOUSROW.FOOD;
In your original create trigger statement, I think, Derby should throw a SQL error when the actual table
name ('FOODNAMETABLE') is used in the 'Triggered-SQL-statement' instead of using the referenced name ('newrow').
Regards,
Rajesh
On 1/24/06, Peter Bowman <[EMAIL PROTECTED]
> wrote:
Hi All,
I have created the following tables in a Derby database:
-------------------------------------------------------
DROP TABLE FREQUENCYTABLE;
DROP TABLE FOODNAMETABLE;
CREATE TABLE FOODNAMETABLE (ID SMALLINT GENERATED ALWAYS AS IDENTITY,
FOOD VARCHAR(40) NOT NULL PRIMARY KEY,
FOLDER VARCHAR(40) NOT NULL);
CREATE TABLE FREQUENCYTABLE (FREQUENCY VARCHAR(40) NOT NULL,
FOOD VARCHAR(40) NOT NULL,
AMOUNT SMALLINT NOT NULL DEFAULT 0,
INCLUDE SMALLINT NOT NULL DEFAULT 0 CHECK
(INCLUDE BETWEEN 0 AND 1));
CREATE TRIGGER UPDATEFOOD
AFTER UPDATE OF FOOD ON FOODNAMETABLE
REFERENCING OLD AS PREVIOUSROW
FOR EACH ROW MODE DB2SQL
UPDATE FREQUENCYTABLE SET FOOD = FOODNAMETABLE.FOOD
WHERE FREQUENCYTABLE.FOOD = PREVIOUSROW.FOOD;
-------------------------------------------------------
The above trigger runs automatically whenever my application updates the
FOOD column
in FOODNAMETABLE, for example when the value in FOOD is changed from
*Oranges*
to *Lemons*. When this happens the trigger is supposed to change any row in
FREQUENCYTABLE
that also has *Oranges* in its FOOD column to *Lemons*, but this doesn't
happen.
By testing I have proved that the trigger runs and that it makes a change
but my
problem is it changes it to the wrong value. It seems that whereas
PREVIOUSROW.FOOD
equals *Oranges*, as expected ( i.e. because that was its value before the
UPDATE on that
table), FOODNAMETABLE.FOOD doesn't equal *Lemons* as I, seemingly
incorrectly, expect.
What's wrong with the trigger?
Thanks,
Peter.
PS. I'm a newbie to databases, just two weeks.
