I don't quite see the need to use
instead of
trigger. With that kind of trigger you need to re-implement all the logic. Wouldn't it be more convenient to use a normal trigger and to just check what has been deleted and is it acceptable.
Consider the following example
create table Users (
UserId int,
Name varchar(100)
);
insert into Users (UserId, Name) values
(101, 'A'),
(102, 'B'),
(103, 'C');
create trigger UserTrigger on Users
after delete as
begin
If Exists(SELECT 1 FROM deleted WHERE UserId = 101)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('Not Allowed to delete UserId : 101 as is a SUPER USER', 16, 1);
END;
end;
begin transaction;
delete from Users where UserId = 102;
begin transaction;
delete from Users where UserId = 101;
begin transaction;
delete from Users;
Actually there is also a more 'declarative' way of doing this. You can also define a child table with a foreign key to Users table. After that you can add the desired user id's to this child table to prevent the deletion of those id's. This solution would require no T-SQL code and maintaining the list of 'permanent' users would be more dynamic.
Consider the following
create table Users2 (
UserId int primary key,
Name varchar(100)
);
insert into Users2 (UserId, Name) values
(101, 'A'),
(102, 'B'),
(103, 'C');
create table Users2DeletePrevention (
UserId int references Users2(UserId)
);
insert into Users2DeletePrevention (UserId) values (101);
begin transaction;
delete from Users2 where UserId = 102;
begin transaction;
delete from Users2 where UserId = 101;
begin transaction;
delete from Users2;
You just need to ensure that no-one can remove the rows from the child table accidentally but that can be handled using table privileges.