I need to know if the T-SQL below is correct to roll back if something bad happens.
I first create a temp table
IF OBJECT_ID(N'tempdb..#LocalSummary') IS NOT NULL
DROP TABLE #LocalSummary
CREATE TABLE #LocalSummary (
then I populate the table
INSERT INTO #LocalSummary
SELECT *....
Then I start my try catch
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM LocalSummaryRealTable
INSERT INTO LocalSummaryRealTable
SELECT S.*
FROM #LocalSummary AS S
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
...
I noticed when I did the insert into the LocalSummaryRealTable
the columns didn't line up and I got an error. I expected things to roll back but in SSMS query window it gave me a message that the trans wasn't committed and asked me if I wanted to commit it. Why would this ask me when it should of been rolled back?
Best Answer
You want to do the
BEGIN TRANSACTION
prior to theBEGIN TRY
:Also, please schema-qualify references to database objects such as tables, views, stored procedures etc. Also, you should get in the habit of adding semi-colons to the end of each statement.
SET XACT_ABORT ON;
is not necessary if you are explicitly rolling back the transaction inside theCATCH
block.