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 Tue, 28 Jun 2011 11:41:38 GMT

Since you mentioned stored procedure as an alternative i have been trying to
find a tutorial that will guide me to writing a stored procedure but have
not found any.I find the reference manual article on it confusing. pls help
if you have any websites i can look at.
Thanks again.

Rick Hillegas-3 wrote:
> 
> Instead of declaring a trigger which fires an update statement, you 
> might want to consider declaring a trigger which fires a database 
> procedure--the procedure would then update the row which you just 
> inserted. The resulting code might be easier to reason about.
> 
> Hope this helps,
> -Rick
> 
> On 6/27/11 8:16 AM, IkeAbalogu wrote:
>> 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.-tp31936869p31945490.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message