1

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

1

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.

|improve this answer|||||
0

You could in theory, but MySQL doesn't allow the trigger to query the same table. In other words you should create a trigger for table members_has_class, and the trigger should run a SELECT on members_has_class, but this is not possible, you would get an error.

An alternative is to add a column to class table, which contains the number of students. The initial value should be 0, then you could update that value with a trigger BEFORE INSERT on the table members_has_class. There should also be a trigger BEFORE DELETE and - even if apparently it doesn't make sense - it's better to have a trigger BEFORE UPDATE, which checks if you are changing the class of a member.

|improve this answer|||||
  • Thanks for the reply. I am still struggling to implement your solution – Anon3713 Mar 3 '18 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.