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
;
