triggers and views.sql

-- USE COVIDVACCINE


/* view for order id , customer(HMO) , supplier 
CREATE VIEW HMO_ORDER_SUPPLIER AS
SELECT O.OID AS 'Order number', H.HMONAME AS 'Customer' , S.SNAME AS 'Supplier'			
FROM HMO_TABLE H
JOIN ORDER_TABLE O
ON (O.HMOLICENSE = H.HMOLICENSE)
JOIN SUPPLIER_TABLE S
ON(O.SLICENSE = S.SLICENSE);
*/


/* view for person name blood type and vaccine status
CREATE VIEW PERSON_BLOODTYPE_VACCINESTATUS AS
SELECT P.FNAME , P.LNAME , M.BLOODTYPE , M.VACCINESTATUS
FROM PERSON_TABLE P
INNER JOIN MEDICALRECORD_TABLE M
ON (P.PID=M.PID AND M.BLOODTYPE = 'B-')
ORDER BY P.FNAME;
*/


/* trigger that up in 1 in town population every time we add person 
DELIMITER $$

CREATE TRIGGER UPDATE_POPULATION 
AFTER INSERT ON PERSON_TABLE
FOR EACH ROW 
BEGIN 
	UPDATE TOWN_TABLE SET POPULATION = POPULATION + 1 
    WHERE TNAME = NEW.LIVEIN;
		
    END$$

DELIMITER ;
*/


/* trigger that update stock in hmo after
 delivery status become delivered
DELIMITER $$

CREATE
	TRIGGER UPDATE_ORDER AFTER UPDATE 
	ON ORDER_TABLE 
	FOR EACH ROW BEGIN
	
		IF NEW.STATUS = 'DELIVERED' THEN
			UPDATE HMO_TABLE H
            JOIN ORDER_TABLE O
            SET H.STOCK = H.STOCK + O.QUANTITY
            WHERE O.HMOLICENSE=H.HMOLICENSE;
		END IF;
		
    END$$

DELIMITER ;
*/