如何取消SQL中的删除

I want to create a trigger to check what is being deleted against business rules and then cancel the deletion if needed. Any ideas?

我想创建一个触发器来检查针对业务规则删除的内容,然后根据需要取消删除。有任何想法吗?

The solution used the Instead of Delete trigger. The Rollback tran stopped the delete. I was afraid that I would have a cascade issue when I did the delete but that didn't seem to happen. Maybe a trigger cannot trigger itself.

该解决方案使用了“替代删除”触发器。 Rollback tran停止了删除。当我进行删除时,我担心会遇到级联问题,但似乎没有发生。也许触发器无法触发自身。

4 个解决方案

#1

Use an INSTEAD OF DELETE (see MSDN) trigger and decide within the trigger what you really want to do.

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

#2

The solution used the Instead of Delete trigger. The Rollback tran stopped the delete. I was afraid that I would have a cascade issue when I did the delete but that did'nt seem to happen. Maybe a trigger cannot trigger itself. Anyhow, thanks all for your help.

该解决方案使用了“替代删除”触发器。 Rollback tran停止了删除。当我进行删除时,我担心会遇到级联问题,但这似乎不会发生。也许触发器无法触发自身。无论如何,谢谢大家的帮助。

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

#3

The trigger can roll back the current transaction, which will have the effect of cancelling the deletion. As the poster above also states, you can also use an instead of trigger.

触发器可以回滚当前事务,这将取消删除。如上面的海报也说明,您也可以使用而不是触发器。

#4

According to MSDN documentation about INSTEAD OF DELETE triggers:

根据有关INSTEAD OF DELETE触发器的MSDN文档:

The deleted table sent to a DELETE trigger contains an image of the rows as they existed before the DELETE statement was issued.

发送到DELETE触发器的已删除表包含在发出DELETE语句之前存在的行的图像。

If I understand it correctly the DELETE is actually being executed. What am I missing?

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

Anyway, I don't understand why do you want to delete the records and if the business rules are not passed then undelete those records. I would have swear it should be easier to test if you pass the business rules before deleting the records.

无论如何,我不明白为什么要删除记录,如果没有传递业务规则,则取消删除这些记录。我会发誓如果你在删除记录之前传递业务规则应该更容易测试。

And I would have said use a transaction, I haven't heard before about INSTEAD OF triggers.

我会说使用一个事务,我以前没有听说过INSTEAD OF触发器。

#1