Auto Closure Trigger

auto_closure.sql

CREATE TRIGGER populate_management_relation_closures
    AFTER INSERT ON management_relations
  FOR EACH ROW
  BEGIN
    INSERT INTO management_relation_closures(manager_id, reportee_id, depth) 
      -- Insert the actual manager relation
      SELECT NEW.manager_id, NEW.reportee_id, 1
      -- Make sure all of the manager's managers know of the new reportee
      UNION
      SELECT manager_id, NEW.reportee_id, depth + 1 FROM management_relation_closures
        WHERE reportee_id = NEW.manager_id
      -- Make sure all of the reportee's reportees know of the managers
      UNION
      SELECT NEW.manager_id, reportee_id, depth + 1 FROM management_relation_closures
        WHERE manager_id = NEW.reportee_id
      UNION
      SELECT b.manager_id, a.reportee_id, a.depth + b.depth + 1 
        FROM management_relation_closures AS a 
          CROSS JOIN (SELECT * FROM management_relation_closures WHERE reportee_id = 8) AS b
        WHERE a.manager_id = NEW.reportee_id;
  END;