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.
