Hey StackOverflow,

I am (once again) having some issues with some SQL.

I have a table, on which I have created an INSTEAD OF trigger to enforce some buissness rules (rules not really important).

This works as intended. My issue is, that now when inserting data into this table, SCOPE_IDENTITY() now returns a NULL value, rather than the actual inserted identity, my guess is that this is because it is now out of scope - but then how do I get this in scope?

I am using SQL Server 2008.

Per request, here's the SQL:

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 did work before the creation of this trigger, also I am using LINQ to SQL in C# and as far as I can see, I have no way of changing SCOPE_IDENTITY to @@IDENITY - is there really no way out of this one?