MySQL: Trigger error: #1193 - Unknown system variable 'PurchasePrice'

Question

I'm trying to create a trigger where if someone says they have a discount code, the purchaseprice of their ticket drops by $10.

I ran the code:

CREATE TRIGGER alterPurchasePrice AFTER INSERT ON CustomerOrders FOR EACH ROW 
BEGIN IF DiscountCode = 'yes' THEN SET PurchasePrice = Cost - 10 END$$

But got the error:

#1193 - Unknown system variable 'PurchasePrice'

However, I have that column in my table. So I don't understand why it's not recognizing it?

Answer 1

You need to identify the column as coming from a table:

DELIMITER $$

CREATE TRIGGER alterPurchasePrice
    BEFORE INSERT ON CustomerOrders
     FOR EACH ROW 
BEGIN
    IF new.DiscountCode = 'yes' THEN
        SET new.PurchasePrice = new.Cost - 10;
    END IF;
END$$

DELIMITER ;

If you want to re-set the value, it should be a before-insert trigger.