db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Error code 42X05. Table doesn't exist.Pls help with code.
Date Mon, 27 Jun 2011 14:24:06 GMT
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
>>>>> ;
>>>>
>>
>>


Mime
View raw message