The syntax for the trigger, can't find the error?

CREATE TRIGGER `update_balance` AFTER UPDATE ON `trading`
FOR EACH ROW BEGIN
 IF (NEW.`line` = 'up' AND NEW.`price_end` > NEW.`price`) THEN
BEGIN
 UPDATE `clients` SET `balance` = `balance` + NEW.`sum` * 1.85 WHERE `email` = NEW.`user_id`;
END
 ELSE IF (NEW.`line` = 'down' AND NEW.`price_end` < NEW.`price`) THEN
BEGIN
 UPDATE `clients` SET `balance` = `balance` + NEW.`sum` * 1.85 WHERE `email` = NEW.`user_id`;
END
END
END;


Gives an error, what could be wrong?

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 " at line 5


5 string, this is the first UPDATE
June 10th 19 at 15:26
1 answer
June 10th 19 at 15:28
Solution
To begin, decide what you have the SQL dialect is not to shove all tags in a row. In different dialects the syntax may be different.
MySQL before trigger definition, you must change the character terminating the command (default is semicolon).
DELIMITER $
CREATE TRIGGER ...
...
END;
$
DELIMITER ;
DELIMITER $
CREATE TRIGGER `update_balance` AFTER UPDATE ON `trading`
FOR EACH ROW BEGIN
 IF (NEW.`line` = 'up' AND NEW.`price_end` > NEW.`price`) THEN
 UPDATE `clients` SET `balance` = `balance` + NEW.`sum` * 1.85 WHERE `email` = NEW.`user_id`;
 ELSEIF (NEW.`line` = 'down' AND NEW.`price_end` < NEW.`price`) THEN
 UPDATE `clients` SET `balance` = `balance` + NEW.`sum` * 1.85 WHERE `email` = NEW.`user_id`;
 END IF
END;
$
DELIMITER ;

#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' at line 8
- Myrtice commented on June 10th 19 at 15:31
The semicolon after END IF forgotten. - Kenyatta commented on June 10th 19 at 15:34
Thank you! - Myrtice commented on June 10th 19 at 15:37

Find more questions by tags SQLMySQL