Syntax Error Mysql

Question

When new user registered automatically its permissionID will be 1. It's just that simple, but mysql syntax errors are killing me. Please help

CREATE TRIGGER `user_default_role` 
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
DECLARE @userID int;
SET @userID = (SELECT userID FROM inserted LIMIT 1);
INSERT INTO `user_permission` (`userID`,`permissionID`,`created_at`,`updated_at`) VALUES (@userID,1 ,NOW(),null);
END

This is 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 '@userID int' at line 5

EDIT: I didn't know about DELIMETER and NEW instead of inserted. Thank you for all who responded so quickly. Here is the updated code:

DELIMITER $$
CREATE TRIGGER `user_default_role` AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `user_permission` (`userID`, `permissionID`, `created_at`, `updated_at`)
        VALUES (new.userID, 1, NOW(), null);
END;$$
DELIMITER ;

Do you have a delimiter statement before the create trigger? Do you have a table called inserted? That looks suspicious. — Gordon Linoff
– Gordon Linoff, Commented Jun 16, 2016 at 9:09

Gordon Linoff · Accepted Answer · 2016-06-16 09:12:04Z

1

inserted is something from SQL Server, but otherwise your syntax really does suggest MySQL. This may work for you:

DELIMITER $$

CREATE TRIGGER `user_default_role` AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `user_permission` (`userID`, `permissionID`, `created_at`, `updated_at`)
        VALUES (new.userId, 1, NOW(), null);
END;$$

DELIMITER ;

answered Jun 16, 2016 at 9:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

Abhishek · Accepted Answer · 2016-06-16 09:22:22Z

1

You are getting syntax error because of following the reasons:

1- Missing Delimiters.

2- Session variable(starting from @) cannot be declared.you can directly set the values to them.

3- There is no inserted table in MySQL where triggers temporary inserts data.You can access inserted values using NEW.

DELIMITER $$
CREATE TRIGGER `user_default_role` 
AFTER INSERT ON `users`
 FOR EACH ROW
    BEGIN
    SET @userID =NEW.userID;
    INSERT INTO `user_permission` (`userID`,`permissionID`,`created_at`,`updated_at`) VALUES (@userID,1 ,NOW(),null);
   END$$
DELIMITER ;

edited Jun 16, 2016 at 9:22

answered Jun 16, 2016 at 9:13

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

Syntax Error Mysql

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