Hello everybody. I run an insert

[Syerston]

Hello everybody.

I run an insert in a stored procedure and return the identity value for populating a field on the next insert.
However, I have now set up a trigger to fire on the insert to the first table which populates an Audit table. The identity is now returned as the identity from the audit table instead.

Any ideas.

 

[JamesLean]

I take it you are using @@IDENTITY in your SP? If so, try using SCOPE_IDENTITY instead.

--James

 

[Syerston]

James thanks for replying.

I am using @@Identity

How would Scope_Identity fit in below. P.S. I know nothing matches in the statement

INSERT INTO ClaimantDetails (****.....)
VALUES (@ProviderID @Address2, @Address3, @Postcode, GetDate()..)
SELECT @@Identity

If @CreditDebit = 'c' or @CreditDebit = 'd'
Begin

INSERT INTO CreditDebit (YearNo, DateKeyed, SPUser.......)
VALUES (@@Identity, @Year, GetDate(), SUSER_SNAME().....)
End

 

[JamesLean]

Just substitute SCOPE_IDENITITY() where you have used @@IDENTITY, eg:

[codeSELECT SCOPE_IDENTITY()[/code]

--James

 

[JamesLean]

Sorry, that should be:

SELECT SCOPE_IDENTITY()

--James

 

[Syerston]

Thanks James I've got it.