db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From IkeAbalogu <ikeabal...@hotmail.com>
Subject Re: Error code 42X05. Table doesn't exist.Pls help with code.
Date Mon, 27 Jun 2011 15:16:25 GMT

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.


Mime
View raw message