Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

on delete cascade not firing trigger

Tags:

mysql

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

like image 597
user3083672 Avatar asked Jan 09 '23 15:01

user3083672


2 Answers

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

like image 132
user3083672 Avatar answered Jan 18 '23 10:01

user3083672


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;
    
like image 25
jawhar omar Avatar answered Jan 18 '23 10:01

jawhar omar