CODEWITHSUNDEEP
sql-servertriggers
CoryU
CoryU

Reputation: 163

How to prevent database trigger updating @@identity after Insert

I am adding a trigger to an existing database table, which is used by an application I did not code, and cannot change the code in.

I want to insert some information from TableA into TableB on INSERT into TableA.

  1. Application runs INSERT INTO TableA <-- this updates @@identity
  2. An ON INSERT trigger that runs on TableA then inserts data into TableB <-- this also updates the @@identity with a new value
  3. Application reads @@identity <-- which is from TableB not from TableA as the application is expecting

Is there any way to not update the @@identity from within the trigger?

Upvotes: 1

Views: 488

Answers (1)

lptr
lptr

Reputation: 6788

....since @@identity has no scope you could create your own scope which carries the @@identity value at the end of the trigger

create table tableA(idA int identity(100, 1), colA int)
go

create table tableB(idB int identity(1000, 1), colB int)
go

create trigger triggerA on tableA
for insert
as
begin

    if not exists(select * from inserted)
    begin
        return;
    end

    declare @tableA@@identity int = @@identity;

    select @@identity as [@@identity_triggerA_in];

    --add rows to tableB
    insert into tableB(colB)
    select object_id
    from sys.all_objects

    select @@identity as [@@identity_after_insert_in_tableB];


    if @tableA@@identity is not null
    begin
        declare @sql varchar(100) = concat('create table #t(id int identity(', @tableA@@identity, ',1)); insert into #t default values');
        exec (@sql);
    end

    select @@identity as [@@identity_triggerA_out];

end
go  

insert into tableA(colA) values (10);
select @@identity;

go

insert into tableA(colA)
select top (200) 1
from sys.all_objects;
select @@identity;
go


insert into tableA(colA)
select 1
where 1=2;

select @@identity;

go

drop table tableA;
go
drop table tableB;
go

Upvotes: 3

Related Questions