MySQL-無法更新存盤函式/觸發器中的表，因為它已被呼叫此存盤函式/觸發器（選擇）的陳述句使用

我是 MySQL 的新手，正在學習觸發器。我有 2 個我想要的表：當插入一個表（detail_transaction）時，另一個表（專案）的“庫存”欄位發生變化。

• '專案'表

• 'detail_transaction' 表

如果我在“detail_transaction”表中插入新行，我希望“item”表中具有相同“id”的“stock”欄位減少并調整為“detail_transaction”的“count”。例如：我在“detail_transaction”表中插入新行：

我想將“專案”表更新為：

我創建了一個觸發器來嘗試實作我的目的，但是當我嘗試在“detail_transaction”中插入新行時出現此錯誤：無法更新存盤函式/觸發器中的“專案”表，因為它已被呼叫此陳述句的陳述句使用存盤函式/觸發器。

我的觸發器：

DELIMITER $$
CREATE TRIGGER update_stock
AFTER INSERT
    ON detail_transaction 
    FOR EACH ROW
BEGIN
    UPDATE item
    JOIN detail_transaction ON detail_transaction.id_item = item.id
    SET stock = stock - NEW.count
    WHERE item.id = NEW.id_item;
END$$
DELIMITER ;

然后，我將行插入 detail_transaction 表：

INSERT INTO detail_transaction (id, id_item, count, total_price)
VALUES (2, 1, 10, (SELECT price FROM item WHERE item.ID = 1) * 10);

但是我得到了錯誤。我能做些什么來解決這個問題？是因為我嘗試插入時的 SELECT 部分嗎？感謝您的回答。

uj5u.com熱心網友回復：

首先（而且固執己見）：觸發器很難除錯、測驗和維護。包含觸發器的系統真的很難除錯，因為它們會帶來副作用——“我在這張桌子上做了 X，然后 Y 發生在另一張桌子上”。作為開發人員，您必須牢記所有觸發器，以了解單個陳述句可能執行的操作。

例如，如果我們以您為例，您可能會在“庫存”欄位上觸發Item創建采購訂單記錄，以便在庫存低于閾值時補充庫存。該purchase order表可能有一個插入觸發器來在 中創建記錄accounts payable，如果給定供應商的總余額超過閾值，它可能有一個插入觸發器來拒絕記錄。detail_transaction該觸發器鏈實作了有效的業務邏輯，但由于產品供應商超出了他們的支付限額，突然插入被拒絕時導致非常復雜的除錯程序。（是的，我見過這種情況！）。

觸發器的挑戰之一是資料庫引擎不希望發生無限回圈，或者讓您正在選擇的欄位的值因觸發器觸發而改變。

此外，您不需要該連接 - 您可以從 NEW 中獲取值。

DELIMITER $$
CREATE TRIGGER update_stock
AFTER INSERT
    ON detail_transaction 
    FOR EACH ROW
BEGIN
    UPDATE item
    SET stock = stock - NEW.count
    WHERE item.id = NEW.id_item;
END$$
DELIMITER ;

這樣做的方法是使用一個變數：

SET @PRICE = ((SELECT price FROM item WHERE item.ID = 1) * 10);


INSERT INTO detail_transaction (id, id_item, count, total_price)
VALUES (2, 1, 10, @PRICE);

SELECT * from item;

見小提琴。

編輯- 其他一些答案顯示了一個更簡單的解決方案 - 計算觸發器中的總價。

理性的人不同意如何使用觸發器 - 但我建議使用觸發器來計算派生值 - “給定專案的總庫存”或“交易的總價格” - 通常不是一個好主意。您實際上是在復制資料 - 一個專案的總庫存水平既是交易總和，也是一行中的屬性。總價既是“價格*數量”，又是一行中的一個屬性。如果有人為 total_price 或 total_stock 執行更新陳述句（有意或作為錯誤的一部分）會發生什么？哪個值是正確的？

uj5u.com熱心網友回復：

你不應該混合 insert..values 和 insert..select 我會把插入重寫為

INSERT INTO detail_transaction (id, id_item, count, total_price)
 select 2, 1, 10,  price * 10 
 FROM item 
WHERE item.ID = 1;

雖然我的選擇是插入前觸發器

DELIMITER $$
CREATE TRIGGER update_stock before INSERT  ON detail_transaction 
FOR EACH ROW
BEGIN
    set new.total_price = (
     select item.price * new.count 
     FROM item 
     WHERE item.ID = new.id
     );
END$$
DELIMITER ;

帶插入物

INSERT INTO detail_transaction (id, id_item, count, total_price)
VALUES (2, 1, 10, null);

您的插入后發布失敗，因為您使用多表更新呼叫觸發觸發器的表，這是不允許的，此問題的解決方案出現在以前的答案中。

uj5u.com熱心網友回復：

CREATE TABLE item (
  `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255),
  `price` INTEGER,
  `stock` INTEGER
);
INSERT INTO item VALUES
  ('1', 'Item_A', '15', '900'),
  ('2', 'Item_B', '9', '500');
SELECT * FROM item;

CREATE TABLE detail_transaction (
  `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
  `id_item` INTEGER,
  `count` INTEGER,
  `total_price` INTEGER,
  FOREIGN KEY (`id_item`) REFERENCES `item` (`id`)
);
INSERT INTO detail_transaction VALUES
  ('1', '1', '5', '75');
SELECT * FROM detail_transaction;

-- trigger which calculates total_price value
CREATE TRIGGER tr_bi_get_total_price
BEFORE INSERT ON detail_transaction 
FOR EACH ROW
  SET NEW.total_price = (
    SELECT NEW.`count` * item.price
    FROM item
    WHERE id = NEW.id_item
    );

-- trigger which adjusts stock value
CREATE TRIGGER tr_ai_update_stock_in_item
AFTER INSERT ON detail_transaction 
FOR EACH ROW
  UPDATE item
  SET stock = stock - NEW.count
  WHERE item.id = NEW.id_item;

INSERT INTO detail_transaction (id_item, `count`) VALUES (1, 10);
SELECT * FROM detail_transaction;
SELECT * FROM item;

小提琴

附言。每個觸發器只包含一個陳述句。因此 BEGIN-END 和 DELIMITER 命令都不需要。

標籤：数据库触发器

上一篇：收到錯誤“statement.executeupdate()無法發出生成結果集的陳述句。”嘗試使用JDBC插入mysql時

下一篇：有沒有辦法在多個條件下獲得一行結果？