distal-attribute
distal-attribute
distal-attribute
distal-attribute

Syntax error

User, date Message
Written by scottieo321
9 months ago
Category: General
2 posts since Sun, 28 Jul 13
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
Written by ansgar
9 months ago
4800 posts since Fri, 07 Apr 06
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.
Written by ansgar
9 months ago
4800 posts since Fri, 07 Apr 06
Or, do the whole trigger creation using HeidiSQL's trigger editor: Create new > Trigger.
Written by tcurtin
4 months ago
1 posts since Tue, 17 Dec 13
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.
Written by ansgar
4 months ago
4800 posts since Fri, 07 Apr 06
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.