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
– Jonathan Allen, Commented Feb 3, 2012 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
– Joe Stefanelli, Commented Feb 3, 2012 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
– Jonathan Allen, Commented Feb 3, 2012 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 — Martin Smith
– Martin Smith, Commented Feb 3, 2012 at 21:09

Martin Smith · Accepted Answer · 2012-02-03 21:30:14Z

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.

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related