Cacher is the code snippet organizer for pro developers

DELIMITER |
CREATE TRIGGER insert_strategicplan_data
AFTER INSERT ON strategic_plan_structures
FOR EACH ROW BEGIN
    INSERT INTO strategic_plan_structure_trees (parent_id, child_id, depth, path)
    VALUES (NEW.id, NEW.id, 0, CONCAT(NEW.name,"/"));

    INSERT INTO strategic_plan_structure_trees (parent_id, child_id, depth, path)
        SELECT parent_id, NEW.id, depth + 1, CONCAT(path, NEW.name, "/")
        FROM strategic_plan_structure_trees
        WHERE child_id = NEW.parent_id;
END; |
DELIMITER ;

BEGIN
    INSERT INTO strategic_plan_structure_trees (parent_id, child_id, depth, path, sequence_path)
    VALUES (NEW.id, NEW.id, 0, CONCAT(NEW.name,"/"), CONCAT(LPAD(CONCAT(NEW.id,'00'),12,'0'),"/ ")); 

    INSERT INTO strategic_plan_structure_trees (parent_id, child_id, depth, path, sequence_path)
        SELECT parent_id, NEW.id, depth + 1, CONCAT(path, NEW.name, "/"), CONCAT(sequence_path, CONCAT(LPAD(CONCAT(NEW.id,'00'),12,'0'),"/ "))
        FROM strategic_plan_structure_trees
        WHERE child_id = NEW.parent_id;
END