-
Notifications
You must be signed in to change notification settings - Fork 0
/
functions.sql
30 lines (28 loc) · 895 Bytes
/
functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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$$