SQL SERVER - Trigger to restrict over 3 records

Question

I am a beginner on SQL and need help setting a Trigger

I need to set a trigger so that if 1 tutor has over 3 different schedule ID's this is not allowed!!

Table

TutorID/ScheduleID/Student
1       1           Tom 
1       1           Harry   
1       1           Lima    
1       2           Zany    
1       2           Logan   
1       3           Zoe
1       3           Lana

Tutor ID/ Tutor Name 1 Sam

how can I restrict someone from inserting another class?

Answer 1

DISCLAIMER: To be completely honest I'm not convinced doing this through a SQL Trigger is the best solution, far better to control this sort of thing through your business layer.

That said you could use a trigger to prevent the insertion:

CREATE TRIGGER MyTrigger ON dbo.ClassTable
AFTER INSERT
AS

IF EXISTS (SELECT TutorId, COUNT(ScheduleId) 
           FROM db.ClassTable
           GROUP BY TutorID
           HAVING COUNT(ScheduleId) > 3)
BEGIN
    ROLLBACK
    RAISERROR ('Too many classes scheduled!', 16, 1);
END
GO

Or if you prefer you can do an INSTEAD OF trigger and just silently discard the pesky extra row :)

CREATE OR ALTER TRIGGER MyTrigger ON dbo.ClassTable
INSTEAD OF INSERT
 AS
    BEGIN
    SET NOCOUNT ON;
    INSERT dbo.ClassTable(TutorId, ScheduleId, Student)
        SELECT TutorId, ScheduleId, Student
        FROM inserted
        WHERE TutorId IN (SELECT TutorId, COUNT(ScheduleId) FROM db.ClassTable WHERE COUNT(ScheduleId) < 3 GROUP BY TutorID);
    END
GO