CODEWITHSUNDEEP
mysqltriggers
loomie
loomie

Reputation: 616

Creating a delete trigger

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

Answers (1)

fthiella
fthiella

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

Related Questions