IF STATEMENT in DELETE TRIGGER - FAILURE

Question

So I've tried a whole lot of diffrent things with If statements, and i simply can't seem to work out how to do this.

I wan't to make the trigger delete the row with the lowest ID, if it recurring. And by recurring i mean the number and username are the same in two rows.

For example

ROW1: ID: 1 , Nr: 1 , UN: MVJ and 

Row2: ID: 2 , Nr: 1 , UN: MVJ

Those are recurring, but if the 'Nr' or 'UN' were different, they wouldn't be.

So here is my try so far.

CREATE TRIGGER no_double_reservations 
AFTER INSERT ON tilmeldte 
FOR EACH ROW 
WHERE
'SELECT COUNT(*) from tilmeldte WHERE (kursus_nr, username) IN ( SELECT kursus_nr, username FROM tilmeldte GROUP BY kursus_nr, username HAVING count(*) = 2 )' = '2'

DELETE from tilmeldte Where tilmeldingsid = 
'Select min(`tilmeldingsid`) from tilmeldte WHERE (kursus_nr, username) IN ( SELECT min(kursus_nr), username FROM tilmeldte GROUP BY kursus_nr, username HAVING count(*) = 2 )' 

END;

Found the right syntax, but found out the move was impossible in SQL. You can't delete a row from the same the table you select which row to delete.

The right syntax was:

DELIMITER !!
CREATE TRIGGER no_double_reservations 
AFTER INSERT ON tilmeldte 
FOR EACH ROW BEGIN
IF(
SELECT COUNT(*) from tilmeldte WHERE (kursus_nr, username) IN ( SELECT kursus_nr, username FROM tilmeldte GROUP BY kursus_nr, username HAVING count(*) = 2 ) = 2)
THEN
DELETE from tilmeldte Where tilmeldingsid = (Select min(`tilmeldingsid`) from tilmeldte WHERE (kursus_nr, username) IN ( SELECT min(kursus_nr), username FROM tilmeldte GROUP BY kursus_nr, username HAVING count(*) = 2 ));
END IF;

END!!

DELIMITER ;

Answer 1

Your trigger has some syntax errors, but those are somewhat unimportant, because what you are trying to do is not possible:

A stored function or trigger cannot 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.6/en/stored-program-restrictions.html

Triggers in MySQL cannot modify other rows in the table that the trigger is defined against. That would open up the possibility for infinite trigger recursion, e.g. a trigger on row A modifies row B, which fires a trigger that modifies row A, which fires a trigger that modifies row B, which...