on delete cascade not firing trigger

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

‘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?

Leave a Reply

Your email address will not be published. Required fields are marked *