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 15:36:20 GMT
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