MySQL trigger for oversubscription protection

How-to enable total oversubscription check for Shift Signup (mysql trigger) :

delimiter |
CREATE TRIGGER stop_oversubscription_handler BEFORE INSERT ON Shifts 
  FOR EACH ROW BEGIN
    SET @insert_failed := "";
    SET @shifts_required := (SELECT shifts_required FROM ShiftAdmin WHERE institution_id = NEW.institution_id);
    SET @shifts_exist := (SELECT COUNT(*) FROM Shifts WHERE institution_id = NEW.institution_id);
    IF ( (@shifts_exist+1) >= (@shifts_required * 1.15)) THEN 
      SET @insert_failed := "oversubscription protection error";
      SET NEW.beginTime := null;
      SET NEW.endTime := null;
      SET NEW.week := null;
      SET NEW.shiftNumber := null;
      SET NEW.shiftTypeID := null;
      SET NEW.duplicate := null;
    END IF;
  END;
|

delimiter ;