如何取消SQL中的删除操作

使用一个 INSTEAD OF DELETE (见 MSDN )在触发器内触发并决定您真正想做什么。

解决方案使用了INSTEADOFDELETE触发器。回滚事务停止了删除。我担心删除时会出现级联问题,但这似乎没有发生。也许触发器不能自己触发。不管怎样,谢谢大家的帮助。

ALTER TRIGGER [dbo].[tr_ValidateDeleteForAssignedCalls]
on [dbo].[CAL]
   INSTEAD OF DELETE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @RecType VARCHAR(1)
    DECLARE @UserID VARCHAR(8)
    DECLARE @CreateBy VARCHAR(8)
    DECLARE @RecID VARCHAR(20)

    SELECT @RecType =(SELECT RecType FROM DELETED)
    SELECT @UserID =(SELECT UserID FROM DELETED)
    SELECT @CreateBy =(SELECT CreateBy FROM DELETED)
    SELECT @RecID =(SELECT RecID FROM DELETED)

     -- Check to see if the type is a Call and the item was created by a different user
    IF @RECTYPE = 'C' and not (@USERID=@CREATEBY)

    BEGIN
        RAISERROR ('Cannot delete call.', 16, 1)
        ROLLBACK TRAN
        RETURN
    END

     -- Go ahead and do the update or some other business rules here
    ELSE
        Delete from CAL where RecID = @RecID    

END

触发器可以回滚当前事务,这将具有取消删除的效果。正如上面的海报所述,您还可以使用一个代替触发器。

根据有关 INSTEAD OF DELETE 触发器:

已删除的表已发送到删除 触发器包含行的图像 因为它们在删除之前就存在了 声明已发布。

如果我理解正确,删除操作实际上正在执行。我错过了什么?

不管怎样,我不明白您为什么要删除这些记录,如果业务规则没有通过,那么就取消删除这些记录。我发誓在删除记录之前,如果您通过了业务规则,那么应该更容易测试。

我本来会说用交易,我以前没听说过 INSTEAD OF 触发器。