MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE team (
  `id`            BIGINT PRIMARY KEY AUTO_INCREMENT, 
  `name`          VARCHAR(255) NOT NULL,
  `member_count`  INT, 
  `member_limit`  INT NOT NULL
);
CREATE TABLE user (
  `id`            BIGINT PRIMARY KEY AUTO_INCREMENT
);
CREATE TABLE team_member (
  `id` BIGINT   PRIMARY KEY AUTO_INCREMENT,
  `team_id`     BIGINT NOT NULL,
  `member_id`   BIGINT NOT NULL,
  CONSTRAINT `fk_team_member_team_id` FOREIGN KEY (`team_id`)
    REFERENCES `team` (`id`),
  CONSTRAINT `fk_team_member_member_id` FOREIGN KEY (`member_id`)
    REFERENCES `user` (`id`)
);
ALTER TABLE team
  ADD CONSTRAINT enforce_team_member_limit
    CHECK ((member_count >= 0) AND (member_count <= member_limit))
;

Records: 0  Duplicates: 0  Warnings: 0


CREATE TRIGGER tr_ai_update_team_size
AFTER INSERT
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = COALESCE(member_count,0) + 1
      WHERE id=NEW.team_id;
END;

CREATE TRIGGER tr_au_update_time_size
AFTER UPDATE
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = COALESCE(member_count,0) + 1
      WHERE id=NEW.team_id;
    UPDATE team SET member_count = COALESCE(member_count,0) - 1
      WHERE id=OLD.team_id;
END;

CREATE tr_ad_update_team_size
AFTER DELETE
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = COALESCE(member_count,0) - 1
      WHERE id=OLD.team_id;
END;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tr_ad_update_team_size
AFTER DELETE
ON team_member
FOR EACH ROW
BEGIN
    UPDATE' at line 1


INSERT INTO team VALUES (1, 'Team 1', null, 3);
INSERT INTO team VALUES (2, 'Team 2', null, 2);

INSERT INTO user VALUES (1);
INSERT INTO user VALUES (2);
INSERT INTO user VALUES (3);
INSERT INTO user VALUES (4);

COMMIT;
INSERT INTO team_member VALUES (1, 1, 1);
INSERT INTO team_member VALUES (2, 1, 2);
INSERT INTO team_member VALUES (3, 1, 3);
SELECT * FROM team_member JOIN team ON team.id=team_member.team_id;


id
team_id
member_id
id
name
member_count
member_limit


1
1
1
1
Team 1
3
3

2
1
2
1
Team 1
3
3

3
1
3
1
Team 1
3
3


INSERT INTO team_member VALUES (4, 1, 4);

Check constraint 'enforce_team_member_limit' is violated.


DELETE FROM team_member where team_id=1 and member_id=3;
INSERT INTO team_member VALUES (4, 1, 4);

Check constraint 'enforce_team_member_limit' is violated.