COMMIT TRANSACTION

Mark the end of a successful implicit or explicit transaction.

Syntax
      COMMIT TRAN[SACTION] [transaction]

      COMMIT TRAN[SACTION] [@transaction_var] [;]

 Key:
   transaction     A name for the transaction <= 32 characters.
   transaction_var A user-defined variable containing a transaction name.

If @@TRANCOUNT is greater than 1, the transaction will stay active. Each COMMIT TRANSACTION decrements @@TRANCOUNT by 1 until it reaches 1.

When @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0.

Placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger is not recommended.

Example

DECLARE @MyTran VARCHAR(20);
SELECT @MyTran = 'MyTransaction';

BEGIN TRANSACTION @MyTran;
USE MyDatabase;
DELETE FROM MyDatabase.MySchema.MyTable WHERE MyColumn = 123;

COMMIT TRANSACTION @MyTran;
GO

"The two offices of memory are collection and distribution" - Dr. Johnson

Related commands:

COMMIT WORK
Equivalent Oracle command: COMMIT