How to create a trigger for this situation?

I have a problem inserting values into a Class table.

I want to write a trigger to prevent happening “an instructor teaches in different class_Id at the same time”.

How can I do this?

CREATE TABLE Class
(
    Class_ID BIGINT,
    c_InstrumentID BIGINT NOT NULL,
    c_StudentID BIGINT,
    c_InstructorID BIGINT NOT NULL,
    c_InstituteId BIGINT NOT NULL,
    c_TermSeason NVARCHAR(10),
    c_TermYear INT,
    c_TimeOfClass TIME NOT NULL,
    c_DayOfClass NVARCHAR(30),
    c_Eligibility INT,
    c_RemainingSession INT,

    CONSTRAINT cons_Season 
        CHECK(c_TermSeason IN ('Spring', 'Summer', 'Fall', 'Winter')),
    CONSTRAINT cons_TimeClass 
        CHECK(c_TimeOfClass BETWEEN '08:30:00' AND '20:30:00'),
    CONSTRAINT cons_RemainSession 
        CHECK (c_RemainingSession BETWEEN 0 AND 12),

    FOREIGN KEY(c_InstrumentID) 
        REFERENCES Instrument(Instrument_ID) ON DELETE NO ACTION,
    FOREIGN KEY(c_StudentID) 
        REFERENCES Student(Student_ID) ON DELETE NO ACTION,
    FOREIGN KEY(c_InstructorID) 
        REFERENCES Instructor(Instructor_ID) ON DELETE NO ACTION,
    FOREIGN KEY(c_InstituteId) 
        REFERENCES Institute(Institute_ID) ON DELETE NO ACTION,

    PRIMARY KEY (Class_ID)
)

This is the trigger which I’ve created:

CREATE OR ALTER TRIGGER One_InstructorDuplicate 
ON Class
AFTER INSERT
AS
BEGIN
    IF (NOT EXISTS (SELECT * 
                    FROM Class C, ((SELECT * FROM CLASS) 
                                   EXCEPT (SELECT * FROM inserted)) AS newC 
                    WHERE newC.c_InstructorID = C.c_InstructorID  
                      AND newC.c_DayOfClass != C.c_DayOfClass 
                      AND newC.c_TermSeason != C.c_TermSeason 
                      AND newC.c_TermYear != C.c_TermYear 
                      AND newC.c_TimeOfClass != C.c_TimeOfClass))
        ROLLBACK TRAN
END;

Answer

Use inserted and JOIN to the Class table. Check for existence of rows in table that matches your requirement (c_DayOfClass, c_TermSeason etc)

CREATE OR ALTER TRIGGER One_InstructorDuplicate 
ON Class
AFTER INSERT
AS
BEGIN
    IF EXISTS 
       (
           SELECT * 
           FROM   inserted i
                  INNER JOIN Class c ON i.c_InstructorID = c.c_InstructorID
           WHERE  i.Class_ID      <> c.Class_ID
           AND    i.c_DayOfClass  =  c.c_DayOfClass 
           AND    i.c_TermSeason  =  c.c_TermSeason 
           AND    i.c_TermYear    =  c.c_TermYear 
           AND    i.c_TimeOfClass =  c.c_TimeOfClass
       )
   BEGIN
        ROLLBACK TRAN
   END
END;