What's wrong with my MYSQL trigger syntax?

Question

CREATE TRIGGER event_house_dealed AFTER UPDATE ON house FOR EACH ROW 
BEGIN
DECLARE finished INT;
IF NEW.isdeal = 1 THEN
    SET finished = 1;
END IF;
END;

The MySQL server keep saying

SQL 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 'IF NEW.isdeal = 1 THEN SET finished = 1' at line 1

Thanks.

Mchl · Accepted Answer · 2011-01-16 11:26:16Z

2

You need to change delimiter before defining trigger

DELIMITER ||
CREATE TRIGGER event_house_dealed AFTER UPDATE ON house FOR EACH ROW 
BEGIN
DECLARE finished INT;
IF NEW.isdeal = 1 THEN
    SET finished = 1;
END IF;
END||

Then of course change the delimiter back to ;

DELIMITER ;

Same goes for declaring functions and stored procedures

answered Jan 16, 2011 at 11:26

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

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

Collectives™ on Stack Overflow

What's wrong with my MYSQL trigger syntax?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related