yeah - i've got the following trigger template calling the following sp:

hope it hleps - malcolm...

CREATE TRIGGER [TRIGGERNAME] ON [dbo].[tablename]   FOR INSERT, UPDATE,
DELETE  AS

/**
PURPOSE:
AUTHOR: Malcolm Cook (mec@stowers-institute.org)
**/

set nocount on --for benefit of MS Access 2002
declare @ID as bigint
SET @ID = @@IDENTITY

IF UPDATE(somecolumn)
BEGIN

 exec sp_reset_IDENTITY @ID  --for benefit of MS Access 2002

END

CREATE PROCEDURE [dbo].[sp_reset_IDENTITY]
(@ID as bigint)

/**

Pupose:  reset global @@IDENTITY

Usage: As the last thing done in a trigger, if you think that some client
application is expecting @@IDENTITY to reflect the last inserted row on for
           the triggers base table.  Trap @@IDENTITY at the head of your
trigger, like this

set nocount on --don't forget this or access gets bulloxed up
declare @ID as bigint
SET @ID = @@IDENTITY

and pass it to this sp_ at the end of the trigger, like this:

exec sp_reset_IDENTITY @ID

Author: Malcolm Cook (mec@stowers-institute.org)

Kudos: to Nik Sestrin (sestrin@astfnet.kuzbass.net) who described the
workaround in
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=O1BcqJTQBHA.1788%40tkmsf
tngp05

**/

AS

DECLARE  @strSQL varchar(300)

SET @strSQL=N'SELECT Identity (Int, ' + Cast(@ID As Varchar(10)) + ',
1) AS ignore INTO #sp_reset_IDENTITY_tmp'

EXECUTE (@strSQL)

GO