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

Syntax error

User, date Message
Written by scottieo321
12 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
12 months ago
4938 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
12 months ago
4938 posts since Fri, 07 Apr 06
Or, do the whole trigger creation using HeidiSQL's trigger editor: Create new > Trigger.
Written by tcurtin
7 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
7 months ago
4938 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.