Rolling back truncate table

Question

I have a stored procedure that does the following:

BEGIN TRANSACTION

    -- Code to delete updated records from production (dbo) table
    DELETE FROM [dbo].[factMyTable]
    WHERE exists (SELECT *
        FROM [RAW].[MyTable]
        WHERE [RAW].[MyTable].[refno] = [dbo].[factMyTable].[refno]
        AND [RAW].[MyTable].[modification_dttm] >= [dbo].[factMyTable].[modification_dttm]
        )

    -- Code to perform the append of incremental records 
    INSERT INTO [dbo].[factMyTable]
    SELECT
         [refno]
        ,[field1]
        ,[field2]
        ,[field3]
        ,[FieldN]
        ,[modification_dttm]
    FROM [RAW].[MyTable]

-- Truncate stage table and get ready for next load
TRUNCATE TABLE [RAW].[MyTable]

COMMIT TRANSACTION

As you can see above, I have a truncate command that is contained within a BEGIN/COMMIT transaction block. However I got an error when executing this stored procedure at the insert command, where a field set as NOT NULL was receiving a NULL value. As a result:

1. The insertion of records from the RAW table into the dbo table got rolled back; BUT
2. The truncation of the RAW table did not roll back.

The idea is that if there is an error with the insertion of data, the truncation shouldn't happen.

According to this article, we can roll back the truncate command, but perhaps my stored procedure is not scripted correctly. Perhaps there is a more direct way of ensuring that the truncation only happens if the insertion returns no errors? How would I go about it?

Answer 1

1. The insertion of records from the RAW table into the dbo table got rolled back; BUT
2. The truncation of the RAW table did not roll back.

No, there was no rollback at all, and here is the repro.

With xact_abort off that is your default set option I created 2 tables, I then open transaction end do 2 inserts one of which(the second one) fails, I added select @@trancount and select from both tables so you can better see what happens:

--set xact_abort on

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go


create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;

select @@trancount as [@@trancount after commit];

select *
from dbo.t1;

select *
from dbo.t2;

As you see, no rollback was made, only your commit. You insert (-1) into dbo.t2 and this row is permanently there. This is because the error

Msg 515, Level 16, State 2, Line 18 Cannot insert the value NULL into column 'col1', table 'dbo.t2'; column does not allow nulls.

is statement terminating only. The second statement fails so no row were inserted, but insert of (-1) was not rollbacked, and as you see after the error your transaction is still open. It's your commit that commits insert of -1 and table truncation.

---

Now the second test: uncomment set xact_abort on, this will make statment terminating only error be batch aborting, all the statements within transaction will be rolled back and execution will be interrupted as soon as the error occurs.

So t1 table will never be truncated and the insert of (-1) in t2 will be rolled back.

And now how your code should be written:

set xact_abort on;

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin try
begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;
end try

begin catch
    select @@trancount as [@@trancount in catch before rollback];
    if @@rowcount > 0 rollback;
    throw;
end catch;

Your code should always set xact_abort on and it should have try..catch block.

You should do rollback from catch ad throw the error

Answer 2

The reference from David is wonderful, but if you are looking for a practical example of what you should be doing, here is a block for you. I've not duplicated your example precisely, since you can see the issues without it.

I build up a sample table and pop some data in there. I intentionally am trying to insert a NULL into the dbo.Fact table, which isn't allowed.

I then wrap my statements in a TRY/CATCH block. In the Catch block I have added a rollback command as well as a throw so the original error will go back to the client. If you omit the throw then no error will go back.

If you want to see your original behavior then you need to remove the TRY/CATCH and just have the BEGIN TRANSACTION and COMMIT in there. Since even with the try/catch SQL properly rolls back the transaction.

/** Build up our sample tables and some data.
    **/ 

DROP TABLE IF EXISTS dbo.RawTable;
DROP TABLE IF EXISTS dbo.Fact;

CREATE TABLE dbo.RawTable
    (
    RefNo CHAR(10) NOT NULL
    , field1 CHAR(10) NULL
    , field2 CHAR(10) NULL
    );

CREATE TABLE dbo.Fact
    (
    RefNo CHAR(10) NOT NULL
    , field1 CHAR(10) NOT NULL
    , field2 CHAR(10) NOT NULL
    );

INSERT INTO dbo.RawTable 
    (RefNo, field1, field2)
VALUES ('T1', NULL, 'T1F2')
    , ('T2', 'T2F1', 'T1F2');

SELECT RefNo, field1, field2 FROM dbo.RawTable; 

GO

BEGIN TRY

    BEGIN TRANSACTION;

    INSERT INTO dbo.Fact 
    (RefNo, field1, field2)
    SELECT RefNo, field1, field2
    FROM dbo.RawTable AS T;

    TRUNCATE TABLE dbo.RawTable;

    COMMIT;

END TRY
BEGIN CATCH

    --If we are here and there are open transactions, then rollback.
    IF @@TRANCOUNT >= 1
    BEGIN
        ROLLBACK;
    END

    --We want the error to bubble back to the client so they know something went wrong.
    ;THROW;

END CATCH

GO

--Show some data.
SELECT RefNo, field1, field2 FROM dbo.Fact; 
SELECT RefNo, field1, field2 FROM dbo.RawTable;