Creating a transaction with SQL Server

If you've clicked on this article, you should already know what a database transaction is, and their usefulness.

If you'd like to refresh your memory, you can read our dedicated article: https://code-garage.fr/blog/quest-ce-qu-une-transaction-en-base-de-donnees

Creating a transaction

Inside a transaction, all operations are performed one after another, they cannot be divided.

We refer to these as "atomic" operations

To start a transaction on SQL Server, simply do:

BEGIN TRANSACTION;

But you need to keep in mind that a transaction can only end in two distinct states:

• All operations have been applied to the database
• No operations have been applied

There's no in-between, it's all or nothing!

Validating a transaction

In SQL, when we validate (and apply) a transaction, we call this a commit. The command to validate our transaction is therefore logically:

COMMIT TRANSACTION;

Canceling a transaction

And conversely, when we want to restore our database to its original state, we perform a rollback, like this;

ROLLBACK TRANSACTION;

Example of a transaction

Here's an example of a demonstration transaction, with which you can test commit or rollback depending on the value of the @Test variable:

-- Commencer une nouvelle transaction
BEGIN TRANSACTION;
DECLARE @Test BIT = 1; -- valeur à modifier

-- [...] <- Insérez votre code SQL
-- INSERT INTO <table> (<column1>, <column2>)
--  VALUES (<value1>, <value2>)

IF @Test = 1
	BEGIN
		-- Valider la transaction
		COMMIT TRANSACTION;
	END
ELSE
	BEGIN
		-- Annuler la transaction
		ROLLBACK TRANSACTION;
	END