Ok I got a couple of practice exercises from class and I found one that I couldn't figure out, even though it sounds pretty simple. I have this tables. I want to make a trigger that after every time I update or insert a value into the product table, it updates the class table with the count of how many products belong to that class, and the sum of the prices.
At first I tried using FOR EACH ROW, but I couldn't figure out how to make it so that it reads the product table new product values for the class of the new entry without 'mutating', as I was trying to read the product table that was being updates/inserted into. Couldn't figure out even if there was a right way to set up :new. and :old. for this purpose.
Tried cursors that checked the product table and selects into variables for count and sum, but it was the same problem, the whole 'mutating' problem.
Now I got it without the FOR EACH ROW but it Counts and Sums all the products and prices for all classes. I can't figure out how to make it work for each class individually. I though maybe a loop and a counter that increased based on the max class, but that seems over-complicated. Any help would be appreciated.
drop table class cascade constraints;
drop table provider cascade constraints;
drop table product cascade constraints;
CREATE TABLE class(
class number(5) constraint pk_class primary key,
description varchar2(20) constraint nn1_class CHECK(description = INITCAP(description) AND description IS NOT NULL),
tot_product number(5) constraint nn2_class CHECK (tot_product >=0 AND tot_product IS NOT NULL),
tot_price number(12,2) constraint nn3_class CHECK (tot_price >=0 AND tot_price IS NOT NULL),
constraint pk1_class CHECK (class >=0)
);
INSERT INTO class VALUES(1,'Description 1', 0, 0);
INSERT INTO class VALUES(2,'Description 2', 0, 0);
INSERT INTO class VALUES(3,'Description 3', 0, 0);
INSERT INTO class VALUES(4,'Description 4', 0, 0);
INSERT INTO class VALUES(5,'Description 5', 0, 0);
CREATE TABLE provider(
provider number(5) constraint pk_provider primary key,
description varchar2(20) constraint nn1_provider CHECK(description = INITCAP(description) AND description IS NOT NULL),
constraint pk1_provider CHECK (provider >=0)
);
INSERT INTO provider VALUES(1,'Description 1');
INSERT INTO provider VALUES(2,'Description 2');
INSERT INTO provider VALUES(3,'Description 3');
INSERT INTO provider VALUES(4,'Description 4');
INSERT INTO provider VALUES(5,'Description 5');
CREATE TABLE product(
product number(5) constraint pk_product primary key,
description varchar2(20) constraint nn1_product CHECK (description = INITCAP(description) AND description IS NOT NULL),
price number(12,2) constraint nn2_product CHECK (price >=0 AND price IS NOT NULL),
available number(5) constraint nn3_product CHECK (available >=0 AND available IS NOT NULL),
class number(5) constraint fk1_product references class NOT NULL,
provider number(5) constraint fk2_product references provider NOT NULL,
constraint pk1_product CHECK (product >=0)
);
CREATE OR REPLACE TRIGGER tot_class
AFTER INSERT OR UPDATE ON product
DECLARE
e_tot_product number(5);
e_tot_price number(12,2);
BEGIN
SELECT COUNT(product) INTO e_tot_product
FROM product
WHERE class = class;
SELECT SUM(price) INTO e_tot_price
FROM product
WHERE class = class;
UPDATE class SET tot_product = e_tot_product, tot_price= e_tot_price WHERE class = class;
END;
/
INSERT INTO product VALUES(1,'Description 1', 100, 10, 1, 1);
INSERT INTO product VALUES(2,'Description 2', 100, 10, 1, 2);
INSERT INTO product VALUES(3,'Description 3', 100, 10, 2, 1);
INSERT INTO product VALUES(4,'Description 4', 100, 10, 4, 5);
INSERT INTO product VALUES(5,'Description 5', 100, 10, 2, 3);
I recommend that to use the MERGE statement in the trigger like below
CREATE OR REPLACE TRIGGER TOT_CLASS
AFTER INSERT OR UPDATE
ON PRODUCT
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
MERGE INTO class oldclass
using
(
SELECT a.class,COUNT(b.product) tot_poduct,sum(b.price) tot_price
FROM product b
join
class a
on a.class = b.class
group by a.class
) newclass
on (oldclass.class = newclass.class)
when matched then
update set oldclass.tot_product = newclass.tot_poduct,
oldclass.tot_price = newclass.tot_price;
END;
/
here if the product class is updated then, we have to recheck the old product count and total price also. hence the above statement will take such problems. Still i am not sure about the performance while on large data set. I would suggest timely call of merge statement reside inside the trigger using DBMS_JOBS or you can use tuning methods on the same.