There are many times when you need to write to your tables using seperate DML statements. These tables may have dependencies that you need to manage. This is when you need a nested transaction. Why?
In this scenario I have two tables. One table I need to insert a new row into, and another I need to update based upon one of the columns in the table I am inserting to. While I have made this a toycode example you can imagine that the insert might be a check register that I am tracking individual spending transactions. The table I am updating would be used to keep the running balance.
DECLARE @RTV AS int DECLARE @RTV2 AS int DECLARE @AGGV AS int SET @RTV = 0 SET @RTV2 = 0 SET @AGGV = 0 SELECT FK_AGG_VAL FROM dbo.atest2 WHERE FK_NUM = 1 BEGIN TRANSACTION INSERT INTO dbo.atest (PK_NUM,SQLSVR_DATE,EINT,EVARCHAR1) VALUES (3,'2010-01-01',5,'Foo') SET @RTV = @@ERROR SET @AGGV = 3 UPDATE dbo.atest2 SET FK_AGG_VAL = (FK_AGG_VAL + @AGGV) FROM dbo.atest2 WHERE FK_NUM = 1 SET @RTV2 = @@ERROR IF @RTV <> 0 or @RTV2 <> 0 ROLLBACK TRANSACTION COMMIT TRANSACTION SELECT FK_AGG_VAL FROM dbo.atest2 WHERE FK_NUM = 1
I reviewed and tried numerous techniques on this subject. Frankly, I found many, though working to be difficult from a code perspective to both implement and understand. They involved really needing to have a good conceptual understanding as to how inner and outer transactions work in SQL Server, and a few other things I found quirky. I wanted a code example that was easy to understand and explain
I created three T-SQL variables. Two are used to manage my insert and update statements in case or an error. The third, @AGGV, is used for my aggregation. I set the values of all of them to zero.
First thing, I select the column FK_AGG_VAL which gives me the current value this column. Notice the select is outside the begin transaction logic. I have the select statement coded so you can see the before and after state of the column value we want to update.
First, we perform the insert into the atest table. Since our goal it to update the column FK_AGG_VAL in atest2 with the value of the EINT column we are inserting into atest it makes sense that this would be our first operation. However, since both DML operations will rollback the transaction if one fails the order actually does not matter for this particular case.
Immediately following the insert you will see that I am setting the value of @RTV to the value found in the variable @@ERROR. @@ERROR will hold the state of the sql statement that was just performed. If successful the value will be zero. Otherwise the value stored in @@ERROR will be whatever SQL Server error was thrown.
Next we move on to our update operation. First thing we do is set the value of @AGGV. For this example I have hardcoded the number. No doubt this value would be set from some sort of dynamic source. In the update statement itself you see that I am adding the current value of FK_AGG_VAL to @RTV. Again, immediately following the statement I check the state of @@ERROR and immediately set it to @RTV2.
Finally, I check the check the status of both @RTV and @RTV2 using "or" logic. If the state of either @RTV or @RTV2 is not zero we are going to rollback the entire transaction, meaning that the insert and the update operations we just performed will both be undone. Once you implement the code you can play with it by making changes to purposely create errors to see that it works as designed.
If find this code much cleaner to understand and manage than using what are called savepoints and doing inner and outer transactions. In essence, and to be technically correct, I have somewhat fibbed in saying that this is actually a nested transaction. But, my rationale for doing so is quite noble. One, nested transactions can be quite confusing to both understand and actully implement. Two, nested transactions are predominantly used to manage dependencies between SQL DML operations than write to more than one table. This code accomplished the same thing, but I think is easier to understand. You can also apply to same concept if you are dealing with seperate stored procedures that are writing to your tables.
There are many, many scenarios for managing transactional dependencies. There are many ways to handle these situations. I have always been in the camp to use what works best for you, and does not end up in the long run making your code hard to maintain, hard to understand, create performance problems in situation where you need to need to scale. If it works and you are achieving your goals then good for you.
Though the example is quite simple (in my opinion) I do consider this to be a more intermediate and/or advanced topic. I say this in-case you are new to this field of work as what I have presented here is merely one example in an arena where there are a multitude of scenarios one needs to deal with.
You will find more SQL code below pertaining to and needed to implement and play around with this example.
CREATE TABLE dbo.atest ( PK_NUM INT NOT NULL , SQLSVR_DATE DATETIME NULL , EVARCHAR1 VARCHAR(20) NULL , EINT INT NULL , CONSTRAINT PK_atest PRIMARY KEY NONCLUSTERED (PK_NUM ASC) ON [PRIMARY] ) GO CREATE TABLE dbo.atest2 ( FK_NUM INT NOT NULL , FK_AGG_VAL INT NULL , CONSTRAINT PK_atest2 PRIMARY KEY NONCLUSTERED (FK_NUM ASC) ON [PRIMARY] ) GO
truncate table atest go truncate table atest2 go