Creating a delete trigger

Question

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?

fthiella · Accepted Answer · 2013-05-03 21:41:13Z

5

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 ;

answered May 3, 2013 at 21:41

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

2 Comments

loomie Over a year ago

So the only thing I did wrong was the OR REPLACE? Or Am I seeing something wrong there? And yes, this works, thanks.

fthiella Over a year ago

@loomie you're welcome :) yes i don't think it's possible to use OR REPLACE, and also the old value of id is old.id and not :old.id

Collectives™ on Stack Overflow

Creating a delete trigger

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related