Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software 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')

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