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.
||Marks the beginning of a transaction.|
||Marks the end of a transaction. All data changes are committed (written) to disk.|
||Undo (removes) all data changes made since the beginning of the transaction.|
||Sets a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if a failure occurs.|
Note: Transactional commands only work with the following statements:
They cannot be used with DDL, such as creating or dropping tables.
Without explicit transactions, each
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
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.