• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

@@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?
0
scm0sml
Asked:
scm0sml
3 Solutions
 
Scott PletcherSenior DBACommented:
Use IDENTITY_SCOPE or ()SCOPE_IDENTITY)
0
 
Kent OlsenData 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
 
Paul JacksonSoftware EngineerCommented:
Try using Scope_Identity instead :

http://msdn.microsoft.com/en-us/library/ms190315.aspx
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Scott PletcherSenior 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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now