Instead of trigger in SQL Server loses SCOPE_IDENTITY?

Question

I have a table where I created an INSTEAD OF trigger to enforce some business rules.

The issue is that when I insert data into this table, SCOPE_IDENTITY() returns a NULL value, rather than the actual inserted identity.

Insert + Scope code

INSERT INTO [dbo].[Payment]([DateFrom], [DateTo], [CustomerId], [AdminId]) VALUES ('2009-01-20', '2009-01-31', 6, 1)  SELECT SCOPE_IDENTITY() 

Trigger:

CREATE TRIGGER [dbo].[TR_Payments_Insert]    ON  [dbo].[Payment]    INSTEAD OF INSERT AS  BEGIN -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;      IF NOT EXISTS(SELECT 1 FROM dbo.Payment p               INNER JOIN Inserted i ON p.CustomerId = i.CustomerId               WHERE (i.DateFrom >= p.DateFrom AND i.DateFrom <= p.DateTo) OR (i.DateTo >= p.DateFrom AND i.DateTo <= p.DateTo)               ) AND NOT EXISTS (SELECT 1 FROM Inserted p               INNER JOIN Inserted i ON p.CustomerId = i.CustomerId               WHERE  (i.DateFrom <> p.DateFrom AND i.DateTo <> p.DateTo) AND                ((i.DateFrom >= p.DateFrom AND i.DateFrom <= p.DateTo) OR (i.DateTo >= p.DateFrom AND i.DateTo <= p.DateTo))               )      BEGIN         INSERT INTO dbo.Payment (DateFrom, DateTo, CustomerId, AdminId)         SELECT DateFrom, DateTo, CustomerId, AdminId         FROM Inserted     END     ELSE     BEGIN             ROLLBACK TRANSACTION     END   END 

The code worked before the creation of this trigger. I am using LINQ to SQL in C#. I don't see a way of changing SCOPE_IDENTITY to @@IDENTITY. How do I make this work?

Answer 1

Use @@identity instead of scope_identity().

While scope_identity() returns the last created id in the current scope, @@identity returns the last created id in the current session.

The scope_identity() function is normally recommended over the @@identity field, as you usually don't want triggers to interfer with the id, but in this case you do.