MySQL trigger syntax error (based on two tables, with if statement)

Question

I'm getting a syntax error while trying to create a trigger. I'm far from MySQL expert but managed to create the following code based on info I found on stackoverflow. The purpose of the trigger is to check if the sales row relates to an auction item (rather than a buy now item) and if it does then calculate a premium of 10% on the sale price (insert the premium amount into the premium column). "listing_id" is the link between the listings table and sales listings table.

DELIMITER $$
CREATE TRIGGER add_premium
AFTER INSERT
   ON sales_listings FOR EACH ROW
BEGIN
   SET @listing_type := (SELECT listing_type
                     FROM listings
                     WHERE listings.listing_id = NEW.listing_id);
   IF @listing_type = 'auction' THEN
            NEW.premium = NEW.price * 0.1;
   ELSE
        NEW.premium = 0;
   END IF;
END$$
DELIMITER;

This is the syntax error:

 #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 '.premium = NEW.price * 0.1;
   ELSE
            NEW.premium = 0;
   END IF;
' at line 9

What am I doing wrong?

Bill Karwin · Accepted Answer · 2017-03-18 19:25:02Z

1

An assignment needs to be preceded by SET.

IF @listing_type = 'auction' THEN
    SET NEW.premium = NEW.price * 0.1;
ELSE
    SET NEW.premium = 0;
END IF;

answered Mar 18, 2017 at 19:25

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Doron Barel Over a year ago

I added the SET but now I'm getting this error: "#1362 - Updating of NEW row is not allowed in after trigger"... if I change the SET to the following, will it only affect the new row that was inserted or all rows? SET sales_listings.premium = NEW.price * 0.1;

Doron Barel Over a year ago

I changed the trigger to before insert and updated the code as follows, but now nothing gets inserted into the table for some reason: SET NEW.premium = NEW.price * 0.1;

Bill Karwin Over a year ago

I have no idea how you are running the INSERT. Is it from some application? If so, are you checking for errors after executing the INSERT? Maybe you are violating a unique constraint or something. If you're doing a test INSERT in the mysql client, check for errors. Also try dropping the trigger and seeing if the INSERT succeeds.

Collectives™ on Stack Overflow

MySQL trigger syntax error (based on two tables, with if statement)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related