DROP TRIGGER IF EXISTS membership_defination_protect_name;

DELIMITER $$

CREATE TRIGGER membership_defination_protect_name
BEFORE UPDATE ON membership_defination
FOR EACH ROW
BEGIN
  IF NEW.membership_name <> OLD.membership_name THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'membership_defination.membership_name is immutable: renaming orphans linked members. Insert a new tier and migrate members.membership instead.';
  END IF;
END$$

DELIMITER ;

DROP TRIGGER IF EXISTS membership_defination_protect_delete;

DELIMITER $$

CREATE TRIGGER membership_defination_protect_delete
BEFORE DELETE ON membership_defination
FOR EACH ROW
BEGIN
  DECLARE linkedMembers INT DEFAULT 0;
  SELECT COUNT(*) INTO linkedMembers FROM members WHERE membership = OLD.membership_name;
  IF linkedMembers > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Cannot delete membership_defination row: members are still linked to this tier. Reassign or remove those members first.';
  END IF;
END$$

DELIMITER ;
