Setting up a trigger in PHPMyAdmin

Question

I'm trying to comprehend triggers, and I think I fully understand them, but I haven't been able to implement any of them. I want this code to delete a user with the name "test". So if anyone updates their name to "test" the user should be deleted.

My example code:

CREATE TRIGGER `my_trigger`
BEFORE UPDATE ON `my_db` FOR EACH ROW
BEGIN
DELETE FROM my_table WHERE `username` = 'test';
END

My 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 '' at line 4 

I can't figure out why the delete statement is giving me an error. Any ideas?

Solution

Here is the syntaxically correct SQL:

DELIMITER ;
DROP TRIGGER IF EXISTS `my_trigger`;
DELIMITER $$
CREATE TRIGGER `my_trigger`
BEFORE UPDATE ON `my_table` FOR EACH ROW
BEGIN
    DELETE FROM my_table WHERE `username` = 'test';
END$$
DELIMITER;

But it won't work, because you can't delete from the table, you are updating:

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

http://dev.mysql.com/doc/refman/5.5/en/faqs-triggers.html#qandaitem-B-5-1-9

If you want a simple example, try this:

DELIMITER ;
DROP TRIGGER IF EXISTS `my_trigger`;
DELIMITER $$
CREATE TRIGGER `my_trigger`
BEFORE UPDATE ON `my_table` FOR EACH ROW
BEGIN
    SET NEW.`username` = 'aaa';
END$$
DELIMITER;

This will always set 'aaa' as the user name when updating.

Comments

[EGHDK]

I'm not sure I understand why you have DELIMITER written up there. All of the examples/tutorials don't have that at all. Can you elaborate?

[user4035]

@EGHDK Changeing delimiter is necessary, because MySQL is seeing the first ";" as the end of the CREATE TRIGGER statement. stackoverflow.com/questions/1346637/…