@@identity returning value of trigger insert not primary insert

Hi,

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?
scm0smlAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Yep.  That's what @@IDENTITY does -- returns that the identity value of the most recent insert.

Try using scope_identity.  It returns the identity value in the current scope (which would be the INSERT statement in your procedure, not the trigger).


Good Luck,
Kent
0
 
Scott PletcherSenior DBACommented:
Use IDENTITY_SCOPE or ()SCOPE_IDENTITY)
0
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
Try using Scope_Identity instead :

http://msdn.microsoft.com/en-us/library/ms190315.aspx
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
Yes, it's SCOPE_IDENTITY(), I couldn't remember for sure which way on the first post :-) .
0
 
AshokCommented:
Try

set @ClientCaseID =  IDENT_CURRENT('tablename')

change tablename to the correct tablename.

HTH
Ashok
0
 
scm0smlAuthor Commented:
Ah ha I've learned something new there!!

Thanks all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.