Trigger Template with Identity Preservation

A starting point for writing triggers that need to preserve identity values. This template captures @@IDENTITY at the start of an INSERT/UPDATE trigger and, at the end, resets the table's identity seed so that subsequent inserts continue from where this one left off - a classic technique when replicating or synchronizing rows between databases.

CREATE TRIGGER [YadaYaa] ON [dbo].[HooptyLa]
FOR INSERT, UPDATE AS
BEGIN
	------------------------------------------------------------------------------------------
	SET NOCOUNT ON;
	--Save @@Identity
	DECLARE @iLastID INT
	SET @iLastID = @@IDENTITY
	------------------------------------------------------------------------------------------

	--Put stuff here!!!

	------------------------------------------------------------------------------------------
	--Reset @@Identity
	DECLARE @sIDSQL VARCHAR(255)
	SET @sIDSQL = 'SELECT IDENTITY( int, ' + CONVERT(VARCHAR(10) , @iLastID) + ' , 1 ) AS IDVal INTO #IVSTACK'
	EXEC(@sIDSQL)
	------------------------------------------------------------------------------------------
END
GO