How do I cancel a Delete in 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?

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.

From stackoverflow

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

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

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

  Leo Moore : The problem is that the application allows the user to delete something assigned to them but since I don't have the source code I can only stop it at the DB level. I don't want users deleting tasks which have been assigned to them which are not completed.

• 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
  

• 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