Using COUNT in triggers before insert into table I am new to MySQL and am struggling with triggers. Say I have a table called members with the following data


MemberID(PK) (INT) | Name (VARCHAR)|
1                  | Barry         |


and another table called class with the following data


ClassID(PK) (INT) | ClassType(VARCHAR) | ClassSize(INT)
1                 | Boxing             | 20


I also have a link entity table called members_has_class with the following data


Members_MemberID(FK) (INT) | Class_ClassID (FK) (INT)|
1                          | 1                       |


I'd like to make a trigger that checks the class is not full when a user likes to sign up to a class where the information entered (Member ID and Class ID) is inserted into members_has_class table.

So for example, if boxing has  a class size of 20, the trigger checks that there are not already 20 people signed up to it. So if there are not 20 people already in the members_has_class they can sign up, if the class is full it does not allow sign up. I was thinking I would have to count how many of the same class id values there are and compare if it is bigger and smaller than the class size.
I suggest avoiding TRIGGERs.

BEGIN;
SELECT COUNT(*) ... FOR UPDATE;
if there is still room
    INSERT ... to add student to class
else
    give error
COMMIT;


That is, write application code perform the logic.  (TRIGGERs and FOREIGN KEYs have limited capabilities.)

Put it into a transaction to prevent another student from sneaking in while you are checking availability.  Note also the FOR UPDATE.

The above transaction could be put in a STORED PROCEDURE, but I suggest you do it in application code, rather than also taking on the potential challenges of SPs.