ITG Systems Integration Inc.

Data Architects

Tech Blog - Don't crush the IDENTITY

People unfamiliar with triggers, may get unexpected results OUTSIDE of the trigger. This usually occurs when a process/application/stored procedure expects to use the identity value of the INSERTED row. If the trigger does an insert of its own, then the identity value that is expected by the outside application will be replaced by the one created by the trigger. The following code snippet will help you keep your identity straight...

CREATE TRIGGER [T_TestTrigger] ON [TestTable] 
FOR  INSERT 
AS

DECLARE @SavedIdent int
DECLARE @Ident int
DECLARE @Cmd varchar(500)

-- Save the identity context,  
SELECT @SavedIdent = @@identity

-- Do any work, including doing an INSERT into a table that has an identity
INSERT into TableWithIdent (c1) Values ('Value 1')

-- This identity is from the table [TableWithIdent]
SELECT @@Ident = @@identity

-- Do Some more work ---------
...
-- Do Some more work ---------

-- Finish up and restore the original identity
IF @SavedIdent is not null
BEGIN
   SELECT @Cmd = 'Create table #RestoreIdentity ( id int identity ('+convert(varchar(20) ,@SavedIdent)+ ',1) not null , SomeColumn int)  
                  INSERT into #RestoreIdentity values (1)
                  DROP table #RestoreIdentity'
   EXEC(@Cmd)
END