Reputation: 616
I have a table called ul_logins
and a table called userbase
and userchanges
. I know, want, whenever I delete some user from ul_logins
a trigger to automatically delete the same user from userbase
and userchanges
. The id of the user is id
at ul_logins
and userID
at userbase
and userchanges
I have written this trigger in mysql (phpmyadmin)
CREATE OR REPLACE TRIGGER deleteUser AFTER DELETE ON ul_logins
FOR EACH ROW BEGIN
DELETE FROM userbase WHERE userID = :old.id;
END
But it doesn't work, I get an error all the time. But I just can't find what the problem is
#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 'TRIGGER deleteUser AFTER DELETE ON ul_logins FOR EACH ROW BEGIN DELETE FROM us' at line 1
I already googled and there was another solution and tried this
DELIMITER |
CREATE OR REPLACE TRIGGER deleteUser AFTER DELETE ON ul_logins
FOR EACH ROW BEGIN
DELETE FROM userbase WHERE userID = :old.id;
END;|
DELIMITER ;
but this didn't work neitehr. Any suggestions?
Upvotes: 3
Views: 8825
Reputation: 49089
This should be the correct syntax:
DROP TRIGGER IF EXISTS deleteUser;
DELIMITER |
CREATE TRIGGER deleteUser AFTER DELETE ON ul_logins
FOR EACH ROW BEGIN
DELETE FROM userbase WHERE userID = old.id;
END;
|
DELIMITER ;
Upvotes: 5