How do I cancel a Delete in SQL

Question

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

Update 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.

Answer 1

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

Answer 2

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.

Answer 3

According to MSDN documentation about INSTEAD OF DELETE triggers:

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

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

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.

Answer 4

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.

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

Answer 5

dunno what you're using this for, but if its an application you need to be handling your business logic in your bus objects...not in a trigger....but if you have to....put the bus logic in the delete proc