MySQL Insert after trigger clarification

Question

I have this code here:

CREATE TRIGGER testTrigger 
AFTER INSERT ON users
BEGIN 
    DECLARE @uid VARCHAR(60)
    SET @uid = (SELECT userid FROM inserted)
    INSERT INTO user_locations (id,uid,lat,lng) VALUES (0,@uid,5.0,5.0)
END;

The idea is to insert generated user id into other table alongside some other data as soon as it hits the first 'users' table but phpMyAdmin gives this error code:

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 @uid VARCHAR(60)
SET @uid = (SELECT userid FROM inserted)
at line 3

Can someone clarify why this trigger is bad?

score 1 · Accepted Answer · edited May 23 '17 at 10:28

I see four problems:

You have to use DELIMITERs so that your able to finish the commands with a semicolon as usual.
FOR EACH ROW is missing.
Use new.uid to access the recently inserted uid.
I'd also suggest using procedure variables instead of session-specific user-defined @variables, the latter ones being loosely typed and not declared as you've done.

But you don't even have to declare a variable. If you don't use phpMyAdmin:

DELIMITER //
CREATE TRIGGER testTrigger 
AFTER INSERT ON users FOR EACH ROW
BEGIN 
    INSERT INTO user_locations (id,uid,lat,lng) VALUES (0,new.uid,5.0,5.0);
END//
DELIMITER ;

Check this answer about delimiter and the MySQL 5.7 docs on triggers and this answer about variables.

Edit, I overread you're using phpMyAdmin:

I don't use phpMyAdmin. But you can (stolen from here)

In phpMyAdmin, select the database that you want to work with.
Go to the SQL tab at the top of the page.
In the "Run SQL query/queries on database" form, change the Delimiter to $$. (Located in a small box at the bottom of the form)
Enter your SQL trigger into the main dialog box on the form. The correct syntax is as follows:

CREATE TRIGGER testTrigger 
AFTER INSERT ON users FOR EACH ROW
BEGIN 
    INSERT INTO user_locations (id,uid,lat,lng) VALUES (0,new.uid,5.0,5.0);
END;$$

Hit "GO" with Super privilege.

MySQL Insert after trigger clarification

1 Answers1