MySql Trigger, before insert delete a row in the same table

Question

Is this possible? Before a row is inserted into tableA, I need to delete any rows in tableA containing duplicate data.The trigger below does not generate an error but doesn't work either.

CREATE TRIGGER remove_old_user
BEFORE INSERT ON userStatus
FOR EACH ROW
DELETE FROM userStatus WHERE username = NEW.username

Any ideas?

Answer 1

Is it essential that the previous record be deleted, rather than updated to match the new info? Sounds like INSERT ... ON DUPLICATE KEY UPDATE would meet most of your needs. More info in the MySQL manual.

(I'm assuming based on the question that the field is unique, and can be part of the primary key)