SQL新手求助：创建Insert触发器实现商品库存扣减

Hey there! Let's get that trigger working correctly for your PHP e-commerce site. First, let's break down the issues in your original code, then I'll share the corrected version with explanations.

Issues in Your Original Trigger #

• MySQL uses NEW (not INSERTED) to reference the newly inserted row—INSERTED is a SQL Server syntax.
• You don't need to query the Receipt table again: the NEW keyword gives you direct access to the values of the row just added, saving unnecessary database calls.
• Local trigger variables in MySQL don't use @ (that's for user-level variables); use DECLARE with explicit data types and lengths instead.
• Your variable assignment syntax was incorrect, and you missed the Store. schema prefix on the Item table in the UPDATE statement.

Corrected Trigger Code #

DELIMITER $$
CREATE TRIGGER update_stock_trigger
AFTER INSERT ON Store.Receipt
FOR EACH ROW
BEGIN
    -- Deduct the sold quantity from the corresponding item's stock
    UPDATE Store.Item
    SET QtyStock = QtyStock - NEW.QtySold
    WHERE ItemID = NEW.ItemID;
END$$
DELIMITER ;

Key Details to Understand #

• AFTER INSERT ON Store.Receipt: This trigger activates immediately after a new row is added to the receipt table.
• FOR EACH ROW: Ensures the trigger runs once for every individual row inserted—critical because one receipt can include multiple items.
• NEW.QtySold & NEW.ItemID: These directly pull the sold quantity and item ID from the freshly inserted receipt entry, no extra queries needed.
• UPDATE Store.Item SET QtyStock = QtyStock - NEW.QtySold: Adjusts the item's stock by subtracting the sold amount for the matching ItemID.

Extra Recommendations #

• Add a check constraint to Store.Item to prevent negative stock (e.g., ALTER TABLE Store.Item ADD CONSTRAINT chk_positive_stock CHECK (QtyStock >= 0);). This avoids invalid stock values if someone tries to sell more than you have.
• If you ever need to correct receipt entries (like updating a sold quantity or deleting a receipt), you'll need additional triggers for AFTER UPDATE and AFTER DELETE to adjust stock back accordingly.

内容的提问来源于stack exchange，提问作者Patcharagit Strauss