开发者

preventing a specific record from being deleted

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 ensu开发者_如何学Gore that rows not matching the above condition are being deleted as expected?


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


Because this is INSTEAD OF you still need to perform the delete operation for the default case.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜