SQL Error #1064 while Create Trigger

Question

I'm trying to create an Trigger within my MySQL Database, but I always get the 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 'BEGIN DECLARE tmppl INT; SELECT MAX(PortListID) FROM pj_servers INTO t' at line 2

This is the query. I just can't find the error.

CREATE TRIGGER init_new_server AFTER INSERT ON pj_servers
BEGIN
    DECLARE tmppl INT;
    SELECT MAX(PortListID) FROM pj_servers INTO tmppl;

    INSERT INTO pj_servers (Registered, PortListID)
    VALUES (Current_Timestamp, (tmppl+1))
    WHERE pj_servers.ID = NEW.ID;

    INSERT INTO pj_serverports (PortList, Port, isOpen, Script)
    VALUES (tmppl+1, 80, TRUE, "say(\"Hallo\"");
END$$

dnll · Accepted Answer · 2014-02-10 17:47:29Z

1

According to the MySQL Reference Manual:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

so probably you're missing FOR EACH ROW in the first line

answered Feb 10, 2014 at 17:47

dnll

6775 silver badges8 bronze badges

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

Comments

UrKll · Accepted Answer · 2014-02-10 18:11:14Z

0

OK found it, dnll was right with the FOR EACH ROW but also I tried to make the Insert with an WHERE statement, wich clearly couldn't work.

fixed code is:

CREATE TRIGGER init_new_server AFTER INSERT ON pj_servers
FOR EACH
ROW BEGIN
DECLARE tmppl INT;

SELECT MAX( PortListID )
FROM pj_servers
INTO tmppl;

UPDATE pj_servers SET Registered = CURRENT_TIMESTAMP ,
PortListID = tmppl +1 WHERE pj_servers.ID = NEW.ID;

INSERT INTO pj_serverports( PortList, Port, isOpen, Script )
VALUES (
tmppl +1, 80,
TRUE , "say(\"Hallo\")"
);

END

answered Feb 10, 2014 at 18:11

UrKll

9931 gold badge8 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Error #1064 while Create Trigger

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related