Scribd
Upload
34 views1 page

SQL Trigger for Sales Summary Update

This document defines a SQL trigger named 'bb_salesum_trg' that activates after an update to the 'orderplaced' field in the 'bb_basket' table. The trigger calculates total sales and quantities for products in the basket, adjusting for any options specified, and updates the 'bb_sales_sum' table accordingly. It uses a cursor to iterate through items in the basket and performs the necessary calculations and updates for each product.

Uploaded by

Lemuel Taku
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
34 views1 page

SQL Trigger for Sales Summary Update

This document defines a SQL trigger named 'bb_salesum_trg' that activates after an update to the 'orderplaced' field in the 'bb_basket' table. The trigger calculates total sales and quantities for products in the basket, adjusting for any options specified, and updates the 'bb_sales_sum' table accordingly. It uses a cursor to iterate through items in the basket and performs the necessary calculations and updates for each product.

Uploaded by

Lemuel Taku
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

CREATE OR REPLACE TRIGGER bb_salesum_trg

AFTER UPDATE OF orderplaced ON bb_basket


FOR EACH ROW
WHEN ([Link] = 1)
DECLARE
CURSOR basketitem_cur IS
SELECT idproduct, quantity, price, option1
FROM bb_basketitem
WHERE idbasket = :[Link];
lv_sale_num NUMBER(5,2) := 0;
lv_qty_num NUMBER(3,1) := 0;
BEGIN
FOR basketitem_rec IN basketitem_cur LOOP
lv_sale_num := basketitem_rec.price*basketitem_rec.quantity;
IF basketitem_rec.option1 = 1 THEN
lv_qty_num := (.5 * basketitem_rec.quantity);
ELSE
lv_qty_num := basketitem_rec.quantity;
END IF;
UPDATE bb_sales_sum
SET tot_sales = nvl(tot_sales,0) + lv_sale_num,
tot_qty = nvl(tot_qty,0) + lv_qty_num
WHERE idproduct = basketitem_rec.idproduct;
END LOOP;
END;

You might also like