Ogün ADSAY Ogün ADSAY - 5 months ago 12
MySQL Question

Syntax Error Mysql

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 ;

Answer

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 ;
Comments