简而言之，不要这样做，而是创建一个view：

CREATE VIEW Stock
AS
    SELECT  IDofTheProduct, SUM(Quantity) AS Quantity
    FROM    (   SELECT  IDofTheProduct, Quantity
                FROM    Supplies
                UNION ALL
                SELECT  IdOfTheProduct, -Quantity
                FROM    Sales
            ) t
    GROUP BY IDofTheProduct;

字符串
这样，无论底层表发生什么变化，视图都会发生变化。这在更新/插入过程中开销较小，并且总是准确的，即使触发器由于某种原因没有触发。

已编辑

抱歉，我忘了MySQL不允许在视图中使用子查询。一个解决方案是创建一个新的视图来代替子查询：

CREATE VIEW SalesAndSupplies
AS
    SELECT  'Supplies' AS `Type`,
            IDofTheProduct, 
            Quantity
    FROM    Supplies
    UNION ALL
    SELECT  'Sales' AS `Type`,
            IDofTheProduct, 
            -Quantity
    FROM    Sales;

CREATE VIEW Stock
AS
    SELECT  IDofTheProduct, SUM(Quantity) AS Quantity
    FROM    SalesAndSupplies
    GROUP BY IDofTheProduct;

型

SQL Fiddle示例

带有视图的解决方案是一个很好的解决方案，但随着时间的推移，它往往会变得更慢，特别是如果你有很多销售和供应数据，因为你总是计算它。
因此，如果你决定通过触发器实现它，那么你必须至少有四个触发器（在两个表上插入和删除）。因为它们都更新股票，所以这部分代码最好分解为存储过程。
更新股票的存储过程

CREATE PROCEDURE sp_update_stock(IN pid INT, IN qty DECIMAL(11, 3))
    INSERT INTO stock (idoftheproduct, quantity) 
    VALUES (pid, qty)
    ON DUPLICATE KEY UPDATE quantity = quantity + qty;

字符串

• 如果在执行时不存在idoftheproduct的行，则将创建它。否则将更新它以反映更改。*

现在触发

CREATE TRIGGER tg_sales_insert
AFTER INSERT ON sales
FOR EACH ROW
    CALL sp_update_stock(NEW.idoftheproduct, -1 * NEW.quantity);

 CREATE TRIGGER tg_supplies_insert
 AFTER INSERT ON supplies
 FOR EACH ROW
    CALL sp_update_stock(NEW.idoftheproduct, NEW.quantity);

CREATE TRIGGER tg_sales_delete
AFTER DELETE ON sales
FOR EACH ROW
    CALL sp_update_stock(OLD.idoftheproduct, OLD.quantity);

CREATE TRIGGER tg_supplies_delete
AFTER DELETE ON supplies
FOR EACH ROW
    CALL sp_update_stock(OLD.idoftheproduct, -1 * OLD.quantity);

型
现在，您可以在Sales and Supplies中插入和删除。

INSERT INTO Supplies VALUES (NULL, 1, 100), (NULL, 1, 50), (NULL, 1, 75);
INSERT INTO Sales VALUES (NULL, 1, 2),(NULL, 1, 10), (NULL, 1, 5);
DELETE FROM Sales WHERE id = 1;
DELETE FROM Supplies WHERE id = 3;

型
如果看一下stock，

| IDOFTHEPRODUCT | QUANTITY |
-----------------------------
|              1 |      135 |

型
这里是SQLFiddledemo

我的情况与您相同，如果我将创建一个supplier、sales、detail_stock和detail_supplier表，则前面有名称 detail 的表将具有主键列、货物和供应商表的外键列、供应商发货量列（供应商发货详情），采购金额（对于购买表），然后最后一个是采购状态或供应商发货状态（这里我为每个细节使用两个状态）。我使用这个状态来触发我在下面作为查询创建的触发器。

-- TRIGGER
DELIMITER /
CREATE TRIGGER trigger_update_purchase
AFTER INSERT ON detail_status_transaction
FOR EACH ROW
BEGIN
    DECLARE quantity_of_items_purchased  INT;

    SET quantity_of_items_purchased = NEW.quantity_purchased;

    IF NEW.status_transaction = 'Completed' THEN
        -- Update stock_book
        UPDATE book
        SET stock_book = stock_book - quantity_of_items_purchased 
        WHERE book_id = (SELECT book_id FROM transaction WHERE id_nota = NEW.transaction_id);
    elseif NEW.transaction_status = 'Refund' then
        update book
        set stock_book = stock_book + quantity_of_items_purchased 
        where id_book = (select book_id from transaction where id_nota = NEW.transaction_id);
    END IF;
END;
DELIMITER ;

字符串
触发更新供应商

DELIMITER /
CREATE TRIGGER trigger_update_supplier
AFTER INSERT ON detail_status_supplier
FOR EACH ROW
BEGIN
    DECLARE  quantity_buy_items INT;

    SET  quantity_buy_items= NEW.quantity_shipped;

    IF NEW.status_supplier = 'Success' THEN
        -- Update book stock (supplier)
        UPDATE book
        SET stock_book = stock_book + quantity_buy_items
        WHERE id_book = (SELECT id_book FROM book WHERE id_book = NEW.book_id);
    elseif NEW.status_supplier = 'Failed' then
        update book
        set stock_book = stock_book -  quantity_buy_items
        where id_book = (SELECT id_book FROM book WHERE id_book = NEW.book_id);
    END IF;
END;
DELIMITER ;

型
然后，通过在 details 表中输入输入来触发此触发器，将根据触发的内容和所需的条件使库存减少或增加。