Implementing Transactions,Error Handling, and Dynamic SQL in SQL

Think of a transaction as a complete package deal. It's like when you buy something from a store. You give them money, and in return, you expect to get the item you paid for.

Now, imagine if you paid the money, but they didn't give you the item. That would be a problem, right? It's because in a transaction, both parts need to happen together. You give the money, and you should get the item. If one part fails, the whole thing fails.

In the world of computer databases, like SQL Server, they also use transactions. Whenever you make changes to the data in the database, like adding or updating information, the database treats it like a transaction. It means that these changes should either happen completely or not at all. If something goes wrong in the middle, it rolls back, just like getting your money back if you don't get what you paid for in a store.

So, in simple terms, transactions in SQL Server make sure that when you make changes to the database, either all the changes happen, or none of them do. It's like a guarantee for data consistency and reliability.

ACID Properties of Transactions

1. Atomicity: Imagine you're transferring money from your savings account to your checking account. Atomicity means that this transaction is like a single action that either happens completely or not at all. If your savings account gets debited, your checking account must get credited. If something goes wrong in between, like a power outage, the entire transaction is canceled.Example: You transfer $100 from your savings account to your checking account. Either the full $100 is transferred, or nothing happens. There won't be a situation where $50 is transferred, and the other $50 disappears.
2. Consistency: Consistency ensures that a transaction takes the database from one valid state to another. Going back to the money transfer example, if you have $500 in savings and $300 in checking before the transaction, after the transaction, you should have $400 in savings and $400 in checking. It shouldn't leave your accounts in an inconsistent state.Example: After the money transfer, you shouldn't end up with $500 in savings and $300 in checking because that doesn't make sense.
3. Isolation: Isolation means that multiple transactions can run simultaneously without interfering with each other. Each transaction should not be aware of the others until they are complete. It's like people in different rooms playing with their own toys without affecting each other's games.Example: If you and your friend are both transferring money at the same time, your transactions should not impact each other. Your money transfer should not interfere with your friend's money transfer.
4. Durability: Durability means that once a transaction is complete and confirmed, its changes are permanent and will survive any failures, like a system crash. It's like writing something in pen; once it's written, it's hard to erase.Example: After your money transfer is successful, even if there's a power outage or a computer crash, your transferred money should still be in your checking account when things come back up.

In essence, ACID properties ensure that database transactions are reliable, predictable, and maintain the integrity of your data, just like ensuring that your money transfers are safe, accurate, and protected from disruptions.

Types of Transactions

In SQL Server, there are two main types of transactions:

1. System Transactions: These are transactions that SQL Server handles internally for its own housekeeping purposes. Users don't have any control over these transactions, and they're not something you need to worry about.
2. User Transactions: These are transactions created by users when they change or read data in the database. These transactions can happen automatically (without you doing anything), implicitly (as part of a larger process), or explicitly (when you specifically start and control a transaction). You can even give your own name to a user transaction if you want to keep track of it.

In this lesson, we'll focus on user transactions, which are the ones you have control over and use when working with SQL Server databases. We won't be dealing with system transactions, as they are managed by SQL Server itself.

Transaction Commands

In SQL, when you want to group a set of actions into a transaction (like a bundle of actions that should all happen together or not at all), you start with a statement called BEGIN TRANSACTION. You can also use a shorter version BEGIN TRAN, and you can even give this transaction a name if you want to keep track of it.

After you've done your actions within the transaction, you have two main choices:

1. Commit: If everything went well and you want to make sure all the actions in your transaction become permanent, you use the COMMIT TRANSACTION command. You can also write it as COMMIT TRAN, COMMIT WORK, or just COMMIT.
2. Rollback: If something went wrong and you want to cancel all the actions in your transaction, you use the ROLLBACK TRANSACTION command. You can also write it as ROLLBACK TRAN, ROLLBACK WORK, or just ROLLBACK.

One important thing to note is that transactions can be nested. This means you can put transactions inside other transactions, and they can span across different parts of your SQL code. It helps you manage complex sequences of actions in a structured way.

Transaction Levels and States

Certainly, let's break it down:

1. @@TRANCOUNT: This is like a counter that keeps track of how many layers of transactions you are currently inside when running SQL code. Imagine transactions as boxes within boxes; each time you start a new transaction, you add another box. Here's what the values mean:
   • If @@TRANCOUNT is 0, it means you're not inside any transaction. It's like having no boxes around your actions.
   • If @@TRANCOUNT is greater than 0, it means you're inside one or more transactions. The number tells you how many layers of boxes you're in. So, if it's 2, you have two boxes around your actions, indicating a nested transaction.
2. XACT_STATE(): This function tells you the condition or state of the current transaction. Think of it as a traffic light:
   • A state of 0 (green light) means there is no active transaction. It's safe to proceed with your actions.
   • A state of 1 (yellow light) means there is a transaction in progress, but it hasn't been confirmed (committed) yet. You're in the middle of something, and you can choose to finish it (commit) or cancel it (rollback). However, it doesn't tell you how many boxes you're in (nested transactions).
   • A state of -1 (red light) means there is a transaction in progress, but there's a serious problem (like an error) that prevents it from being confirmed (committed). It's like a critical roadblock; you can't proceed, and you need to address the issue first.

These two functions help you understand your current transaction environment. @@TRANCOUNT counts the number of boxes (nested transactions), while XACT_STATE() tells you the current status of the traffic light for the transaction (whether it can proceed, needs your decision, or is blocked by an error).

Certainly, here are complete examples of how to check the status and level of a transaction using @@TRANCOUNT and XACT_STATE():

Checking @@TRANCOUNT:

-- Start a transaction
BEGIN TRANSACTION;

-- Check the transaction count
SELECT @@TRANCOUNT AS TransactionCount;

-- Nested transaction
BEGIN TRANSACTION;

-- Check the transaction count again
SELECT @@TRANCOUNT AS TransactionCount;

-- Commit the nested transaction
COMMIT;

-- Check the transaction count after committing the nested transaction
SELECT @@TRANCOUNT AS TransactionCount;

-- Commit the outer transaction
COMMIT;

-- Check the transaction count after committing the outer transaction
SELECT @@TRANCOUNT AS TransactionCount;

-- No transaction
SELECT @@TRANCOUNT AS TransactionCount;

TransactionCount
---------------
1

TransactionCount
---------------
2

TransactionCount
---------------
1

TransactionCount
---------------
0

TransactionCount
---------------
0

Checking XACT_STATE():

-- Start a transaction
BEGIN TRANSACTION;

-- Check the transaction state
IF XACT_STATE() = 0
BEGIN
    PRINT 'No Active Transaction';
END
ELSE IF XACT_STATE() = 1
BEGIN
    PRINT 'Transaction is active and can be committed.';
END
ELSE IF XACT_STATE() = -1
BEGIN
    PRINT 'Transaction is active but cannot be committed due to an error.';
END

-- Commit the transaction
COMMIT;

-- Check the transaction state after committing
IF XACT_STATE() = 0
BEGIN
    PRINT 'No Active Transaction';
END
ELSE IF XACT_STATE() = 1
BEGIN
    PRINT 'Transaction is active and can be committed.';
END
ELSE IF XACT_STATE() = -1
BEGIN
    PRINT 'Transaction is active but cannot be committed due to an error.';
END

Transaction is active and can be committed.
No Active Transaction

Transaction Modes
There are three modes for user transactions in SQL Server—that is, three ways of working with transactions:
■ Autocommit
■ Implicit transaction
■ Explicit transaction

Autocommit Mode:

• In autocommit mode, each individual SQL statement is treated as its own transaction.
• After you execute a single SQL statement (like an INSERT, UPDATE, or DELETE), it automatically commits the changes immediately.
• You don't explicitly start or manage transactions; each statement is its own transaction.

-- In autocommit mode, each statement is its own transaction.
INSERT INTO Customers (Name) VALUES ('John');
-- The above statement is automatically committed.

Implicit Transaction Mode:

1. Implicit Transaction Mode: When you enable implicit transaction mode in SQL Server using the command SET IMPLICIT_TRANSACTIONS ON;, it changes the way SQL Server handles transactions.
2. How it Works: In this mode, when you execute one or more SQL statements, such as data modification (DML) statements like INSERT, UPDATE, DELETE, data definition (DDL) statements like CREATE TABLE, or even just a SELECT statement, SQL Server automatically starts a transaction in the background.
3. Transaction Count: SQL Server keeps track of the number of transactions using a system variable called @@TRANCOUNT. When you issue a SQL statement, @@TRANCOUNT is incremented by one, indicating that you are inside a transaction.
4. No Automatic Commit or Rollback: Importantly, in implicit transaction mode, SQL Server doesn't automatically commit or roll back the transaction after your statement(s) are executed. It leaves the transaction open and pending.
5. Manual Commit or Rollback: To complete the transaction and make your changes permanent, you must issue a COMMIT command if everything went well, or a ROLLBACK command if there's an issue and you want to cancel the transaction. You do this interactively as part of your SQL script.
6. Even for SELECT: Surprisingly, even if you only issue a simple SELECT statement in this mode, SQL Server starts a transaction. You still need to commit or roll back this transaction manually.
7. Not the Default: By default, SQL Server operates in autocommit mode, where each individual SQL statement is treated as a transaction and is automatically committed after execution. Implicit transaction mode is an option you need to enable explicitly using SET IMPLICIT_TRANSACTIONS ON;.

In summary, implicit transaction mode in SQL Server is a mode where transactions are started automatically when you issue SQL statements, but they are not automatically committed or rolled back. You, as the user, must decide when and how to complete or cancel these transactions using COMMIT or ROLLBACK statements.

-- Implicit transaction mode
-- Transaction is started automatically for this INSERT.
INSERT INTO Orders (OrderDate) VALUES ('2023-10-10');
-- You can still explicitly COMMIT or ROLLBACK.
COMMIT;

Explicit Transaction Mode

1. Explicit Transaction Mode: In SQL Server, an explicit transaction occurs when you explicitly initiate a transaction using the BEGIN TRANSACTION or BEGIN TRAN command.
2. How it Works: When you issue BEGIN TRANSACTION, SQL Server starts a new transaction, and the value of @@TRANCOUNT is incremented by 1. This indicates that you are inside a transaction.
3. Executing Statements: Once inside an explicit transaction, you can execute various SQL statements, such as data modification (DML) or data definition (DDL) commands, to make changes to the database.
4. Commit or Rollback: After you've executed your desired statements and are ready to finalize the transaction, you have two options:
   • Use COMMIT to confirm and make all the changes permanent.
   • Use ROLLBACK to cancel all the changes and revert the database to its previous state.
5. Interactive or Code: You can run explicit transactions interactively by issuing commands in a SQL script, or you can incorporate explicit transactions within code, such as stored procedures. This gives you flexibility in managing transactions in both manual and automated processes.
6. Nested Transactions: You can also nest explicit transactions within other explicit transactions. However, each nested transaction increments @@TRANCOUNT by 1. This means that if you start an explicit transaction within another transaction, you effectively have a nested transaction. Managing nested transactions can be complex and requires careful handling to ensure the desired behavior.
7. Error Handling: When executing data modification or DDL statements within explicit transactions, it's essential to handle errors properly. Some errors can cause the entire transaction to roll back, while others may not. For example, foreign key violations typically don't cause the entire transaction to roll back. To ensure consistent and reliable behavior, you should implement error handling in your code to handle unexpected issues.

In summary, explicit transaction mode in SQL Server provides you with full control over when and how transactions are initiated, executed, and finalized. It allows you to ensure data integrity and consistency, especially when dealing with complex sequences of SQL statements or when multiple actions must succeed or fail together.

-- Explicit transaction mode
BEGIN TRANSACTION;
UPDATE Inventory SET Quantity = Quantity - 10 WHERE ProductID = 101;
-- You can choose to COMMIT if everything is okay or ROLLBACK if there's an issue.
COMMIT;

Implementing Transactions

Work with Transaction Modes

1. Work with an implicit transaction first by opening SSMS and opening an empty query
   window. Execute the following code. Execute each command step by step, in sequence.
   Note the output of @@TRANCOUNT.

SET IMPLICIT_TRANSACTIONS ON;
SELECT @@TRANCOUNT; -- 0
SET IDENTITY_INSERT Production.Products ON;
-- Issue DML or DDL command here
INSERT INTO Production.Products(productid, productname, supplierid, categoryid,
 unitprice, discontinued)
 VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
SELECT @@TRANCOUNT; -- 1
COMMIT TRAN;
SET IDENTITY_INSERT Production.Products OFF;
SET IMPLICIT_TRANSACTIONS OFF;
-- Remove the inserted row
DELETE FROM Production.Products WHERE productid = 101; -- Note the row is deleted

Certainly, let's break down the provided SQL code step by step:

1. SET IMPLICIT_TRANSACTIONS ON; - This command enables implicit transactions. It means that any data manipulation (DML) or data definition (DDL) statements will be wrapped in an automatic transaction, and you'll need to explicitly commit or roll back these transactions.
2. SELECT @@TRANCOUNT; -- 0 - It checks the current transaction count, and since you just enabled implicit transactions, there are no active transactions at this point, so @@TRANCOUNT returns 0.
3. SET IDENTITY_INSERT Production.Products ON; - This command allows you to insert explicit values into an identity column (productid) of the "Production.Products" table. Normally, SQL Server generates the values for identity columns automatically.
4. INSERT INTO Production.Products (...) - This is an example of an insert statement. You are inserting a row into the "Production.Products" table with specific values, including a productid of 101.
5. SELECT @@TRANCOUNT; -- 1 - After the insert, a transaction is automatically started because implicit transactions are turned on. So, @@TRANCOUNT now returns 1, indicating that you are within a transaction.
6. COMMIT TRAN; - This command commits the transaction that was automatically started by the insert statement. It makes the changes permanent.
7. SET IDENTITY_INSERT Production.Products OFF; - This command turns off the identity insert mode, so subsequent inserts will allow SQL Server to generate the values for the identity column.
8. SET IMPLICIT_TRANSACTIONS OFF; - This command disables implicit transactions. Now, data manipulation and definition statements won't automatically start transactions, and you have to manage transactions explicitly.
9. DELETE FROM Production.Products WHERE productid = 101; - This statement deletes the row with productid equal to 101 from the "Production.Products" table. Since implicit transactions are now off, this delete operation is not part of a transaction.

2. Next, you work with an explicit transaction. Execute the following code. Note the value
of @@TRANCOUNT.

SELECT @@TRANCOUNT; -- 0
BEGIN TRAN;
 SELECT @@TRANCOUNT; -- 1
 SET IDENTITY_INSERT Production.Products ON;
 INSERT INTO Production.Products(productid, productname, supplierid, 
categoryid, unitprice, discontinued)
 VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
 SELECT @@TRANCOUNT; -- 1
 SET IDENTITY_INSERT Production.Products OFF;
COMMIT TRAN;
-- Remove the inserted row
DELETE FROM Production.Products WHERE productid = 101; -- Note the row is deleted

Certainly, let's go through the provided SQL code step by step:

1. SELECT @@TRANCOUNT; -- 0 - It checks the current transaction count, and initially, there are no active transactions, so @@TRANCOUNT returns 0.
2. BEGIN TRAN; - This command starts a new transaction. Now, you are within a transaction.
3. SELECT @@TRANCOUNT; -- 1 - After starting the transaction, @@TRANCOUNT returns 1, indicating that you are inside a transaction.
4. SET IDENTITY_INSERT Production.Products ON; - This command allows you to insert explicit values into an identity column (productid) of the "Production.Products" table.
5. INSERT INTO Production.Products (...) - This is an example of an insert statement. You are inserting a row into the "Production.Products" table with specific values, including a productid of 101.
6. SELECT @@TRANCOUNT; -- 1 - After the insert, @@TRANCOUNT still returns 1 because you are still within the same transaction that you started earlier.
7. SET IDENTITY_INSERT Production.Products OFF; - This command turns off the identity insert mode, so subsequent inserts will allow SQL Server to generate the values for the identity column.
8. COMMIT TRAN; - This command commits the transaction, making the changes permanent. After this, there are no active transactions.
9. SELECT @@TRANCOUNT; -- 0 - After committing the transaction, @@TRANCOUNT returns 0 again, indicating that there are no active transactions.
10. DELETE FROM Production.Products WHERE productid = 101; - This statement deletes the row with productid equal to 101 from the "Production.Products" table. Since the transaction was already committed, this delete operation is not part of a transaction.

3. To work with a nested transaction by using COMMIT TRAN, execute the following
   code. Note that the value of @@TRANCOUNT increments to 2.

SELECT @@TRANCOUNT; -- = 0
BEGIN TRAN;
 SELECT @@TRANCOUNT; -- = 1
 BEGIN TRAN;
 SELECT @@TRANCOUNT; -- = 2
 -- Issue data modification or DDL commands here
 COMMIT
 SELECT @@TRANCOUNT; -- = 1
COMMIT TRAN;
SELECT @@TRANCOUNT; -- = 0

4. To work with a nested transaction by using ROLLBACK TRAN, execute the following
   code. Note that the value of @@TRANCOUNT increments to 2 but only one ROLLBACK
   is required.

SELECT @@TRANCOUNT; -- = 0
BEGIN TRAN;
 SELECT @@TRANCOUNT; -- = 1
 BEGIN TRAN;
 SELECT @@TRANCOUNT; -- = 2
 -- Issue data modification or DDL command here
 ROLLBACK; -- rolls back the entire transaction at this point
SELECT @@TRANCOUNT; -- = 0