Exception handling with IDENTITY column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Exception handling with IDENTITY column

Author

Topic

Alvin_SQL007
Starting Member

12 Posts

Posted - 2013-06-06 : 17:30:18

Hi All,

I have the following query.
-----------------------------------------------------------------
DECLARE @errNum INT
DECLARE @errMsg VARCHAR(MAX)

BEGIN TRY

BEGIN TRAN

INSERT INTO TABLE1
(Value1,
Value2)
SELECT Value1,
Value2
FROM TABLE30

UPDATE TABLE2
SET TABLE2.ID = 250
WHERE ID = 202

COMMIT TRAN

END TRY

BEGIN CATCH

SELECT @errNum = error_number()
SELECT @errMsg = convert(VARCHAR,@errNum) + '-' + error_message()
SELECT error_number() ErrorNBR, error_severity() Severity,
error_line() ErrorLine, error_message() Msg
PRINT 'Error !!! - ' + @errMsg

ROLLBACK TRAN

RAISERROR (@errMsg,@errNum,16,'SCRIPT ERROR')

END CATCH
-------------------------------------------------------------------
Table1 has an Identity column (ID) and Table2 has the Identity colum (ID). So, the update happenning on Table2 is not allowerd! understood. and everything gets rolled back(including the first insert). which is perfect.

but next time when i remove the update and run the same query i get following error

The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "constraint name". The conflict occurred in database "database name", table "dbo.Table1", column 'ID'.

some forums say it is because of the IDENTITY is not Reseeded! but i am not able to solve this problem. any help is appreciated.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 17:36:16

I didn't quite follow the logic of what you are describing; and what you described is mostly right. When you insert something into a table with an identity column and then rollback, the next insert will not reuse the identity values that were rolled back. You can see that behavior if you run this test program. You can copy it to an SSMS window and run it.

Given that, you have to restructure you code to live with that behavior. As I said earlier, I didn't follow the logic you are trying to implement, so I am unable to help:

CREATE TABLE #tmp(id INT NOT NULL IDENTITY(1,1));

SELECT IDENT_CURRENT('#tmp') -- shows 1

BEGIN TRAN
INSERT INTO #tmp DEFAULT VALUES;
ROLLBACK

SELECT IDENT_CURRENT('#tmp'); -- shows 2

INSERT INTO #tmp DEFAULT VALUES;
SELECT * FROM #tmp; -- shows 2

DROP TABLE #tmp;

Alvin_SQL007
Starting Member

12 Posts

Posted - 2013-06-06 : 18:02:14

Hi James, Thanks for the swift response.Following is my explanation

1) create a temp table #temp3 and insert 1 record as following.

CREATE TABLE #tmp3(id INT NOT NULL IDENTITY(1,1),value1 VARCHAR(50));
INSERT INTO #tmp3 (value1)
SELECT 'INSERT1'

2) Comment the above code and paste the following in the same query pane

BEGIN TRAN

INSERT INTO #tmp3 (value1)

SELECT 'INSERT2'

ROLLBACK

SELECT IDENT_CURRENT('#tmp3')

SELECT * FROM #tmp3

3) run the query. you will see 2 result panes. 1st result pane will have value 2 because of SELECT IDENT_CURRENT('#tmp3') and the second will show 1 row as we inserted in step 1

4) now, run the query again, the first pane will have 3 and the second pane unchanged. if you keep running the query the IDENTITY value will keep increasing .

But what i want to do is, if the TRAN is rolled back then dont increase the identity value. so that next time i insert without rollback i should have identity value as 2.

Alvin_SQL007
Starting Member

12 Posts

Posted - 2013-06-06 : 18:30:04

********SOLVED*******

By adding following code after the rollback

DECLARE @RESEEDVALUE BIGINT;
SELECT @RESEEDVALUE = ISNULL(MAX(ID),0)
FROM #tmp3

DBCC CHECKIDENT (#tmp3, RESEED,@RESEEDVALUE)
----------------------------------------------------------------
so the final query looks like following

BEGIN TRAN

INSERT INTO #tmp3 (value1)

SELECT 'INSERT3'

ROLLBACK

DECLARE @RESEEDVALUE BIGINT;
SELECT @RESEEDVALUE = ISNULL(MAX(ID),0)
FROM #tmp3

DBCC CHECKIDENT (#tmp3, RESEED,@RESEEDVALUE)

SELECT IDENT_CURRENT('#tmp3')

SELECT * FROM #tmp3

-----------------------------------------------

Thanks for you help James.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-06-10 : 10:52:51

reseeding the identity is really bad form. WHY do the IDs need to be consecutive? It seems that you are solving the wrong problem.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources