July 22, 2016 at 11:29 am
Hi guys,
Just looking for some advice on the best way to go about this. Here's my example.
So, I'm inserting some data into TableA. I then take the identity primary key from TableA and insert it into TableB (foreign key). So here's my problem. I'd like to put both inserts in a transaction and roll both back if there are any failures. But unfortunately TableA has to be committed first. I can't rollback TableA once committed if the insert to tableB fails which is what I need to do. And I can't do one commit at the end because the data has to go into table A so that I can get the keys to insert into TableB.
Any ideas on how I can rollback everything in a transaction on any failures?
Thanks,
Strick
July 22, 2016 at 12:24 pm
As long as they are in the same transaction, changes to table A do not need to be committed in order to be available to table B. Generally, people use explicit transactions to guarantee that they are in the same transaction.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 23, 2016 at 7:22 am
If you have some other reason to commit the changes to the first table (e.g. triggers) then just know they fire when the DML is executed, but not committed until the commit. A single transaction is treated as a single unit of work.
So, unless there's something weird going on, then what Drew said is exactly correct. If you have a RECONFIGURE, it can't be inside a transaction. A BACKUP can't be rolled back and have the file be deleted from disk. Other than this type of thing, you should be good. Test it out as a single transaction and see if the whole thing works.
July 23, 2016 at 7:31 am
stricknyn (7/22/2016)
But unfortunately TableA has to be committed first.
Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2016 at 2:35 pm
Because I need the keys to insert into the second table
July 24, 2016 at 2:24 am
Doesn't mean the transaction has to be committed, you can get the IDs within the transaction. You can get them from the insert using the OUTPUT clause, then use that to insert into the second table, whole lot inside a begin try... end try with a transaction block wrapped around it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2016 at 7:36 pm
Here is the scrit to illustrate the point:
USE tempdb
CREATE TABLE #TableA (
ID INT IDENTITY(1,1),
Name NVARCHAR(50),
PRIMARY KEY (id),
)
CREATE TABLE #TableB (
REfID INT ,
Reference NVARCHAR(50),
FOREIGN KEY (RefID) REFERENCES #TableA (id)
)
DECLARE @Lastid INT, @Name NVARCHAR(50), @Reference NVARCHAR(50)
SELECT @Name = 'Integrity', @Reference = 'Referential'
BEGIN TRANSACTION
INSERT INTO #TableA (Name )
SELECT @Name
WHERE NOT EXISTS (SELECT * FROM #TableA ta WHERE ta.Name = @Name)
SELECT @Lastid = SCOPE_IDENTITY()
INSERT #TableB ( REfID, Reference )
SELECT @Lastid, @Reference
SELECT * FROM #TableB tb
INNER JOIN #TableA ta ON ta.ID = tb.REfID
-- both records are inserted, IDENTITY has been used with no issues
ROLLBACK
SELECT * FROM #TableB tb
SELECT * FROM #TableA ta
-- nothing left. ROLLBACK reversed everything what was done within the transaction
DROP TABLE #TableB
DROP TABLE #TableA
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply