Thanks.I see the error.I am updating newrow because it is the newly inserted row that the trigger will update its previousbalance column.I will try to come with a where statement to get to the newly inserted row.Your help will still be appreciated. Thanks again.
Rick Hillegas-3 wrote: > > After running the database creation script, I see the reported 42X05 > error when creating the problem trigger: > > CREATE TRIGGER NEWBALANCE > AFTER INSERT ON APP.PRODUCTQUANTMONITOR > REFERENCING NEW AS NEWROW > FOR EACH ROW > UPDATE NEWROW SET PREVIOUS_BALANCE = > CASE > WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 > THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = > NEWROW.FK1_SUPPLYID) > ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE > FK1_SUPPLYID = NEWROW.FK1_SUPPLYID > AND TANKNUMBER = NEWROW.TANKNUMBER) > END > ; > ERROR 42X05: Table/View 'NEWROW' does not exist. > > The error occurs because the triggered statement is trying to update the > transient transition variable NEWROW rather than an actual table. The > following rewritten trigger definition does compile and run cleanly. I > don't know if this is what you are trying to achieve, though: > > CREATE TRIGGER NEWBALANCE > AFTER INSERT ON APP.PRODUCTQUANTMONITOR > REFERENCING NEW AS NEWROW > FOR EACH ROW > UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE = > CASE > WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1 > THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = > NEWROW.FK1_SUPPLYID) > ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE > FK1_SUPPLYID = NEWROW.FK1_SUPPLYID > AND TANKNUMBER = NEWROW.TANKNUMBER) > END > ; > > Hope this helps, > -Rick > > On 6/27/11 6:55 AM, IkeAbalogu wrote: >> Thanks.For your interest and help.Below is the entire schema >> >> >> -------------------------------------------------------------- >> -- Database creation Script >> -------------------------------------------------------------- >> -- Create a Database table to represent the "product" entity. >> CREATE TABLE product( >> name VARCHAR(30) NOT NULL, >> description LONG VARCHAR NOT NULL, >> source LONG VARCHAR NOT NULL, >> -- Specify the PRIMARY KEY constraint for table "product". >> -- This indicates which attribute(s) uniquely identify each row of data. >> CONSTRAINT pk_product PRIMARY KEY (name) >> ); >> >> -- Create a Database table to represent the "productsupply" entity. >> CREATE TABLE productsupply( >> supplyid INTEGER NOT NULL, >> supply_date DATE NOT NULL, >> isfinished SMALLINT NOT NULL, >> finish_date DATE NOT NULL, >> quantity BIGINT NOT NULL, >> costprice DOUBLE PRECISION NOT NULL, >> saleprice DOUBLE PRECISION NOT NULL, >> fk1_name VARCHAR(30) NOT NULL, >> -- Specify the PRIMARY KEY constraint for table "productsupply". >> -- This indicates which attribute(s) uniquely identify each row of data. >> CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) >> ); >> >> -- Create a Database table to represent the "productquantmonitor" entity. >> CREATE TABLE productquantmonitor( >> checkdate DATE NOT NULL, >> tanknumber INTEGER NOT NULL, >> balance BIGINT NOT NULL, >> previous_balance BIGINT NOT NULL, >> quantitysold BIGINT NOT NULL, >> fk1_supplyid INTEGER NOT NULL >> ); >> >> -- Create a Database table to represent the "producttank" entity. >> CREATE TABLE producttank( >> tankid INTEGER NOT NULL, >> tankcapacity BIGINT NOT NULL, >> fk1_name VARCHAR(30) NOT NULL, >> -- Specify the PRIMARY KEY constraint for table "producttank". >> -- This indicates which attribute(s) uniquely identify each row of data. >> CONSTRAINT pk_producttank PRIMARY KEY (tankid) >> ); >> >> >> -------------------------------------------------------------- >> -- Alter Tables to add fk constraints -- >> >> -- Now all the tables have been created the ALTER TABLE command is used >> to >> define some additional >> -- constraints. These typically constrain values of foreign keys to be >> associated in some way >> -- with the primary keys of related tables. Foreign key constraints can >> actually be specified >> -- when each table is created, but doing so can lead to dependency >> problems >> within the script >> -- i.e. tables may be referenced before they have been created. This >> method >> is therefore safer. >> >> -- Alter table to add new constraints required to implement the >> "producttank_product" relationship >> >> -- This constraint ensures that the foreign key of table "producttank" >> -- correctly references the primary key of table "product" >> >> ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_to_product FOREIGN >> KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE >> RESTRICT; >> >> -- Alter table to add new constraints required to implement the >> "productsupply_product" relationship >> >> -- This constraint ensures that the foreign key of table "productsupply" >> -- correctly references the primary key of table "product" >> >> ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product >> FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON >> UPDATE >> RESTRICT; >> >> -- Alter table to add new constraints required to implement the >> "productquantmonitor_productsupply" relationship >> >> -- This constraint ensures that the foreign key of table >> "productquantmonitor" >> -- correctly references the primary key of table "productsupply" >> >> ALTER TABLE productquantmonitor ADD CONSTRAINT >> fk1_productquantmonitor_to_productsupply FOREIGN KEY(fk1_supplyid) >> REFERENCES productsupply(supplyid) ON DELETE RESTRICT ON UPDATE RESTRICT; >> -------------------------------------------- >> >> Thanks. >> >> >> Rick Hillegas-3 wrote: >>> When I run these commands, I get an error on this one: >>> >>> ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product >>> FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON >>> UPDATE >>> RESTRICT; >>> ERROR X0Y46: Constraint 'FK1_PRODUCTSUPPLY_TO_PRODUCT' is invalid: >>> referenced table PRODUCT does not exist. >>> >>> That seems reasonable because I don't see a table called PRODUCT in this >>> schema. Can you share the rest of your schema with us? >>> >>> Thanks, >>> -Rick >>> >>> On 6/27/11 6:22 AM, IkeAbalogu wrote: >>>> Thanks for your response.I get Error code 42X05. Table/view doesn't >>>> exist >>>> -- Create a Database table to represent the "productsupply" entity. >>>> CREATE TABLE productsupply( >>>> supplyid INTEGER NOT NULL, >>>> supply_date DATE NOT NULL, >>>> isfinished SMALLINT NOT NULL, >>>> finish_date DATE NOT NULL, >>>> quantity BIGINT NOT NULL, >>>> costprice DOUBLE PRECISION NOT NULL, >>>> saleprice DOUBLE PRECISION NOT NULL, >>>> fk1_name VARCHAR(30) NOT NULL, >>>> -- Specify the PRIMARY KEY constraint for table "productsupply". >>>> -- This indicates which attribute(s) uniquely identify each row of >>>> data. >>>> CONSTRAINT pk_productsupply PRIMARY KEY (supplyid) >>>> ); >>>> >>>> -- Create a Database table to represent the "productquantmonitor" >>>> entity. >>>> CREATE TABLE productquantmonitor( >>>> checkdate DATE NOT NULL, >>>> tanknumber INTEGER NOT NULL, >>>> balance BIGINT NOT NULL, >>>> previous_balance BIGINT NOT NULL, >>>> quantitysold BIGINT NOT NULL, >>>> fk1_supplyid INTEGER NOT NULL >>>> ); >>>> >>>> -- Create a Database table to represent the "producttank" entity. >>>> CREATE TABLE producttank( >>>> tankid INTEGER NOT NULL, >>>> tankcapacity BIGINT NOT NULL, >>>> fk1_name VARCHAR(30) NOT NULL, >>>> -- Specify the PRIMARY KEY constraint for table "producttank". >>>> -- This indicates which attribute(s) uniquely identify each row of >>>> data. >>>> CONSTRAINT pk_producttank PRIMARY KEY (tankid) >>>> ); >>>> -- This constraint ensures that the foreign key of table >>>> "productsupply" >>>> -- correctly references the primary key of table "product" >>>> >>>> ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product >>>> FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON >>>> UPDATE >>>> RESTRICT; >>>> >>>> -- Alter table to add new constraints required to implement the >>>> "productquantmonitor_productsupply" relationship >>>> >>>> -- This constraint ensures that the foreign key of table >>>> "productquantmonitor" >>>> -- correctly references the primary key of table "productsupply" >>>> >>>> ALTER TABLE productquantmonitor ADD CONSTRAINT >>>> fk1_productquantmonitor_to_productsupply FOREIGN KEY(fk1_supplyid) >>>> REFERENCES productsupply(supplyid) ON DELETE RESTRICT ON UPDATE >>>> RESTRICT; >>>> >>>> >>>> Rick Hillegas-3 wrote: >>>>> Can you share your schema and the error message you are seeing? That >>>>> will help people advise you. >>>>> >>>>> Thanks, >>>>> -Rick >>>>> >>>>> On 6/27/11 5:20 AM, IkeAbalogu wrote: >>>>>> CREATE TRIGGER NEWBALANCE >>>>>> AFTER INSERT ON APP.PRODUCTQUANTMONITOR >>>>>> REFERENCING NEW AS NEWROW >>>>>> FOR EACH ROW >>>>>> UPDATE NEWROW SET PREVIOUS_BALANCE = >>>>>> CASE >>>>>> WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM >>>>>> APP.PRODUCTQUANTMONITOR) >>>>>> = >>>>>> 1 >>>>>> THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID = >>>>>> NEWROW.FK1_SUPPLYID) >>>>>> ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE >>>>>> FK1_SUPPLYID = NEWROW.FK1_SUPPLYID >>>>>> AND TANKNUMBER = NEWROW.TANKNUMBER) >>>>>> END >>>>>> ; >>>>> >>> >>> > > > -- View this message in context: http://old.nabble.com/Error-code-42X05.-Table-doesn%27t-exist.Pls-help-with-code.-tp31936869p31938244.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
