November 26, 2011 at 8:53 am
Hi
I need to write an undelete SP. Records are deleted by moving to a deleted table. The PK on the original tbl is an Identity(1,1) field.
This leaves a hole in the PK that I can move a record back on the v.rare occurance it is needed.
If I put SET IDENTITY_INSERT ON before I do the insert and then SET IDENTITY_INSERT OFF after I'm ok.
What if the insert fails will IDENTITY_INSERT still be enabled? Its says it last for the session is that the connection or the SP duration.
I would hope the undelete SP if it fails would leave the IDENTITY_INSERT OFF for everyone else using the DB.
Advice would be greatly appreciated.
November 26, 2011 at 9:27 am
If you are using as SP or even a dynamic T_SQL statement to do the insert, I suggest that you modify it to include a TRY CATCH block, so if the insert fails it will go to the CATCH block, and in the CATCH block you set the IDENTITY INSERT to OFF, in that way your concern for perhaps leaving the IDENTITY INSERT ON will be satisfied.
November 27, 2011 at 11:42 pm
bitbucket-25253 (11/26/2011)
If you are using as SP or even a dynamic T_SQL statement to do the insert, I suggest that you modify it to include a TRY CATCH block, so if the insert fails it will go to the CATCH block, and in the CATCH block you set the IDENTITY INSERT to OFF, in that way your concern for perhaps leaving the IDENTITY INSERT ON will be satisfied.
Bitbucket is absolutely right :-). Just wanted to mention that don't forget to include "SET IDENTITY INSERT OFF" in the TRY block as well along with the CATCH block. Thus, if the SP executed successfully it will execute the whole code of TRY block (& it will set the identity insert OFF) other wise it will go to CATCH block (& it will set the identity insert OFF).
November 28, 2011 at 2:48 am
Thanks
Not used try catch before
Guess it will look something like this
Try -- move rec from tblCustomer_deleted --> tblCustomer
(
set identity_insert tblCustomer on
Begin Trans
insert into tblCustomer
Select * from tblCustomer_deleted where deliD =@ID
delete tblCustomer_deleted where deliD =@ID
Commit trans
)
Catch error
(
set identity_insert tblCustomer off
roll back transaction
)
November 28, 2011 at 3:00 am
Should look something like this
Begin Try -- move rec from tblCustomer_deleted --> tblCustomer
set identity_insert tblCustomer on
Begin Trans
insert into tblCustomer
Select * from tblCustomer_deleted where deliD =@ID
delete tblCustomer_deleted where deliD =@ID
Commit trans
End Try
Begin Catch
set identity_insert tblCustomer off
roll back transaction
End Catch
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply