Wednesday, October 30, 2013

How do I cancel a Delete in MSSQL SERVER

We use instead of delete trigger's ROLLBACK TRAN to cancel a delete statement CREATE TRIGGER dbo.t__protect__locked__from__delete
ON dbo.table
instead OF DELETE
AS
  BEGIN
      SET nocount ON

      DECLARE @IsLocked BIT,
              @ID       INT

      SELECT @IsLocked = IsLocked,@ID = ID
      FROM   deleted

      IF @IsLocked = 1
        BEGIN
            RAISERROR ('Cannot delete',16,1)
            ROLLBACK TRAN
            RETURN
        END
      -- Go ahead and do the update or some other business rules here
      ELSE
        DELETE FROM table
        WHERE  ( ID = @ID )
  END
GO