I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints.
There are 3 tables involved in this arrangement
responses
------------------------
id_responses
text
n_comments
responses_has_comments
------------------------
id_responses
id_comments
comments
------------------------
id_comments
text
the link table – reviews_has_comments (rhc) exists because comments can be added to several other types of content.
A cascading foreign key constraint on rhc removes rows when a comment or response has been deleted. This is working as expected. I have written a trigger to increment/decrement n_comments on responses when a change occurs in rhc.
The incrementing trigger is working fine, but the decrementing trigger is not. Here are the triggers.
CREATE TRIGGER `inc_n_comments_in_responses`
AFTER INSERT ON `responses_has_comments` FOR EACH ROW
BEGIN
UPDATE responses
SET n_comments = n_comments + 1
WHERE id_responses = NEW.id_responses;
END;
$$
CREATE TRIGGER `dec_n_comments_in_responses`
AFTER DELETE ON `responses_has_comments` FOR EACH ROW
BEGIN
UPDATE responses
SET n_comments = n_comments - 1
WHERE id_responses = OLD.id_responses;
END;
$$
Can anyone cast their eye over this and point me in the right direction? As I understand it, the BEFORE and AFTER commands happen before and after the CASCADE.
UPDATE
OK – this is a case of RTFM (although in this instance the manual sucks….)
MySQL documentation on this matter shows that Foreign Key constraints do NOT activate triggers.
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
So – let's look at the options:
1: I can increase the scope of the delete trigger thus:
CREATE TRIGGER `dec_n_comments`
AFTER DELETE ON `comments` FOR EACH ROW
BEGIN
SELECT rshc.id_responses INTO @id_responses
FROM responses_has_comments AS rshc WHERE id_comments = OLD.id_responses
IF @id_responses IS NOT NULL THEN
UPDATE responses
SET n_comments = n_comments - 1
WHERE id_responses = @id_responses;
END IF;
-- rinse and repeat for all linked "commentable" table data...
END;
$$
My problem with this is that as the types of content to which comments can be added increases, so to will the size of this IF/ELSE (and it's bigger brother, the UPDATE trigger)
I could possibly remove the multiple IF/ELSE statements this points to
if I write a PROCEDURE to return a placeholder table name and identifier to
use in the subsequent UPDATE clause. This would require an additional field
on the comments table in order to get the relevant table type – but I'm
personally suspicious of referencing table meta data in this way…
I could possibly replace the resource/link/comment paradigm with a one to many
relationship – suffixing each type of comment with it's parent resource: i.e response_comments
Finally – I could just sack this off and keep it in the application – which at the moment
is really looking like the most likely option.
Best Answer
If I understand correctly, that is not a justification for
responses_has_comments
table - you can have several tables with 1-to-many relationships with the samecomments
table. The only reason for introducingresponses_has_comments
would be if the same comment can be attached to more than one response, as well as more than one comment being attached to each response, ie it is a many-to-many relationship - I don't think that is what you are trying to achieve.This does not answer your main question but may sidestep the problem in your updated trigger. Your
comments
trigger then becomes: