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? This is the trigger which I've created: Answer Use inserted and JOIN to the Class table. Check for existence of rows in table that matches your

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)
)

​x
 
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;

 
CREATE OR ALTER TRIGGER One_InstructorDuplicate ON ClassAFTER INSERTASBEGIN    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 TRANEND;​

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;

 
CREATE OR ALTER TRIGGER One_InstructorDuplicate ON ClassAFTER INSERTASBEGIN    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   ENDEND;​

Advertisement

Answer