trigger.sql

DROP SEQUENCE STORE_SEQ;
DROP SEQUENCE COFFEE_SEQ;
DROP SEQUENCE PROMOTION_SEQ;
DROP SEQUENCE MEMBERLEVEL_SEQ;
DROP SEQUENCE CUSTOMER_SEQ;
DROP SEQUENCE PURCHASE_SEQ;
	      
CREATE SEQUENCE STORE_SEQ
	START WITH     1
	INCREMENT BY   1;

CREATE OR REPLACE TRIGGER STORE_TRIG
	BEFORE INSERT ON Store
	FOR EACH ROW
	BEGIN 
		:new.Store_ID := STORE_SEQ.nextval;
	END;
/



CREATE SEQUENCE COFFEE_SEQ
	START WITH     1
	INCREMENT BY   1;

CREATE OR REPLACE TRIGGER COFFEE_TRIG
	BEFORE INSERT ON Coffee
	FOR EACH ROW
	BEGIN 
		:new.Coffee_ID := COFFEE_SEQ.nextval;
	END;
/


CREATE SEQUENCE PROMOTION_SEQ
	START WITH     1
	INCREMENT BY   1;

CREATE OR REPLACE TRIGGER PROMOTION_TRIG
	BEFORE INSERT ON Promotion
	FOR EACH ROW
	BEGIN 
		:new.Promotion_ID := PROMOTION_SEQ.nextval;
	END;
/


CREATE SEQUENCE MEMBERLEVEL_SEQ
	START WITH     1
	INCREMENT BY   1;

CREATE OR REPLACE TRIGGER MEMBERLEVEL_TRIG
	BEFORE INSERT ON MemberLevel
	FOR EACH ROW
	BEGIN 
		:new.MemberLevel_ID := MEMBERLEVEL_SEQ.nextval;
	END;
/

CREATE SEQUENCE CUSTOMER_SEQ
	START WITH     1
	INCREMENT BY   1;

CREATE OR REPLACE TRIGGER CUSTOMER_TRIG
	BEFORE INSERT ON Customer
	FOR EACH ROW
	BEGIN 
		:new.Customer_ID := CUSTOMER_SEQ.nextval;
	END;
/

CREATE SEQUENCE PURCHASE_SEQ
	START WITH     1
	INCREMENT BY   1;

CREATE OR REPLACE TRIGGER Purchase_TRIG
	BEFORE INSERT ON Purchase
	FOR EACH ROW
	BEGIN 
		:new.Purchase_ID := PURCHASE_SEQ.nextval;
	END;
/
ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';
CREATE OR REPLACE TRIGGER trig_BuyCoffee
	AFTER INSERT ON BuyCoffee
	FOR EACH ROW
	DECLARE
	VALUE1 FLOAT;
	VALUE2 FLOAT;
	VALUE3 FLOAT;
	VALUE4 FLOAT;
	VALUE5 FLOAT;
	BEGIN 
	    --Grab Customer id
		 SELECT Customer_ID into VALUE1
		            FROM PURCHASE
		            WHERE :new.Purchase_id = Purchase.Purchase_id;
		SELECT Reward_Points into VALUE2
		            FROM COFFEE
		            WHERE :new.coffee_id = coffee_id;
		SELECT Booster_Factor into VALUE3
		            FROM Customer JOIN MEMBERLEVEL 
		                on CUSTOMER.MemberLevel_ID = MEMBERLEVEL.MemberLevel_ID
		            WHERE value1 = customer.customer_ID;
		SELECT COUNT(*) INTO VALUE5
			FROM PROMOTEFOR
			WHERE COFFEE_ID = :NEW.COFFEE_ID;

	    IF VALUE5 > 0 THEN
	        VALUE4 := 2;
	    ELSE
	        VALUE4 := 1;
	    END IF;
		UPDATE CUSTOMER SET TOTAL_POINTS = TOTAL_POINTS + (value2 * :new.Purchase_Quantity * value3 * value4) - (value2 * :new.Redeem_Quantity)
		WHERE value1 = CUSTOMER_ID;
	END;
	/