SQL Transactions

A Transaction is a set of SQL operations that either succeed as a group or fail as a group. A transaction is an all or nothing event -- there cannot be some operations that succeed and others that fail. Transactions are surrounded by BEGIN TRAN and COMMIT TRAN.

BEGIN TRAN;

INSERT INTO [Order] (OrderNumber, CustomerId, TotalAmount)
VALUES (543210, 23, 183.35);

DECLARE @OrderId INT;
SET @OrderId = SCOPE_IDENTITY();

INSERT INTO OrderItem(OrderId, ProductId, UnitPrice, Quantity)
VALUES (@OrderId, 5, 21.35, 1);

INSERT INTO OrderItem(OrderId, ProductId, UnitPrice, Quantity)
VALUES (@OrderId, 20, 81.00, 2);

COMMIT TRAN;

BEGIN TRAN starts the transaction and COMMIT TRAN commits the transaction, that is, it ensures that all records are written correctly to the hard drive. If any of the INSERT statements fail, than all INSERT statements are rolled back (undone).

These are the fundamental transactional commands.

Note: Transactional commands only work with the following statements: INSERT, UPDATE, and DELETE. They cannot be used with DDL, such as creating or dropping tables.

Without explicit transactions, each INSERT, UPDATE, and DELETE statement is automatically committed (written to disk) following a successful completion. In case of an error, all column changes are rolled back (erased). This is referred to as autocommit mode.

There are situations where you need explicit transactions, such as banking solutions, with debit and credit transactions, or ecommerce solutions with shopping cart and order transactions. If not, there is a risk of partial transactions, incomplete orders, and financial loss.

BEGIN TRAN;

INSERT INTO [Supplier] (CompanyName, ContactName, 
                        City, Country, Phone)
VALUES ('Amsterdam Broodjeshuis', 'Jan van de Berge', 
        'Amsterdam', 'Netherlands','(31) 1 2612 7769');

DECLARE @SupplierId INT;
SET @SupplierId = SCOPE_IDENTITY();

INSERT INTO Product(SupplierId, ProductName, UnitPrice, Package)
VALUES (@SupplierId, 'Krentebollen', 4.35, '6 in a bag');

INSERT INTO Product(SupplierId, ProductName, UnitPrice, Package)
VALUES (@SupplierId, 'Volkoren brood', 6.35, '1 loaf');

INSERT INTO Product(SupplierId, ProductName, UnitPrice, Package)
VALUES (@SupplierId, 'Roggebrood', 2.99, '20 slices per pkgs');

COMMIT TRAN;

Again, either all INSERT statements are successful or they all fail.
For example, it is impossible that the first two INSERT succeed and the rest not.

• Our Sql INSERT Tutorial
• Our Sql UPDATE Tutorial
• Our Sql DELETE Tutorial
• Our Sql Stored Procedures Reference