Is there a way to make INSTEAD OF Triggers work with SCOPE_IDENTITY?

Question

This trigger:

ALTER TRIGGER InsteadTrigger on CustomerView
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Person
        SELECT FirstName, LastName
        FROM inserted

Causes this query to return null.

INSERT INTO CustomerView (FirstName, LastName) Values ('Jonathan', 'Allen')
SELECT SCOPE_IDENTITY()

Is there a way to fix the trigger so that SCOPE_IDENTITY will return the correct value? (I can't use @@IDENTITY because there may be other triggers on the table involved.)

P.S. My customer is asking about this. I would never intentionally get myself into this situation. — Jonathan Allen, Feb 03 '12 at 21:02
First off, your making the very common error of assuming the INSERT would only process one row. Multiple rows can be inserted by a single insert statement. — Joe Stefanelli, Feb 03 '12 at 21:07
No I'm not. The trigger will work for multiple rows and no one expects SCOPE_IDENTITY to work unless you only inserted one. — Jonathan Allen, Feb 03 '12 at 21:08
possible duplicate of [SQL Server list of insert identities](http://stackoverflow.com/questions/4619343/sql-server-list-of-insert-identities) — Joe Stefanelli, Feb 03 '12 at 21:08
@JoeStefanelli - That doesn't work here. I was going to suggest the `OUTPUT` clause but it always seems to return `0` in my test. It does the `OUTPUT` clause before generating the `identity` [Test script here](https://gist.github.com/1732636) — Martin Smith, Feb 03 '12 at 21:09

Martin Smith · Accepted Answer · 2012-02-03T21:30:14.287

3

No I don't think this is possible.

You can return it from the trigger itself of course.

ALTER TRIGGER InsteadTrigger on CustomerView
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Person
        SELECT FirstName, LastName
        FROM inserted

        SELECT SCOPE_IDENTITY() AS PersonId
END

And you can use CONTEXT_INFO to suppress the result set except for when you require it.

Using the OUTPUT clause doesn't work in this context as discussed in this Microsoft Connect Item it is evaluated before the insert into the main table and so before the IDENTITY function is called.

edited Feb 03 '12 at 21:30

answered Feb 03 '12 at 21:20

Martin Smith

438,706
87
741
845

Not great, but then again neither is this whole situation. At this time I do believe this is the best that they can hope for. – Jonathan Allen Feb 04 '12 at 01:24

Is there a way to make INSTEAD OF Triggers work with SCOPE_IDENTITY?

1 Answers1