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