如何创建一个触发这种情况吗?

Question

我有一个问题插入值到Class表

我想写一个触发器来防止发生"讲师在不同的class_Id在同一时间教学"。

我该怎么做?

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

这是我创建的触发器:

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 1

对Class表使用inserted和JOIN。检查表中是否存在符合您要求的行(c_DayOfClass, c_TermSeason等)

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;