在這篇"實作練習 : PHP + Stored Procedure + Stored Function + Trigger"中，練習了使用Trigger/Stored Function/Stored Procedure並在PHP實現，但是因為在新增訂單時並沒有檢查庫存，因此必須再進一步來更新相關邏輯。
前面的練習已經實現了 :
(1) 建立 Trigger 可以自動減去訂單數量，用來維護庫存量；
(2) 建立 Stored Procedure 用來建立訂單；
(3) 建立 Trigger 用來記錄建立訂單的log；
(4) 建立 Stored Function 用來計算訂單總額；
(5) 以php呼叫Stored Procedure 以介面來輸入訂單資料。
檢查庫存的邏輯應該寫在哪個地方?
目前有兩個觸發器 : UpdateStockAfterOrder 及 LogOrderAfterInsert
預存程序 CreateOrder 及 預存函式 CalculateOrderTotal
比較有可能的是 UpdateStockAfterOrder 與 CreateOrder
以下是觸發器UpdateStockAfterOrder
DELIMITER //
CREATE TRIGGER UpdateStockAfterOrder
AFTER INSERT ON OrderDetails
FOR EACH ROW
BEGIN
UPDATE Products SET Stock = Stock - NEW.Quantity WHERE ProductID = NEW.ProductID;
END //
DELIMITER ;
觸發器UpdateStockAfterOrder是當新增訂單資料到OrderDetails後，會自動去更新庫存，但是AFTER INSERT再檢查庫存是否足夠就沒有意義，因此應該在CreateOrder ~~ 新增資料到OrderDetails的程序中檢查。
原本CreateOrder的Stored Procedure如下 :
DELIMITER //
CREATE PROCEDURE CreateOrder(
IN p_CustomerID INT,
IN p_OrderDetails JSON )
BEGIN
DECLARE v_OrderID INT;
DECLARE v_TotalAmount
DECIMAL(10, 2) DEFAULT 0.00;
DECLARE v_ProductID INT;
DECLARE v_Quantity INT;
DECLARE v_Price DECIMAL(10, 2);
DECLARE v_Iterator INT DEFAULT 0;
DECLARE v_ItemCount INT;
SET v_ItemCount = JSON_LENGTH(p_OrderDetails);
INSERT INTO Orders (CustomerID, TotalAmount) VALUES (p_CustomerID, 0.00);
SET v_OrderID = LAST_INSERT_ID();
WHILE v_Iterator < v_ItemCount DO
SET v_ProductID = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].ProductID')));
SET v_Quantity = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].Quantity')));
SET v_Price = (SELECT Price FROM Products WHERE ProductID = v_ProductID);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);
SET v_TotalAmount = v_TotalAmount + (v_Quantity * v_Price);
SET v_Iterator = v_Iterator + 1;
END WHILE;
UPDATE Orders SET TotalAmount = v_TotalAmount
WHERE OrderID = v_OrderID;
END //
DELIMITER ;
修改後如下 :
DELIMITER //
CREATE PROCEDURE CreateOrder(
IN p_CustomerID INT,
IN p_OrderDetails JSON )
BEGIN
DECLARE v_OrderID INT;
DECLARE v_TotalAmount
DECIMAL(10, 2) DEFAULT 0.00;
DECLARE v_ProductID INT;
DECLARE v_Quantity INT;
DECLARE v_Price DECIMAL(10, 2);
DECLARE v_Stock INT;
DECLARE v_Iterator INT DEFAULT 0;
DECLARE v_ItemCount INT;
SET v_ItemCount = JSON_LENGTH(p_OrderDetails);
INSERT INTO Orders (CustomerID, TotalAmount) VALUES (p_CustomerID, 0.00);
SET v_OrderID = LAST_INSERT_ID();
WHILE v_Iterator < v_ItemCount DO
SET v_ProductID = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].ProductID')));
SET v_Quantity = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].Quantity')));
-- 查詢產品價格和庫存
SELECT Price, Stock INTO v_Price, v_Stock
FROM Products
WHERE ProductID = v_ProductID;
-- 檢查庫存是否足夠
IF v_Stock IS NULL THEN
INSERT INTO OrderLogs (OrderID, LogMessage)
VALUES (v_OrderID, CONCAT('Product ', v_ProductID, ' does not exist in inventory'));
ELSEIF v_Stock < v_Quantity THEN
INSERT INTO OrderLogs (OrderID, LogMessage)
VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Tried to order ', v_Quantity, ' but only ', v_Stock, ' available.'));
ELSE
-- 如果庫存足夠，將產品加入訂單
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);
END IF;
SET v_Iterator = v_Iterator + 1;
END WHILE;
-- 最後更新總金額
UPDATE Orders SET TotalAmount = CalculateOrderTotal(v_OrderID)
WHERE OrderID = v_OrderID;
END //
DELIMITER ;
CustomerID : 1
Order Details : [{"ProductID": 1, "Quantity": 2}, {"ProductID": 2, "Quantity": 100}]
產品Product A庫存從35變成33，完全正確，並且產品Product B因為庫存不足，並沒有寫入訂單，因此庫存量不變。
訂單表頭資訊如下 :
訂單表身資訊如下 :
訂單log資訊如下 :
LogMessage詳細資訊如下 :
){kind=link}
0 留言