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:
- The insertion of records from the RAW table into the dbo table got rolled back; BUT
- 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?
Best Answer
No, there was no
rollback
at all, and here is the repro.With
xact_abort off
that is yourdefault
set option I created 2 tables, I then open transaction end do 2inserts
one of which(the second one) fails, I addedselect @@trancount
andselect
from both tables so you can better see what happens:As you see, no
rollback
was made, only yourcommit
. You insert (-1) intodbo.t2
and this row is permanently there. This is because the erroris 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 yourcommit
that commits insert of -1 and tabletruncation
.Now the second test: uncomment
set xact_abort on
, this will makestatment terminating
only error bebatch aborting
, all thestatements
within transaction will berolled back
and execution will be interrupted as soon as the error occurs.So
t1
table will never be truncated and the insert of (-1) int2
will berolled back
.And now how your code should be written:
Your code should always set xact_abort on and it should have
try..catch
block.You should do
rollback
fromcatch
adthrow
theerror