Syntax error

[expired user #7177]'s profile image [expired user #7177] posted 11 years ago in General Permalink
Trying to get this code to work in Heidi but it keeps coming up with a syntax error on line 5. No spacific words or parts as it says the error is in '' anyone got any ideas for this?



CREATE TRIGGER `custom_spawn`
BEFORE INSERT ON `survivor`
FOR EACH ROW
BEGIN
DECLARE x INT;
DECLARE y varchar(50);
SET x = (SELECT `unique_id` FROM `cust_spawn` WHERE `unique_id`=NEW.unique_id);
SET y = (SELECT `worldspace` FROM `cust_spawn` WHERE `unique_id`=NEW.unique_id);
IF NEW.unique_id = x THEN
SET NEW.worldspace=y;
END IF;
END;$$

Thanks in advance
ansgar's profile image ansgar posted 11 years ago Permalink
Did you set the delimiter to $$ first? You can do that by using the "DELIMITER $$" command, or by using the button with the red semicolon on the main toolbar.
ansgar's profile image ansgar posted 11 years ago Permalink
Or, do the whole trigger creation using HeidiSQL's trigger editor: Create new > Trigger.
[expired user #7497]'s profile image [expired user #7497] posted 10 years ago Permalink
I'm getting exactly the same behavior with 8.1.0.4545.

After much frustration, I found HeidiSQL's trigger editor and used it to create the trigger I wanted (using the same exact code), then copied the successful line from the log:
DROP TRIGGER `models_update_trigger`;
CREATE TRIGGER `models_update_trigger` BEFORE UPDATE ON `models` FOR EACH ROW BEGIN
INSERT INTO TPT.models_history
SELECT * FROM TPT.models WHERE
id = OLD.id;
END;


Copied it into a query window and executed, and got the error again:
CREATE TRIGGER `models_update_trigger` BEFORE UPDATE ON `models` FOR EACH ROW BEGIN
INSERT INTO TPT.models_history
SELECT * FROM TPT.models WHERE
id = OLD.id;
/* 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 '' at line 4 */


There's clearly something strange going on in the query window with triggers.
ansgar's profile image ansgar posted 10 years ago Permalink
As told above, you need to set the delimiter to something different than semicolon ";". The default *is* semicolon, so HeidiSQL splits your SQL code at the wrong place, after "...id = OLD.id;"

Please login to leave a reply, or register at first.