I have 3 tables important to my problem here. Debates consists in several Arguments, which is a child of Posts. When I delete a Debate, I want to also delete related arguments, as well as the corresponding posts.
The first thing I tried is to create a cascade delete for the arguments, but this does not delete the posts. Indeed, this would require a 'reverse' cascade delete as the post is the parent of the argument. As suggested in Is there any "reverse" ON DELETE CASCADE option?, I then tried to add a trigger on the deletion of arguments to also delete posts, but this question (on delete cascade not firing trigger) showed MySQL 'Triggers are not activated by foreign key actions.'
So I wanted to solve this problem by creating a trigger that does exactly what I explained in the first paragraph.
CREATE TRIGGER argument_delete BEFORE DELETE on debates
FOR EACH ROW
BEGIN
DELETE FROM posts
WHERE posts.id IN (SELECT arguments.id FROM arguments WHERE arguments.debate_id = debates.id);
END
As the arguments are cascade deleted when posts are deleted, this should work.
However, this trigger does not seem to work when a debate is deleted. Either I get the error saying there is a foreign key constraint violation if I remove the cascade delete between debates and arguments, or I get only the debate and the arguments deleted (not the posts) when I keep the previously cited cascade delete.
Does someone have an idea on how to do this?
Edit:
I actually based the delete part of the trigger on this question: Delete all rows in a table based on another table
