TRIGGERS.txt

CREATE OR REPLACE TRIGGER UPDATE_USER_INFO_AND_BILL
BEFORE INSERT OR UPDATE
ON SUBSCRIPTION
FOR EACH ROW
DECLARE
	USER_EMAIL VARCHAR2(30);
	NUM_PROFILES INTEGER;
	SUBSCR_TYPE VARCHAR2(3);
	SUBSCR_ID INTEGER;
	MONTHLY_BILL NUMBER;
BEGIN
	USER_EMAIL := :NEW.EMAIL;
	SUBSCR_TYPE := :NEW.SUB_TYPE;
	SUBSCR_ID := :NEW.SUB_ID;
	IF SUBSCR_TYPE='BAS' THEN
			NUM_PROFILES := 2;
			MONTHLY_BILL := 5;
	ELSIF SUBSCR_TYPE='STA' THEN
		NUM_PROFILES := 4;
		MONTHLY_BILL := 8;
	ELSE
		NUM_PROFILES := 6;
		MONTHLY_BILL := 10;
	END IF;
	
	:NEW.BILL := MONTHLY_BILL;
	UPDATE USER_NETFLIX SET MAX_PROFILES=NUM_PROFILES
	WHERE EMAIL = USER_EMAIL;
	
EXCEPTION	
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS');
	WHEN DUP_VAL_ON_INDEX THEN
		DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUE ON INDEX');
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR');
END;
/


CREATE OR REPLACE TRIGGER UPDATE_MOVIE_RATING_1
AFTER UPDATE
OF RATING
ON MOVIE_WATCH
FOR EACH ROW

DECLARE
	NMID NUMBER;
	NRATING NUMBER;
	ORATING NUMBER;

BEGIN
	NMID := :NEW.MOVIE_ID;
	NRATING := :NEW.RATING;
	ORATING := :OLD.RATING; 
	
	UPDATE MOVIE
	SET RATING = ((RATING*TOTAL_VOTES)+NRATING-ORATING)/(TOTAL_VOTES)
	WHERE MOVIE_ID = NMID;
	 
END ;
/

CREATE OR REPLACE TRIGGER UPDATE_MOVIE_RATING_2
AFTER INSERT
ON MOVIE_WATCH
FOR EACH ROW

DECLARE
	NMID NUMBER;
	NRATING NUMBER;

BEGIN
	NMID := :NEW.MOVIE_ID;
	NRATING := NVL(:NEW.RATING, 0);

	
	UPDATE MOVIE
	SET RATING = ((RATING*TOTAL_VOTES)+NRATING)/(TOTAL_VOTES+1),
							TOTAL_VOTES = TOTAL_VOTES + 1,
							TOTAL_VIEWS = TOTAL_VIEWS + 1
	WHERE MOVIE_ID = NMID;
	 
END ;
/

CREATE OR REPLACE TRIGGER UPDATE_SHOW_RATING_1
AFTER UPDATE
OF RATING
ON SHOW_WATCH
FOR EACH ROW

DECLARE
	NSID NUMBER;
	NRATING NUMBER;
	ORATING NUMBER;

BEGIN
	NSID := :NEW.SHOW_ID;
	NRATING := :NEW.RATING;
	ORATING := :OLD.RATING; 
	
	UPDATE SHOW
	SET RATING = ((RATING*TOTAL_VOTES)+NRATING-ORATING)/(TOTAL_VOTES)
	WHERE SHOW_ID = NSID;
	 
END ;
/

CREATE OR REPLACE TRIGGER UPDATE_SHOW_RATING_2
AFTER INSERT
ON SHOW_WATCH
FOR EACH ROW

DECLARE
	NSID NUMBER;
	NRATING NUMBER;

BEGIN
	NSID := :NEW.SHOW_ID;
	NRATING := NVL(:NEW.RATING, 0); 
	
	UPDATE SHOW
	SET RATING = ((RATING*TOTAL_VOTES)+NRATING)/(TOTAL_VOTES+1),
							TOTAL_VOTES = TOTAL_VOTES + 1,
							TOTAL_VIEWS = TOTAL_VIEWS + 1
	WHERE SHOW_ID = NSID;
	 
END ;
/