Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

reviews_has_comments (rhc) exists because comments can be added to several other types of content.

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 same comments table. The only reason for introducing responses_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:

CREATE TRIGGER `dec_n_comments`
    AFTER DELETE ON `comments` FOR EACH ROW
    BEGIN
        UPDATE responses
        SET n_comments = n_comments - 1
        WHERE id_responses = @id_responses;
        -- rinse and repeat for all linked "commentable" table data...        
    END;
$$
share|improve this answer
    
Wouldn't the comments table then require a nullable foreign key index for each of the tables it was related to? How would you set up this relationship? Several tables "_has_comments" besides responses. I guess I could put an "abstract" name for the identifier on the comments table.. –  sunwukung May 26 '11 at 20:27
    
Yes, would add a nullable foreign key index for each of the tables it was related to if you want to stick with a single comments table. You can then also constrain the FKs so that no more than one of them can be non-null with a check constraint if you wish. –  Jack Douglas May 27 '11 at 3:06
    
Hmmm - that just doesn't sit right with me,you're just pushing complexity into the application layer - and surely it's a normalisation no-no.. –  sunwukung May 27 '11 at 8:50
    
The application layer doesn't need to know how this is modelled - make it simple with a view for each kind of comment if necessary. If you don't like nullable FKs (and I'd say there is nothing wrong with them), bite the bullet and have separate comments tables for each. –  Jack Douglas May 27 '11 at 9:14
    
Sure, it's not a criticism, more a preference. I would at this point rather push the complexity into the triggers than adopt this pattern. Thanks for taking the time to deal with the question. –  sunwukung May 27 '11 at 10:21
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.