How to apply commit transaction? SQL Server 2005 Best Practices

SQLUSA

Microsoft SQL Server 2005
Database Design Best Practices

How to apply COMMIT TRANSACTION?

Execute the following script in Query Editor to create a scenario for the use of COMMIT TRANSACTION and ROLLBACK TRANSACTION.

USE AdventureWorks
GO

SELECT * from Person.ContactType
GO

-- TRY SHOULD SUCCEED
BEGIN TRY
BEGIN TRANSACTION
SET IDENTITY_INSERT Person.ContactType ON

INSERT Person.ContactType(ContactTypeID, [Name])
VALUES (1000, 'Email Support')

SET IDENTITY_INSERT Person.ContactType OFF
COMMIT TRANSACTION
PRINT 'TRANSACTION COMMITTED'
END TRY
BEGIN CATCH

ROLLBACK TRANSACTION
PRINT 'TRANSACTION ROLLED BACK'

END CATCH
GO

SELECT * from Person.ContactType
GO

-- Since this is only a test, we delete the inserted entry
DELETE Person.ContactType WHERE ContactTypeID=1000
GO

SELECT * from Person.ContactType
GO

-- TRY SHOULD FAIL DUE TO CONFLICTING INSERTS
BEGIN TRY
BEGIN TRANSACTION
SET IDENTITY_INSERT Person.ContactType ON

INSERT Person.ContactType(ContactTypeID, [Name])
VALUES (1000, 'Email Support')

SET IDENTITY_INSERT Person.ContactType OFF
COMMIT TRANSACTION
PRINT 'TRANSACTION COMMITTED'
END TRY
BEGIN CATCH

ROLLBACK TRANSACTION
PRINT 'TRANSACTION ROLLED BACK'

END CATCH
GO
SELECT * from Person.ContactType
GO

The Best SQL Server 2005 Training in the World