[RESOLVED] identity seed incremented in transaction

[angelica]

HI,

I have the attached

On inspecting my T_SQL I think I need to insert Rollback in the INSERT part of the SPROC. For example when USERID (field) was missing in tb_Translog, the identity for that table was still executed because the next correction entry had skipped a record.

I wonder if someone can give some advice pls.

PS I have included a ROLLBACK for every error message but I am still getting the indentity seed incremented. I am updating the file. Anyone can give me some expert help on these transactios pls

[brucevde]

A Rollback does not affect Identity values. Once an Identity value has been used it is gone for good.

[angelica]

Dont quite understand it.

If I have an error or condition is not satisified I put a ROLLBACK. The contents of the record do NOT got through but the identity seed is incremented ! Is this normal even though I put it inside a transaction?!

[GaryMazzone]

The Identity value is not affected by the Rollback command. The value can only be generated one time and one time only. By the time the Rollback is sent the action has already completed. You can do other things such as getting the current identity of the table in question at the start of each insert and then if a rollback occures re-seed the table to that value.

PS by the way I think this is way to much trouble to even consider.

[angelica]

ok Gary,

Thanks to you both, at least I set my mind at rest Im doing it the right way.

I wont go into that trouble as you said. cheers