Replace Trigger After Delete by Trigger Instead of

Im trying to ignore deletion of employees that have a salary higher than 5000. Im using a Trigger on Employee table after delete. If the employee deleted has a salary higher 5000 a reinsert the row into the Employee table.

CREATE TRIGGER T1 ON Employee AFTER DELETE
AS
INSERT INTO Employee
SELECT * FROM DELETED D
WHERE D.salary > 5000


Is there any way of doing this without reinserting the row? How can i use INSTEAD OF instead of AFTER?

2 Answers
2

You need to create VIEW like This simple example :

CREATE TABLE Test
(
RowID integer NOT NULL,
Data integer NOT NULL,

CONSTRAINT PK_Test_RowID
PRIMARY KEY CLUSTERED (RowID)
);
GO
INSERT dbo.Test
(RowID, Data)
VALUES
(1, 100),
(2, 200),
(3, 300);
GO
CREATE TRIGGER dbo_Test_IOD
ON dbo.Test
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;

DELETE FROM dbo.Test
WHERE EXISTS
(
SELECT * FROM Deleted
WHERE Deleted.RowID = dbo.Test.RowID
);
END;
GO
DELETE dbo.Test;
GO
DROP TABLE dbo.Test;


An INSTEAD OF trigger can decide what to do instead of facing changed records as with an AFTER trigger. This is mostly used for views that are based on multiple tables and that need to be updateable. The trigger then has to direct the updates to the correct underlying tables.

In your case, you would do

CREATE TRIGGER T1 ON Employee INSTEAD OF DELETE
AS
DELETE FROM Employee
FROM Employee e
INNER JOIN deleted d ON e.EmployeeID = d.EmployeeID
WHERE e.salary <= 5000


Make sure that recursive triggers are disabled.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.