functions.sql

USE MCRONALDS_DB;
DELIMITER $$
CREATE PROCEDURE MAKE_ORDER(order_id INTEGER, item_id INTEGER, quantity INTEGER)
BEGIN
INSERT INTO ORDERLINE_T VALUES(order_id, item_id, quantity);
END$$;
DELIMITER;

DELIMITER $$
CREATE PROCEDURE FINISH_ORDERING(order_id INTEGER, customer_id INTEGER)
BEGIN
INSERT INTO RECEIPT_T VALUES(order_id, customer_id);
END$$
DELIMITER;

DELIMITER $$
CREATE TRIGGER CONTINUE_ORDER
BEFORE INSERT ON ORDERLINE_T
FOR EACH ROW
BEGIN
IF EXISTS (
	SELECT RECEIPT_T.OrderID FROM RECEIPT_T
    WHERE NEW.OrderID = RECEIPT_T.OrderID
)
THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot edit this order. The order has already been sent to the kitchen.';
ELSE
SET @price = (SELECT ITEM_T.Price FROM ITEM_T WHERE NEW.ItemID = ITEM_T.ItemID) * NEW.OrderedQuantity;
UPDATE ORDER_T SET ORDER_T.SalePrice = ORDER_T.SalePrice + @price WHERE NEW.OrderID = ORDER_T.OrderID;
END IF;
END$$