LISTING 4: The ResetIdentity Stored Procedure

-- The ResetIdentity stored procedure
CREATE PROC ResetIdentity
  @prevident AS int
AS

DECLARE @SQL AS varchar(100)

SELECT @SQL =
  'SELECT IDENTITY(int, ' +
            CAST(@prevident as varchar) +
            ', 1) AS ident ' +
  'INTO #TmpIdent'

EXEC(@SQL)
GO

-- The revised trigger
-- In SQL Server 6.5 drop and recreate the trigger
ALTER TRIGGER trg_i_Orders_logorder ON Orders FOR INSERT
AS

DECLARE @neworder AS int
SELECT @neworder = @@identity

INSERT INTO LogOrders(orderid, customerid, orderdate)
  SELECT * FROM inserted

EXEC ResetIdentity @neworder
GO