On gists

Triggers example

MySql

triggers-example.sql Raw #

CREATE TRIGGER trRaiseTopicCountAll
AFTER INSERT ON forum_commentary
FOR EACH ROW 
BEGIN    
    UPDATE forum_topic 
    SET forum_commentary_count_all = @forum_commentary_count_all +1 
    WHERE forum_topic_id = NEW.forum_topic_id;
    IF (NEW.commentary_parent_id IS NULL) 
        THEN 
            UPDATE forum_topic 
            SET first_commentary_id = NEW.forum_commentary_id 
            WHERE forum_topic_id = NEW.forum_topic_id;
    END IF; 
END;
    
    
CREATE TRIGGER trRaiseTopicCountVisible
AFTER UPDATE ON forum_commentary
FOR EACH ROW 
BEGIN    
    IF (OLD.forum_commentary_status_id <> NEW.forum_commentary_status_id)
        THEN 
            IF (NEW.forum_commentary_status_id = 2) 
            THEN 
                UPDATE forum_topic 
                SET forum_commentary_count_visible = @forum_commentary_count_visible +1 
                WHERE forum_topic_id = NEW.forum_topic_id;
            ELSE 
                UPDATE forum_topic 
                SET forum_commentary_count_visible = @forum_commentary_count_visible -1 
                WHERE forum_topic_id = NEW.forum_topic_id;
            END IF;    
    END IF;
END;