Cacher is the code snippet organizer for pro developers

/* 

TRANSACTION - group of tasks defining a unit of work - the entire unit must succeed or fail together; no partial completion is permitted. It's refered to as ACID (Atomic, can't be split up, Consistent, there won't be an orphan rows in one table, Isolated, doesn't affect the operation of other operations going on at the same time, Durable, it changes things in the database permanently). An example would be a bank transfer. You don't want an operation that debits money from one account but then fails to credit the amount to the other account. Individual queries are treated by SQL Server as a transaction. SQL Server uses locking mechanisms and the transaction log to support transactions.

XACT_STATE() - can check transactional state (0 = no transactions running, 1 = transaction is in process but is on path to be completed successfully, 2 = transaction is in process but is doomed to fail)

XACT_ABORT - Any error condition will cause any work done in the TRANSACTION to be rolled back automatically. Note that there is no ROLLBACK statement in the code below. This is an all or nothing solution, so normally you would include a ROLLBACK to you could be more specific in test what the error was and you can have more control.

*/

-- A unit of work example below. It's inserting in two tables, but you want it to succeed or fail together. You don't want it to succeed in creating the order header (the first statement) and then fail to insert the order detail, since you'll then get an orphan order header.
INSERT INTO Sales.Order ...
INSERT INTO Sales.OrderDetail ...

-- Basic TRANSACTION syntax with TRY/CATCH block
-- begin the TRY block
BEGIN TRY
	-- begin the TRANSACTION
	BEGIN TRANSACTION
		-- specify the unit of work you want to complete
		INSERT INTO Sales.Order...
		INSERT INTO Sales.OrderDetail
	-- complete the transaction
	COMMIT TRANSACTION
-- end the TRY block
END TRY
-- begin the CATCH block to handle any errors that come up in the TRY block
BEGIN CATCH
	-- checks how many TRANSACTIONs are running
	IF @@TRANCOUNT > 0
	BEGIN
		-- if error is thrown, ROLLBACK the TRANSACTION
		ROLLBACK TRANSACTION
	END
	PRINT ERROR_MESSAGE();
	THROW 500001, 'An error has occurred', 0;
END CATCH;

-- more detailed example of TRANSACTION that COMMITs everything and then ROLLBACKs what it's done

-- begin TRY
BEGIN TRY
	-- begin the TRANSACTION that has three distinct queries that will succeed or fail together
	BEGIN TRANSACTION
		-- first insert query, creates SalesOrderHeader
		INSERT INTO SalesLT.SalesOrderHeader (DueDate, CustomerID, ShipMethod)
		VALUES
		(DATEADD(dd, 7, GETDATE()), 1, 'STD DELIVERY');

		-- query 2, DECLAREs variable that will get the IDENTITY created in the scope
		DECLARE @SalesOrderID int = SCOPE_IDENTITY();

		-- third query, creates SalesOrderDetail using the IDENTITY in the @SalesOrderID
		INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount)
		VALUES
		(@SalesOrderID, 1, 99999, 1431.50, 0.00);
	-- COMMITs the TRANSACTION
	COMMIT TRANSACTION
-- ends TRY block
END TRY
-- begins CATCH block the will handle any errors that arise from the TRY block
BEGIN CATCH
	-- IF there's a transaction in process
	IF @@TRANCOUNT > 0
	-- BEGIN the block of the IF statement
	BEGIN
		-- print the transaction state using XACT_STATE()
		PRINT XACT_STATE();
		-- ROLLBACK the TRANSACTION
		ROLLBACK TRANSACTION;
	-- END the block of the IF statement
	END
	-- PRINT the error message
	PRINT ERROR_MESSAGE();
	-- additionally, also THROW a custom error message
	THROW 500001, 'An insert failed. The transaction was cancelled.', 0;
-- end the CATCH block
END CATCH;

/* 

Results of query above:

(1 rows(s) affected)

(0 rows(s) affected)
1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder1"
Msg 500001, Level 16, State 0, Line 49
An insert failed. The transaction was cancelled.


DETAIL results of the query above:

-- the first query succeeded
(1 rows(s) affected)

-- the second query failed
(0 rows(s) affected)
-- result of XACT_STATE(), showing 1 meaning TRANSACTION is in process
1
-- SQL Server error message, in black text
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder1"
-- the custom error message thrown, in red text
Msg 500001, Level 16, State 0, Line 49
An insert failed. The transaction was cancelled.

*/

-- example using XACT_ABORT. Any error condition will cause any work done in the TRANSACTION to be rolled back automatically. Note that there is no ROLLBACK statement in the code below. This is an all or nothing solution, so normally you would include a ROLLBACK to you could be more specific in test what the error was and you can have more control.

SET XACT_ABORT ON;
-- begin TRY
BEGIN TRY
	-- begin the TRANSACTION that has three distinct queries that will succeed or fail together
	BEGIN TRANSACTION
		-- first insert query, creates SalesOrderHeader
		INSERT INTO SalesLT.SalesOrderHeader (DueDate, CustomerID, ShipMethod)
		VALUES
		(DATEADD(dd, 7, GETDATE()), 1, 'STD DELIVERY'

		-- query 2, DECLAREs variable that will get the IDENTITY created in the scope
		DECLARE @SalesOrderID int = SCOPE_IDENTITY();

		-- third query, creates SalesOrderDetail using the IDENTITY in the @SalesOrderID
		INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount
		VALUES
		(@SalesOrderID, 1, 99999, 1431.50, 0.00);
	-- COMMITs the TRANSACTION
	COMMIT TRANSACTION
-- ends TRY block
END TRY
-- begins CATCH block the will handle any errors that arise from the TRY block
BEGIN CATCH
	-- PRINT the error message
	PRINT ERROR_MESSAGE();
	-- additionally, also THROW a custom error message
	THROW 500001, 'An insert failed. The transaction was cancelled.', 0;
-- end the CATCH block
END CATCH;
--turns off XACT_ABORT
SET XACT_ABORT OFF;

-- SET TRANSACTION allows you to change the isolation level of a transaction globally or in a session, as a way of preventing multiple users from updating the same data at the same time. Higher isolation means a safer option but takes more resources, whereas lower isolation can be faster but less safe. By default, a transaction takes place in read/write mode, with both reads and writes permitted to tables used in the transaction, but SET TRANSACTION can change this. Serializable is the safest but takes the most resources, meaning a transaction must complete before another transaction can touch the data. you can relax the consistency rules with READ COMMITTED or even READ UNCOMMITTED, in situations such as bulk reporting where precise consistency and repeatable results are less important than minimizing the amount of overhead for locking. Line 15 shows how to set session iso level.

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic:
    ISOLATION LEVEL level
  | READ WRITE
  | READ ONLY

level:
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;