Get the identity of an insert inside a trigger

Question

I have TableA and TableB into SQL Server 2008.

TableA have a trigger that fires after INSERT & UPDATE, On the trigger Im inserting into TableB so my question is how can i get the inserted ID from TableB? FROM INSERTED have the information of the record in TableA?

ALTER TRIGGER [dbo].[trg_up_move] ON [dbo].[TableA] AFTER INSERT, UPDATE
AS 
BEGIN
DECLARE @idMovINS INT
DECLARE @stationINS char(8)
DECLARE @idWorkerINS INT
DECLARE @statusINS TINYINT
SELECT @idMovINS=id_mov FROM INSERTED
SELECT @stationINS=station_number FROM INSERTED
SELECT @idWorkerINS=id_worker FROM INSERTED
SELECT @statusINS=status_mov FROM INSERTED
-- CODE

-- MORE CODE

-- MUCH MORE CODE

  IF @SOMEVAL='WISE DECISION'
  BEGIN
     DECLARE @idTableB INT
     INSERT INTO TableB (FieldA,FieldB,FieldC)VALUES(@idWorkerINS,@stationINS,'More info')
     --SET @idTableB=@@IDENTITY
     --SET @idTableB=SCOPE_IDENTITY()

     --MAKE SOME OTHER THINGS WITH @idTableB
  END
END

If I send a hundred or thousand inserts to tableA on the same session. Can I use @@identity or scope_identity() (i go for the last)to get the b insert's identity? And how convinient and safe is to use one of this two functions taking in consideration each time the trigger is fired and the time to complete all the trigger's function

Possible duplicate of [What is the difference between Scope\_Identity(), Identity(), @@Identity, and Ident\_Current?](http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide) — Yuck, Apr 26 '16 at 20:36
Possible duplicate of [How do I use an INSERT statement's OUTPUT clause to get the identity value?](http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value) — GSerg, Apr 26 '16 at 20:40

granadaCoder · Answer 1 · 2016-04-26T20:41:31.267

Triggers are set based. Thus the notion of getting a single Identity is misguided.

Aka, if you insert 10 rows, the "inserted" table will have 10 rows on it.

The example below shows the use of "output". I believe it would suit your needs better.

create table PrimaryHolderTable ( i int identity (1001,2) not null primary key, j int not null unique )
create table #OutputResultsHolder ( i int not null, j int not null)

insert into PrimaryHolderTable (j)
output inserted.i, inserted.j into #OutputResultsHolder
select top 10 o.object_id from sys.objects as o order by o.object_id desc /*<< from sys.objects is there just to provide some rows */


select * from #OutputResultsHolder
drop table #OutputResultsHolder, PrimaryHolderTable;

go



create table dbo.EmployeeTable ( EmpKey int identity(1001,2) ,  EmpAge int not null );
create table dbo.AuditTable ( EntityKey int not null default -1  ,  OldValue int null, NewValue int null , Tag varchar(64)  );

insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , 'Employee Inserted' into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
 values( 18 );

insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , 'Employee Inserted' into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag) 
 values( 20 );

insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , 'Employee Inserted' into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag) 
 values( 22 );


update dbo.EmployeeTable
   set EmpAge  = EmpAge + 1
output inserted.EmpKey , deleted.EmpAge, inserted.EmpAge , 'Employee Updated' into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
 where EmpAge <=20;

delete from dbo.EmployeeTable
output deleted.EmpKey , deleted.EmpAge, NULL , 'Employee Deleted'  into dbo.AuditTable (EntityKey , OldValue , NewValue , Tag)
 where EmpAge > 0; /*Test multi rows*/

select * from dbo.EmployeeTable; /* <<will be empty at this point */
select * from dbo.AuditTable;

drop table dbo.EmployeeTable, dbo.AuditTable;
go

I once had a vendor who didn't understand this function of triggers. They had a trigger on a table with a subquery. If you did any DML on a set of more than 1, it would throw the "subquery returns more than one row" error. — Arthur D, Apr 26 '16 at 20:39
I know well how the triggers works, I don't say that i will have a single Identity im saying about the process that fires when one record its insert. Maybe I will go for editing the cuestion. — Robert K, Apr 26 '16 at 20:50

Get the identity of an insert inside a trigger

1 Answers1