Transact-SQL Implicit Transactions
DB-Library applications and Transact-SQL scripts use the Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement to start implicit transaction mode. Use the SET IMPLICIT_TRANSACTIONS OFF statement to turn implicit transaction mode off. Use the COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statements to end each transaction.
SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT OFF
GO
USE pubs
GO
CREATE TABLE ImplicitTran (Cola int PRIMARY KEY,
Colb char(3) NOT NULL)
GO
SET IMPLICIT_TRANSACTIONS ON
GO
/* First implicit transaction started by an INSERT statement */
INSERT INTO ImplicitTran VALUES (1, 'aaa')
GO
INSERT INTO ImplicitTran VALUES (2, 'bbb')
GO
/* Commit first transaction */
COMMIT TRANSACTION
GO
/* Second implicit transaction started by a SELECT statement */
SELECT COUNT(*) FROM ImplicitTran
GO
INSERT INTO ImplicitTran VALUES (3, 'ccc')
GO
SELECT * FROM ImplicitTran
GO
/* Commit second transaction */
COMMIT TRANSACTION
GO
SET IMPLICIT_TRANSACTIONS OFF
GO