T-SQL如何防止删除表中的第一行

I have a table like this:

我有这样一张桌子：

CREATE TABLE Users
(

    idUser int IDENTITY(1,1) PRIMARY KEY,
firstName varchar(40) NOT NULL,
secondName varchar(40),
lastName varchar(70) NOT NULL,
position varchar(80),
section varchar(80) NOT NULL
)

And someone need to delete only one of the Users. How can I prevent first row being deleted? I can't reinsert it, first row must have idUser = 1.

有人需要只删除其中一个用户。如何防止第一行被删除？我无法重新插入它，第一行必须有idUser = 1。

3 个解决方案

#1

You can create a Trigger on table [Users] that watch when a register from this table is being deleted. So you check if deletion is userId:1 then you don't delete it, else.. you delete it, here is the code:

您可以在表[Users]上创建一个Trigger，用于在删除此表中的寄存器时进行监视。所以你检查删除是否是userId：1然后你不删除它，否则..你删除它，这是代码：

CREATE TRIGGER sampleTrigger
    ON [Users]
    INSTEAD OF DELETE
AS
    IF (SELECT idUser FROM deleted) = 1
    BEGIN
        RAISERROR ('Is not allowed to delete idUser: 1',1,1)    
    END
    ELSE
    BEGIN
        DELETE FROM [Users] WHERE idUser = (SELECT idUser FROM deleted)
    END
GO

#2

In this case you will need another "Audit" table which will keep records for each user in case if a user deletes a record that users details will get inserted in that table, and Create an Instead of delete trigger on User which checks in Audit table if that user details exits in Audit table then prevent it from further deletion of if does not exist in Audit table then Allow the Record Deletion and adds a row into Audit table for this user to prevent further deletions from this user.

在这种情况下，您将需要另一个“Audit”表，该表将保留每个用户的记录，以防用户删除将在该表中插入用户详细信息的记录，并在User表上创建一个而不是删除触发器以检查Audit表如果该用户详细信息在审计表中退出，则阻止其进一步删除如果在审计表中不存在则允许删除记录并在此表中为此用户添加一行以防止进一步删除此用户。

#3

You should put such logics in your Business(Logic) Layer.
check that the user to delete is no UID=1

您应该将这些逻辑放在您的业务（逻辑）层中。检查要删除的用户是否UID = 1