SQL Server Triggers – Instead of Delete – An example

An SQL trigger that is executed “instead of” a DML statement is known as an INSTEAD OF trigger. There is absolutely no actual insert, remove, or update activity. Instead, it carries out other commands listed in the trigger.

The DML triggers known as INSTEAD OF triggers are those that are triggered in place of triggering events like INSERT, UPDATE, or DELETE events.

In this article, you’ll know how to write an INSTEAD OF DELETE trigger.

The example below shows a situation in which a table containing all the student information ought not to be deleted because of related payment transactions. The student’s record has to be deactivated in its place.

/* Sample Table and Data */
CREATE TABLE tbStudentDetails(iStudentID INT, vcStudentName VARCHAR(50), bActive BIT)

INSERT INTO tbStudentDetails SELECT 1001, ‘Yasir’, 1
INSERT INTO tbStudentDetails SELECT 1002, ‘Samuel’, 1
INSERT INTO tbStudentDetails SELECT 1003, ‘Mike’, 1
INSERT INTO tbStudentDetails SELECT 1004, ‘Nick’, 1
INSERT INTO tbStudentDetails SELECT 1005, ‘Josh’, 1
INSERT INTO tbStudentDetails SELECT 1006, ‘Ayub’, 1
GO

/* Trigger to fire against a DELETE statement */
CREATE TRIGGER trgDeleteStudent ON tbStudentDetails
INSTEAD OF DELETE
AS
BEGIN
UPDATE a SET bActive = 0 FROM tbStudentDetails a
JOIN deleted d ON a.iStudentID = d.iStudentID
END
GO

/* Let’s try to delete a record from tbStudentDetails */
DELETE FROM tbStudentDetails WHERE iStudentID = 1002

/* Let’s see what is the output */
SELECT * FROM tbStudentDetails