mirror bugzilla milesones into a custom field cf_support_milestone.

triggers.sql

-- mirror the bugzilla milestones in another picklist
-- developers have their own target milestones (the default picklist for milestones)
-- support engineers have their own support milestones which is a custom field.
-- milestones -> cf_support_milestone

delimiter //

CREATE TRIGGER cf_milestones_insert
AFTER INSERT ON milestones
FOR EACH ROW
BEGIN
    -- only want to mirror milestones of product 8
    IF NEW.product_id = 8 THEN
        -- support milestones.
        INSERT IGNORE INTO cf_support_milestone(value,sortkey,isactive)
        VALUES (value,sortkey,isactive);
    END IF;
END;
//


CREATE TRIGGER cf_milestones_update
AFTER UPDATE ON milestones
FOR EACH ROW
BEGIN
    -- only want to mirror milestones of product 8
    IF OLD.product_id = 8 THEN
        -- support milestones.
        INSERT IGNORE INTO cf_support_milestone(value,sortkey,isactive)
        VALUES (OLD.value,NEW.sortkey,NEW.isactive);
        UPDATE IGNORE cf_support_milestone
        SET value = NEW.value, sortkey = NEW.sortkey, isactive = NEW.isactive
        WHERE value = OLD.value;
    END IF;
END;
//

CREATE TRIGGER cf_milestones_delete
AFTER DELETE ON milestones
FOR EACH ROW
BEGIN
    -- only want to mirror milestones of product 8
    IF OLD.product_id = 8 THEN
        -- support milestones.
        DELETE IGNORE FROM cf_support_milestone
        WHERE value = OLD.value;
    END IF;
END;
//

delimiter ;

update milestones set product_id = product_id ;