How can we set a trigger for NO ACTION foreign key?

Question

I was looking for a way to set a trigger for my table that acts like cascade.

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

Now I have a trigger for deleting Student.

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;

But it sounds that there is a problem in deleting student id = 1.

Does anybody know what can I do in this situation?

I don't want to use ON DELETE CASCADE.

Your trigger code makes no sense. If you DELETE rows in Class where datediff >= 0, then there will be no rows to UPDATE where datediff > 0. And you ignore all rows where datediff < 0 - which will never work because of the foreign key. Your sample data is far too simplistic and you are not thinking of what happens over time. Without knowing your schema and goal, the only way forward is to delete/update from the "bottom up". You remove or update the Class rows before you delete the Student rows. — SMor, Jul 02 '22 at 14:36
[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, Jul 02 '22 at 14:41
In all your questions, you only describe the immediate problem. I think you would get better responses, and more useful answers, if you show some sample data and explain what problem you are trying to solve, rather than just the error you get with the implementation of your solution. This helps to prevent [xy problems](https://xyproblem.info/). Also, "it sounds that there is a problem" isn't a helpful description of an issue, the picture is much too small to read for me — HoneyBadger, Jul 02 '22 at 16:11

score 0 · Answer 1 · answered Jul 02 '22 at 18:36

0

You can't implement cascading deletes in an AFTER trigger. The trigger runs after the DELETE, and the Foreign Key prevents the DELETE.

You can use an INSTEAD OF trigger, where you would delete the child rows, then delete the target row.

answered Jul 02 '22 at 18:36

David Browne - Microsoft

80,331
6
39
67

How can we set a trigger for NO ACTION foreign key?

1 Answers1