15

I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.

What are my options for doing this? Is there a simple way to do a "dry run" of a DELETE?

We need to run a batch of several DELETEs in separate transactions. If one fails, the others are already committed. (Bad design from the start, I know, but it's not my application, and it's not changing.) The best workaround at the moment sounds like doing a dry check to see if the DELETEs will fail.

I'm trying to make sure they all succeed, or none of them succeed.

0

3 Answers 3

27

If your goal is to process all deletes only if they all succeed, why not just use TRY/CATCH:

BEGIN TRANSACTION;
BEGIN TRY
  DELETE #1;
  DELETE #2;
  DELETE #3;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
END CATCH

If the goal is to allow all successful deletes to succeed even if one or more will fail, then you can use individual TRY/CATCH, e.g.

BEGIN TRY
  DELETE #1;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH

BEGIN TRY
  DELETE #2;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH
0
8

One option is to begin a transaction, run your delete, and then always rollback:

begin tran

delete Table1 where col1 = 1

-- Test whether it is there
select * from Table1 where col1 = 1

rollback tran

-- Confirm that it is still there
select * from Table1 where col1 = 1
0
0

I would like to improve the solution provided by Aaron Bertrand with some code, in case you want to try to add any element of a table, managing the exceptions to ignore fails or also stop the process afters errors.

This one will select the records from the table and then tries to delete them without exceptions:

DECLARE @MaxErrors INT
SET @MaxErrors = 5;    // Setting 0 will stop process after the first error!

SELECT
    [Id]
    , ROW_NUMBER() OVER (ORDER BY Id ASC) AS [Index]
INTO #DeletingItems
FROM myTable

DECLARE @Current INT, @Max INT, @Id INT, @TotErrors INT
SELECT
    @Current = 1
    , @TotErrors = 0
    , @Max = MAX([Index])
FROM #DeletingTable

WHILE @Current <= @Max
BEGIN
    SELECT
        @Id = [Id]
    FROM #DeletingItems
    WHERE
        [Index] = @Index;

    BEGIN TRANSACTION;    
    BEGIN TRY    
        DELETE FROM myTable WHERE [Id] = @Id;

        COMMIT TRANSACTION;    
    END TRY    
    BEGIN CATCH    
        ROLLBACK TRANSACTION;

        SET @TotErrors = @TotErrors + 1;

        IF @TotErrors > @MaxErrors
            BREAK;
    END CATCH

    SET @Current = @Current + 1;
END
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.