How to lock a record to prevent delete

Question

0.00/5 (No votes)

See more:

i have a database in sql server called travel. In the database is a table called users. In the users table a column userid is mark as a primary key. I want to lock a record in the users table with the userid of 101. Am trying to do that with a trigger. But is not working.

What I have tried:

ALTER TRIGGER [dbo].[UserTrigger]
ON [dbo].[Users]
INSTEAD OF DELETE
AS
IF exists (Select UserId From Users Where UserId = 101)
BEGIN
RAISERROR ('Not Allowed to delete UserId : 1 as is a SUPER USER',16,1)
END
ELSE
BEGIN
Delete From Users Where UserId in (UserId)
END

Posted 18-Feb-20 3:36am

MaximusDebois

Updated 18-Feb-20 5:10am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2020-02-18T04:08:00

You're raising the error when someone tries to delete any user record, so long as the Users table contains the specified record.

You should only be raising the error if someone tries to delete the specified record.

But it's a good job your test fails; your Else block is set to delete all users! You're telling SQL to delete users whose UserId is equal to their UserId, which is obviously all of them.

Use the deleted virtual table to check which records are going to be deleted:
Use the inserted and deleted Tables - SQL Server | Microsoft Docs[^]

SQL

ALTER TRIGGER [dbo].[UserTrigger]
ON [dbo].[Users]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    If Exists(SELECT 1 FROM deleted WHERE UserId = 101)
    BEGIN
        RAISERROR('Not Allowed to delete UserId : 101 as is a SUPER USER', 16, 1);
        ROLLBACK TRANSACTION;
        Return;
    END;
    
    DELETE
    FROM
        Users
    WHERE
        UserId In (SELECT UserId FROM deleted)
    ;
END

Wendelius · Answer 2 · 2020-02-18T05:11:00

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

SQL

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; -- Successful

begin transaction;
delete from Users where UserId = 101; -- Raises error

begin transaction;
delete from Users;                    -- Raises error

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

SQL

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; -- Successful

begin transaction;
delete from Users2 where UserId = 101; -- Raises a foreign key error

begin transaction;
delete from Users2;                    -- Raises a foreign key error

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.

How to lock a record to prevent delete

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0