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

I take it this table was working before you inserted the INSTEAD OF statement?

Have you checked your Primary Key field to make sure it has an Identity specification?

Answer 2

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.

Answer 3

Since you're on SQL 2008, I would highly recommend using the OUTPUT clause instead of any of the custom identity functions. SCOPE_IDENTITY currently has some issues with paralell queries that cause me to recommend against it entirely. @@Identity does not, but it's still not as explicit, and as flexible, as OUTPUT. Plus OUTPUT handles mult-row inserts. Have a look here for the BOL article, and some great examples.

Answer 4

Like araqnid commented, the trigger seems to rollback the transaction when a condition is met. You can do that easier with an AFTER INSTERT trigger:

CREATE TRIGGER [dbo].[TR_Payments_Insert]
   ON  [dbo].[Payment]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    IF <Condition>
    BEGIN
        ROLLBACK TRANSACTION
    END
END

Then you can use SCOPE_IDENTITY() again, because the INSERT is no longer done in the trigger.

The condition itself seems to let two identical rows past, if they're in the same insert. With the AFTER INSERT trigger, you can rewrite the condition like:

IF EXISTS(
    SELECT *
    FROM dbo.Payment a
    LEFT JOIN dbo.Payment b
        ON a.Id <> b.Id
        AND a.CustomerId = b.CustomerId
        AND (a.DateFrom BETWEEN b.DateFrom AND b.DateTo
        OR a.DateTo BETWEEN b.DateFrom AND b.DateTo)
    WHERE b.Id is NOT NULL)

And it will catch duplicate rows, because now it can differentiate them based on Id. It also works if you delete a row and replace it with another row in the same statement.

Anyway, if you want my advice, move away from triggers altogether. As you can see even for this example they are very complex. Do the insert through a stored procedure. They are simpler and faster than triggers:

create procedure dbo.InsertPayment
    @DateFrom datetime, @DateTo datetime, @CustomerId int, @AdminId int
as
BEGIN TRANSACTION

IF NOT EXISTS (
    SELECT *
    FROM dbo.Payment
    WHERE CustomerId = @CustomerId
    AND (@DateFrom BETWEEN DateFrom AND DateTo
    OR @DateTo BETWEEN DateFrom AND DateTo))
    BEGIN

    INSERT into dbo.Payment 
    (DateFrom, DateTo, CustomerId, AdminId)
    VALUES (@DateFrom, @DateTo, @CustomerId, @AdminId)

    END
COMMIT TRANSACTION

Answer 5

I was having serious reservations about using @@identity, because it can return the wrong answer.

But there is a workaround to force @@identity to have the scope_identity() value.

Just for completeness, first I'll list a couple of other workarounds for this problem I've seen on the web:

1. Make the trigger return a rowset. Then, in a wrapper SP that performs the insert, do INSERT Table1 EXEC sp_ExecuteSQL ... to yet another table. Then scope_identity() will work. This is messy because it requires dynamic SQL which is a pain. Also, be aware that dynamic SQL runs under the permissions of the user calling the SP rather than the permissions of the owner of the SP. If the original client could insert to the table, he should still have that permission, just know that you could run into problems if you deny permission to insert directly to the table.

2. If there is another candidate key, get the identity of the inserted row(s) using those keys. For example, if Name has a unique index on it, then you can insert, then select the (max for multiple rows) ID from the table you just inserted to using Name. While this may have concurrency problems if another session deletes the row you just inserted, it's no worse than in the original situation if someone deleted your row before the application could use it.

Now, here's how to definitively make your trigger safe for @@Identity to return the correct value, even if your SP or another trigger inserts to an identity-bearing table after the main insert.

Also, please put comments in your code about what you are doing and why so that future visitors to the trigger don't break things or waste time trying to figure it out.

CREATE TRIGGER TR_MyTable_I ON MyTable INSTEAD OF INSERT
AS
SET NOCOUNT ON

DECLARE @MyTableID int
INSERT MyTable (Name, SystemUser)
SELECT I.Name, System_User
FROM Inserted

SET @MyTableID = Scope_Identity()

INSERT AuditTable (SystemUser, Notes)
SELECT SystemUser, 'Added Name ' + I.Name
FROM Inserted

-- The following statement MUST be last in this trigger. It resets @@Identity
-- to be the same as the earlier Scope_Identity() value.
SELECT MyTableID INTO #Trash FROM MyTable WHERE MyTableID = @MyTableID

Normally, the extra insert to the audit table would break everything, because since it has an identity column, then @@Identity will return that value instead of the one from the insertion to MyTable. However, the final select creates a new @@Identity value that is the correct one, based on the Scope_Identity() that we saved from earlier. This also proofs it against any possible additional AFTER trigger on the MyTable table.

Update:

I just noticed that an INSTEAD OF trigger isn't necessary here. This does everything you were looking for:

CREATE TRIGGER dbo.TR_Payments_Insert ON dbo.Payment FOR INSERT
AS 
SET NOCOUNT ON;
IF EXISTS (
   SELECT 1
   FROM
      Inserted I
      INNER JOIN dbo.Payment P ON I.CustomerID = P.CustomerID
   WHERE
      I.DateFrom < P.DateTo
      AND P.DateFrom < I.DateTo
) ROLLBACK TRAN;

This of course allows scope_identity() to keep working. The only drawback is that a rolled-back insert on an identity table does consume the identity values used (the identity value is still incremented by the number of rows in the insert attempt).

I've been staring at this for a few minutes and don't have absolute certainty right now, but I think this preserves the meaning of an inclusive start time and an exclusive end time. If the end time was inclusive (which would be odd to me) then the comparisons would need to use <= instead of <.