Mysql – Using COUNT in triggers before insert into table

countinsertMySQLtrigger

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.

enter image description here

enter image description here

enter image description here

Best Answer

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.

Related Question