sql - 防止使用触发器删除单行

Question

我有这张表:

CREATE TABLE Workspaces (
AreaNr CHAR(2)
    CONSTRAINT ck_a_areanr REFERENCES Areas(AreaNr)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
SpaceNr INT
    CONSTRAINT ck_spacenr CHECK (SpaceNr > 0 AND SpaceNr < 1001),
Area DECIMAL(2,1)
    CONSTRAINT ck_areal CHECK (area > 0),
CONSTRAINT ck_workspaces PRIMARY KEY (AreaNr, SpaceNr)
)

现在我想创建一个触发器来防止从表 Workspaces 中删除单行(随机选择)。目前我有以下触发器，但这个触发器仍然允许删除单行。

当前触发器:

CREATE TRIGGER deleteWorkspace ON Workspaces
FOR DELETE AS

BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;

IF @Count >= (SELECT SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('Workspaces') AND index_id = 1
)

BEGIN
RAISERROR('You cannot delete all rows from Workspaces!',16,1)
ROLLBACK TRANSACTION
RETURN;
END

END
GO

期望的结果:我希望能够防止对上表中的单行进行删除，如果有人可以帮助我更改上面的触发器以便修复此问题，我将不胜感激。有谁知道如何做到这一点？

Answer 1

在此实例中，您需要使用已删除的虚拟表。我不完全确定为什么您只关心单个 行删除，但代码如下:

CREATE TRIGGER deleteWorkspace ON Workspaces
FOR DELETE AS

IF(SELECT COUNT(*) FROM deleted) = 1
BEGIN
RAISERROR('Single Delete Not Allowed!', 16, 1)
ROLLBACK
END
ELSE
RAISERROR('Multiple Rows Being Deleted, Allowed!', 16, 1)
BEGIN
END

END
GO

-肖恩