2 added 1813 characters in body
source | link

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.

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.

    Tweeted twitter.com/#!/StackDBAs/status/73712367296462848
1
source | link

Derived value not calculating as expected using combination of FK and Triggers (MySQL)

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.