Prevent Row Deletions/Updates on SQL Server

If you reach a requirements to prevent some table for deletions or/and updates you may wonder how to reach that. The simple answer is to use INSTEAD OF  triggers. INSTEAD OF triggers override the standard action of triggering statement: INSERT, UPDATE or DELETE. On that way, INSTEAD OF trigger can ignore parts of the batch, not process part of the batch or taking an alternative action. The major difference to the AFTER triggers is that INSTEAD OF triggers can be defined on a views as well as on a tables.

So, a solution of the problem, preventing a row deletions on the table can be the following INSTEAD OF trigger:

CREATE TRIGGER trgPreventRowDeletion ON Test_table INSTEAD OF DELETE
AS
BEGIN
REISEERROR ('Deletions are not allowed from the Test_table', 16, 1)
END

On a similar way it can be defined INSTEAD OF INSERT and INSTEAD OF UPDATE triggers.