我们如何为没有动作外键设置触发器？

Question

我一直在寻找一种为桌面扳机设置扳机的方法。

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,
--StartSession and FinalSession in Add Column
c_TimeOfClass TIME NOT NULL,-- in add constraint
c_DayOfClass NVARCHAR(30),-- in add constraint
c_Eligibility INT,-- in add constraint 1 or 0 date current > finalsession => 0
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 -1 AND 13),--Update CONSTRAINT,
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 update_DeleteStudent on Student
AFTER DELETE
AS
BEGIN
    DELETE Class
    FROM Class C INNER JOIN deleted d
    ON C.c_StudentID = d.Student_ID
    WHERE DATEDIFF(WEEK, C.Final_Session, GETDATE()) >= 0
    UPDATE Class 
    SET c_StudentID = NULL
    WHERE c_StudentId IN (SELECT Student_ID FROM deleted) AND DATEDIFF(WEEK, GETDATE(), Final_Session) > 0
    END;

但是听起来删除Student ID = 1时存在问题。

有人知道在这种情况下我该怎么办？

我不想在删除CASCADE上使用。

Answer 1

您无法在后触发器中实现级联删除。触发器在删除后运行，并且外键可防止删除。

您可以使用一个代替触发器，在此删除子行，然后删除目标行。