2

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?

DJPharaohCHS
  • 181
  • 1
  • 3
  • 13

2 Answers2

4

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • When I run that I get: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF END' at line 7 . I tried it with just END$$ but get the same error. – DJPharaohCHS Apr 17 '17 at 02:31
  • Any thoughts on why it would still error out after the change? – DJPharaohCHS Apr 17 '17 at 19:36
  • @DJPharaohCHS . . . Added a semicolon to the `END IF`. – Gordon Linoff Apr 17 '17 at 23:17
-2

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

You didn't use NEW keyword. Kindly use it

rajul agrawal
  • 1
  • 2
    It would be immensely helpful if you remembered to format your code as... code ;) Maybe also highlight your additions with bold, so they are easier to spot. – Atheist Apr 26 '21 at 12:59
  • 1
    How is this answer different/better from the accepted one? Try to avoid bumping up old posts, especially the ones with accepted answers. – Janez Kuhar Apr 26 '21 at 14:02