G'day Rajesh,
Thankyou so much for your help, it worked correctly the very first time.
Note that I used something almost identical:
-----------------------------------------------------------------------------------------------------------
CREATE TRIGGER UPDATEFOOD
AFTER UPDATE OF FOOD ON FOODNAMETABLE
REFERENCING OLD AS PREVIOUSROW REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
UPDATE FREQUENCYTABLE SET FOOD = NEWROW.FOOD
WHERE FREQUENCYTABLE.FOOD = PREVIOUSROW.FOOD;
----------------------------------------------------------------------------------------------------------
This version threw an SQL syntax error which I incorrectly took to mean that
it wasn't possible to have more than one REFERENCING statement per
AFTER trigger.
Anyway, again:
Thanks matey (from the land down under).
From: Rajesh Kartha <[EMAIL PROTECTED]>
Reply-To: "Derby Discussion" <[email protected]>
To: Derby Discussion <[email protected]>
Subject: Re: NEWBIES TRIGGER CONFUSION PROBLEM
Date: Tue, 24 Jan 2006 17:55:10 -0800
How about change the trigger from
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.
>
>
>
