SET IMPLICIT_TRANSACTIONS

Sets implicit transaction mode for the connection.

Syntax

SET IMPLICIT_TRANSACTIONS { ON | OFF }

Remarks

When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.

When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:

If the connection is already in an open transaction, the statements do not start a new transaction.

Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.

Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.

The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver automatically set IMPLICIT_TRANSACTIONS to OFF when connecting. SET IMPLICIT_TRANSACTIONS defaults to OFF for connections from DB-Library applications.

When SET ANSI_DEFAULTS is ON, SET IMPLICIT_TRANSACTIONS is enabled.

The setting of SET IMPLICIT_TRANSACTIONS is set at execute or run time and not at parse time.

Examples

This example demonstrates transactions that are started explicitly and implicitly with the IMPLICIT_TRANSACTIONS set ON. It uses the @@TRANCOUNT function to demonstrate open and closed transactions.

USE pubs
GO

CREATE table t1 (a int)
GO
INSERT INTO t1 VALUES (1)
GO

PRINT 'Use explicit transaction'
BEGIN TRAN
INSERT INTO t1 VALUES (2)
SELECT 'Tran count in transaction'= @@TRANCOUNT
COMMIT TRAN
SELECT 'Tran count outside transaction'= @@TRANCOUNT
GO

PRINT 'Setting IMPLICIT_TRANSACTIONS ON'
GO
SET IMPLICIT_TRANSACTIONS ON
GO

PRINT 'Use implicit transactions'
GO
-- No BEGIN TRAN needed here.
INSERT INTO t1 VALUES (4)
SELECT 'Tran count in transaction'= @@TRANCOUNT
COMMIT TRAN
SELECT 'Tran count outside transaction'= @@TRANCOUNT
GO

PRINT 'Use explicit transactions with IMPLICIT_TRANSACTIONS ON'
GO
BEGIN TRAN
INSERT INTO t1 VALUES (5)
SELECT 'Tran count in transaction'= @@TRANCOUNT
COMMIT TRAN
SELECT 'Tran count outside transaction'= @@TRANCOUNT
GO

SELECT * FROM t1
GO

-- Need to commit this tran too!
DROP TABLE t1
COMMIT TRAN
GO

See Also

ALTER TABLE

BEGIN TRANSACTION

CREATE TABLE

DELETE

DROP TABLE

FETCH

GRANT

Implicit Transactions

INSERT

OPEN

REVOKE

SELECT

SET

SET ANSI_DEFAULTS

@@TRANCOUNT

Transactions

TRUNCATE TABLE

UPDATE