If you've ever worked with an object with an IDENTIY column and an INSTEAD OF INSERT trigger defined, you are familiar with this issue; since the INSERT occurs within the scope of the trigger instead of the scope of the insert statement that caused the trigger to execute, SCOPE_IDENTITY() does not return the value of the IDENTITY column. Here is an example of a common situation: a base table with an IDENTITY column and and extension table with a 1:1 relationship to the base, and the storage details abstracted by a view that encapsulates the join logic:

begin tran
go
create table t1
(
    id int identity(1,1)
        constraint PK_t1
            primary key,
    col1 varchar(50)
)
go
create table t2
(
    t1_id int
        constraint PK_t2
            primary key
        constraint FK_t2_t1_id
            foreign key
            references t1 ( id ),
    col2 varchar(50)
)
go
create view v12
as
select
    t1.id,
    t1.col1,
    t2.col2
from t1
join t2 on t2.t1_id = t1.id
go
create trigger v12_ioi
on v12
instead of insert
as
if exists
(
    select count(*)
    from inserted
    having count(*) > 1
)
begin
    raiserror( 'This object only supports single-row inserts', 11, 1 )
    return
end

declare @t1_id int

insert t1( col1 )
select col1
from inserted

set @t1_id = scope_identity()

insert t2( t1_id, col2 )
select @t1_id, col2
from inserted
go
insert v12
(
    col1,
    col2
)
values
(
    'Adam',
    'Anderson'
)

select scope_identity()
-- This returns NULL
go
rollback

Now, sharp-eyed relational DB experts might scoff at this example, because the INSTEAD OF INSERT's implementation restricts INSERT operations to one row at a time. I would agree with this objection; whenever possible, an INSTEAD OF ANYTHING trigger should handle set-based operations instead of forcing the client to use row-based ones (although I would add that most client applications will in fact by default perform INSERTs one row at a time). The most common way to handle this would be to define another unique key on the base table, and using the inserted values in this natural key to look up the generated IDENTITY values in the base table. However, that's not the subject of today's demonstration, and I'm trying to keep the example code as short as possible.

Now obviously, we have access to the SCOPE_IDENTITY() within the INSTEAD OF INSERT trigger, so the question is, how to pass it back out to the caller? The most straightforward way is to output it as a result set, by selecting it.

create trigger v12_ioi
on v12
instead of insert
as
if exists
(
    select count(*)
    from inserted
    having count(*) > 1
)
begin
    raiserror( 'This object only supports single-row inserts', 11, 1 )
    return
end

declare @t1_id int

insert t1( col1 )
select col1
from inserted

set @t1_id = scope_identity()
select @t1_id as id

insert t2( t1_id, col2 )
select @t1_id, col2
from inserted

OK, great, now the IDENTITY gets output as a result set, but how can a SQL batch make use of this output? I want to be able to use the ID to do something like this:

insert v12
(
    col1,
    col2
)
values
(
    'Adam',
    'Anderson'
)

select *
from v12

declare @id int
set @id = scope_identity()

update v12
set col2 = 'works for Falafel'
where id = @id

select *
from v12

I'll tell you what doesn't work: trying to put an OUTPUT clause on that INSERT statement!

insert v12
(
    col1,
    col2
)
output inserted.id
into @ids
values
(
    'Adam',
    'Anderson'
)

-- This returns NULL
select *
from @ids

That's because the OUTPUT clause only outputs values from the virtual INSERTED table, which only contains NULLs in the IDENTITY column. The INSERTED virtual table is also non-updatable, so you can't update it with the SCOPE_IDENTITY() values within the INSTEAD OF INSERT trigger.

I've told you a lot of what won't work, so now it's time for the payoff: what will work. The ultimate goal is to get back that SCOPE_IDENTITY() in a way that following statements can use it. That means getting it into a variable somehow, which means using SET, SELECT, or INSERT. You can't SET @var = INSERT... nor can you SELECT @var = INSERT..., but you can INSERT @table_var EXEC...:

declare @ids table ( id int )

insert @ids ( id )
exec sp_executesql
    N'insert v12
    (
        col1,
        col2
    )
    values
    (
        @col1,
        @col2
    )',
    N'@col1 varchar(50),
    @col2 varchar(50)',
    @col1 = 'Adam',
    @col2 = 'Anderson'

select *
from v12

update v12
set col2 = 'works for Falafel'
from v12
join @ids ids on ids.id = v12.id

select *
from v12

This will achieve the result we're looking for: getting a SCOPE_IDENTITY() out of an INSTEAD OF INSERT trigger and usable by subsequent SQL statements without needing an alternate key. It is also legal to simply insert into the view as normal. The IDENTITY will be output, but can be safely ignored by subsequent SQL statements.

To summarize the technique:

• Put code in the INSTEAD OF INSERT trigger that outputs the IDENTITY value(s) as a result set. This can be achieved with the use of the SCOPE_IDENTITY() function or an OUTPUT clause on the INSERT to the table that actually has the IDENTITY column.
• To capture the output from an INSERT statement to the view:
• Take the INSERT statement and wrap it in a string. This may involve either escaping quotes, or parameterizing the query (parameterization is recommended).
• Ensure the presence of a table that can accept the result set that will be output by the trigger. The table can be a permanent table, temp table, or table variable.
• Execute the INSERT string, inserting the results into the table

Here is the final example code, which you can copy and paste into SQL Management Studio to play with:

begin tran
go
create table t1
(
    id int identity(1,1)
        constraint PK_t1
            primary key,
    col1 varchar(50)
)
go
create table t2
(
    t1_id int
        constraint PK_t2
            primary key
        constraint FK_t2_t1_id
            foreign key
            references t1 ( id ),
    col2 varchar(50)
)
go
create view v12
as
select
    t1.id,
    t1.col1,
    t2.col2
from t1
join t2 on t2.t1_id = t1.id
go
create trigger v12_ioi
on v12
instead of insert
as
if exists
(
    select count(*)
    from inserted
    having count(*) > 1
)
begin
    raiserror( 'This object only supports single-row inserts', 11, 1 )
    return
end

declare @t1_id int

insert t1( col1 )
select col1
from inserted

set @t1_id = scope_identity()
select @t1_id as id

insert t2( t1_id, col2 )
select @t1_id, col2
from inserted
go
declare @ids table ( id int )

insert @ids ( id )
exec sp_executesql
    N'insert v12
    (
        col1,
        col2
    )
    values
    (
        @col1,
        @col2
    )',
    N'@col1 varchar(50),
    @col2 varchar(50)',
    @col1 = 'Adam',
    @col2 = 'Anderson'

select *
from v12

update v12
set col2 = 'works for Falafel'
from v12
join @ids ids on ids.id = v12.id

select *
from v12
go
rollback