防止删除特定记录

发布于 2024-09-29 01:15:32 字数 474 浏览 1 评论 0原文

我想防止删除特定记录。此触发器适用于该特定记录。但是，其他记录在删除时仍然保留。为什么？

 ALTER TRIGGER [Globalization].[CountriesTracker] 
 ON [Globalization].[Countries] 
 INSTEAD OF DELETE
 AS 
 BEGIN
SET NOCOUNT ON;
IF ((Select COUNT(*) from [Deleted]
     Where [Deleted].[CountryId] = '36bd1536-fb56-4ec4-957e-1b3afde16c56') = 1)
BEGIN       
    RAISERROR('You can not delete this specific record!', 0, 0)
    ROLLBACK TRANSACTION
    RETURN
END
END

如何确保不符合上述条件的行按预期被删除？

原文

I want to prevent a specific record from being deleted. This trigger works fine for that specific record. However, other records still remain when they're being deleted. Why?

 ALTER TRIGGER [Globalization].[CountriesTracker] 
 ON [Globalization].[Countries] 
 INSTEAD OF DELETE
 AS 
 BEGIN
SET NOCOUNT ON;
IF ((Select COUNT(*) from [Deleted]
     Where [Deleted].[CountryId] = '36bd1536-fb56-4ec4-957e-1b3afde16c56') = 1)
BEGIN       
    RAISERROR('You can not delete this specific record!', 0, 0)
    ROLLBACK TRANSACTION
    RETURN
END
END

How can I ensure that rows not matching the above condition are being deleted as expected?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江挽川 2024-10-06 01:15:32

您有一个 INSTEAD OF 触发器，因此您需要在其中进行实际的 DELETE。

我还考虑简单地过滤掉受保护的行，因为：

您需要抛出错误吗？还是默默无视？
包含受保护行的多行删除怎么样：中止整个行，或删除其余行？

像这样的东西：

ALTER TRIGGER [Globalization].[CountriesTracker]  ON [Globalization].[Countries] 
 INSTEAD OF DELETE
 AS 
SET NOCOUNT ON;

DELETE
   CT
FROM
   [Globalization].[Countries] C
   JOIN
   DELETED D ON C.CountryId = D.CountryId
WHERE
    [Deleted].[CountryId] <> '36bd1536-fb56-4ec4-957e-1b3afde16c56'
 GO

You have an INSTEAD OF trigger so you need an actual DELETE in it.

I'd also consider simply filtering the protected row out because:

Do you need an error throwing? Or silently ignore?
What about multi row deletes that contain the protected row: abort the whole, or delete the rest?

Something like:

ALTER TRIGGER [Globalization].[CountriesTracker]  ON [Globalization].[Countries] 
 INSTEAD OF DELETE
 AS 
SET NOCOUNT ON;

DELETE
   CT
FROM
   [Globalization].[Countries] C
   JOIN
   DELETED D ON C.CountryId = D.CountryId
WHERE
    [Deleted].[CountryId] <> '36bd1536-fb56-4ec4-957e-1b3afde16c56'
 GO

回复收藏 0 原文