on delete cascade not firing trigger

Question

I have a table called 'player_instance' and then a table called 'penalties' (which has a foreign key referencing player_instance). I also have a table called 'scores'.

I have a DELETE TRIGGER on penalties such that if a row is deleted then it will alter a column in scores. I know this works - when a penalty is deleted it will decrease a column in scores.

I also have a ON DELETE CASCADE where if player_instance is deleted then all associated penalties will also be deleted. I know this works too.

But when player_instance is deleted it will only delete the relevant penalties and not fire the trigger to alter scores. Can anyone help me understand why this is not working?

Many thanks

Answer 1

'Triggers are not activated by foreign key actions.'

http://dev.mysql.com/doc/refman/5.5/en/stored-program-restrictions.html

Ahhh!

Can anyone help me with a workaround?

Answer 2

For workaround follow the steps below

1. Delete (ON DELETE CASCADE) in foreign key reference

2. for replacing (ON DELETE CASCADE) add this trigger

   CREATE TRIGGER DELETE_CHILD_ROW BEFORE DELETE ON PARANT_TABLE
   FOR EACH ROW
   BEGIN
       DELETE FROM CHILD_TABLE WHERE PARANT_ID=OLD.PARANT_ID;
   END;
   

Answer 3

Yup, there is. Setup your cascade delete to set null instead and in the first level trigger delete the record with null key. That should fire delete trigger. I just ran into this and testing it now.