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 Tue, 28 Jun 2011 13:32:48 GMT
On 6/28/11 4:41 AM, IkeAbalogu wrote:
> 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.
I responded to the "stored procedure tutorial help" thread. Hope that 
helps...

-Rick
> 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
>>>>>>>>> ;
>>>>
>>
>>


Mime
View raw message