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?

You can't do that (see answer bellow). But you can create a stored procedure, that updates the table, then deletes rows from this table (use transaction). — Valery Viktorovsky
– Valery Viktorovsky, Commented May 21, 2013 at 16:56

user4035 · Accepted Answer · 2013-05-21 16:56:29Z

1

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.

answered May 21, 2013 at 16:56

user4035

24k11 gold badges69 silver badges101 bronze badges

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

2 Comments

EGHDK Over a year ago

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 Over a year ago

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

Valery Viktorovsky · Accepted Answer · 2013-05-21 16:43:22Z

0

It's possible to associated trigger only with a table.

Also within a stored function or trigger, 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.

Restrictions on Stored Programs

answered May 21, 2013 at 16:43

Valery Viktorovsky

6,7863 gold badges47 silver badges52 bronze badges

Collectives™ on Stack Overflow

Setting up a trigger in PHPMyAdmin

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related