I have an insert statrment in my sp followed directly by:
set @ClientCaseID = @@identity
@ClientCaseID is then returned.
It was all working fine.
I then needed to audit things so added a trigger that created a record in a historical table on an insert into the table above.
The problem now is that @ClientCaseID is the value of the insert occuring in the trigger, not my insert statement above as before.
How can I get around this?