CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqltransactions
Sunny
Sunny

Reputation: 4809

SQL Transaction not working

I am using below SQL to delete records and insert into Customers table inside a transaction. If there is an error in the insert statements, I am seeing the error message and when I try to execute select * from customers, it is not displaying result set. And when I close SSMS window, it is showing There are uncommitted transactions. Do you wish to commit these transactions before closing the window?

After I click OK, results are getting displayed from the table. So, is there any locking mechanism taking place while using transaction.

USE CMSDB;
BEGIN TRY
    BEGIN TRAN t1;

        DELETE FROM Customers
        print @@trancount -->prints 3 since there are three records
        INSERT INTO CUSTOMERS
        INSERT INTO CUSTOMERd --> error here
        INSERT INTO CUSTOMERS

    COMMIT TRAN t1;
END TRY
BEGIN CATCH 
    print 'hi' --> not printing
    select @@trancount --> not resulting anything
    IF @@TRANCOUNT > 0 
        ROLLBACK TRAN t1;
    -- Error Message
    DECLARE @Err nvarchar(1000)
    SET @Err = ERROR_MESSAGE()
    RAISERROR (@Err,16,1)
END CATCH
GO

Message

(3 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 208, Level 16, State 1, Line 8
Invalid object name 'dbo.Customerd'.

Upvotes: 3

Views: 4967

Answers (1)

i-one
i-one

Reputation: 5120

Excerpt from TRY…CATCH description:

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  • Compile errors, such as syntax errors, that prevent a batch from running.

  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

In this case what happens is

The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.

Upvotes: 2

Related Questions