Board index » delphi » MSSQL Triggers and @@IDENTITY

MSSQL Triggers and @@IDENTITY

Is there a way to preserve the value global variable @@IDENTITY despite the
fact that my INSERT TRIGGER is inserting records into another table?

(I ran into a similar problem with @@ROWCOUNT, but then I realized I could
just SET NOCOUNT OFF/ON.)

 

Re:MSSQL Triggers and @@IDENTITY


Quote
>Is there a way to preserve the value global variable @@IDENTITY despite the
>fact that my INSERT TRIGGER is inserting records into another table?

Assign it to your own variable.

Clive

Re:MSSQL Triggers and @@IDENTITY


This wouldn't solve my problem.  ADO needs to read the value directly from
@@IDENTITY so that it can resync the autoinc field on my client side
recordset.

Quote
"Clive Walden" <cli...@clivewalden.com> wrote in message

news:6p74ct4vat2slbicmr99o1gmc1kqa16sks@4ax.com...
Quote
> >Is there a way to preserve the value global variable @@IDENTITY despite
the
> >fact that my INSERT TRIGGER is inserting records into another table?

> Assign it to your own variable.

> Clive

Re:MSSQL Triggers and @@IDENTITY


I found the following in a post
inmicrosoft.public.sqlserver.programming.  I haven't tried myself, but
it looks like a decent work around

From: Ajax (a...@mail.vinajax.com.tw)
Subject: Re: IDENTITY column problems with triggers
Newsgroups: microsoft.public.sqlserver.programming
Date: 2000-09-10 01:30:02 PST

Create a temporary table named ##Temp_Identity_Set with an identity
column.

Create table ##Temp_Identity_Set (
        ID        int        Identity
)

The owner of temporary tables created by any user is DBO. However,
whenever you attempt to process anything in tempdb your login is
mapped to guest user.

Make the guest account in tempdb a member of the db_owner role.
However, since tempdb is recreated at every startup, you must do this
inside a startup procedure.

1. Create a stored procedure in the master database as an
administrator
user with the following in the stored procedure body:

exec tempdb..sp_addrolemember 'db_owner','guest'

2. Run sp_procoption to mark the procedure startup option to true.

Create a stored procedure named sys_identity_set as follow, and set
the execution permission to public:

create procedure sys_identity_set (@identity_save int ) AS
   set identity_insert ##temp_identity_set on
   delete from ##temp_identity_set
   insert into ##temp_identity_set (id) values (@identity_save)
   set identity_insert ##temp_identity_set off
return
go

In the trigger which will perform an insert into the audit
table, add the following code:

create trigger table_insert_trigger on identity_table for insert as
   declare @identity_save int
   select @identity_save = @@IDENTITY
   ....(your code here)
   exec sys_identity_set identity
return
go

On Tue, 27 Mar 2001 14:47:27 -0500, "Troy Wolbrink"

Quote
<wolbr...@ccci.org> wrote:
>Is there a way to preserve the value global variable @@IDENTITY despite the
>fact that my INSERT TRIGGER is inserting records into another table?

>(I ran into a similar problem with @@ROWCOUNT, but then I realized I could
>just SET NOCOUNT OFF/ON.)

Chris Miller
VersaTrans Solutions, Inc.

Re:MSSQL Triggers and @@IDENTITY


Thank you.  This does the trick!  I think I may have even reduced the
solution down to a single stored procedure:
--
CREATE PROCEDURE sp_SetIdentity(@NewIdentity int) AS

  CREATE TABLE #TempTable (ID int IDENTITY)
  SET IDENTITY_INSERT #TempTable ON
  INSERT #TempTable (ID) VALUES (@NewIdentity)
  DROP TABLE #TempTable
--
But, I'll still have to test it!
--Troy

Quote
"Chris Miller" <chris.mil...@no.canned.ham.versatrans.com> wrote in message

news:vdp6ctgok68mfdkb088i87skm217tl2kck@4ax.com...
Quote
> I found the following in a post
> inmicrosoft.public.sqlserver.programming.  I haven't tried myself, but
> it looks like a decent work around

> From: Ajax (a...@mail.vinajax.com.tw)
> Subject: Re: IDENTITY column problems with triggers
> Newsgroups: microsoft.public.sqlserver.programming
> Date: 2000-09-10 01:30:02 PST

> Create a temporary table named ##Temp_Identity_Set with an identity
> column.

> Create table ##Temp_Identity_Set (
>         ID        int        Identity
> )

> The owner of temporary tables created by any user is DBO. However,
> whenever you attempt to process anything in tempdb your login is
> mapped to guest user.

> Make the guest account in tempdb a member of the db_owner role.
> However, since tempdb is recreated at every startup, you must do this
> inside a startup procedure.

> 1. Create a stored procedure in the master database as an
> administrator
> user with the following in the stored procedure body:

> exec tempdb..sp_addrolemember 'db_owner','guest'

> 2. Run sp_procoption to mark the procedure startup option to true.

> Create a stored procedure named sys_identity_set as follow, and set
> the execution permission to public:

> create procedure sys_identity_set (@identity_save int ) AS
>    set identity_insert ##temp_identity_set on
>    delete from ##temp_identity_set
>    insert into ##temp_identity_set (id) values (@identity_save)
>    set identity_insert ##temp_identity_set off
> return
> go

> In the trigger which will perform an insert into the audit
> table, add the following code:

> create trigger table_insert_trigger on identity_table for insert as
>    declare @identity_save int
>    select @identity_save = @@IDENTITY
>    ....(your code here)
>    exec sys_identity_set identity
> return
> go

> On Tue, 27 Mar 2001 14:47:27 -0500, "Troy Wolbrink"
> <wolbr...@ccci.org> wrote:

> >Is there a way to preserve the value global variable @@IDENTITY despite
the
> >fact that my INSERT TRIGGER is inserting records into another table?

> >(I ran into a similar problem with @@ROWCOUNT, but then I realized I
could
> >just SET NOCOUNT OFF/ON.)

> Chris Miller
> VersaTrans Solutions, Inc.

Re:MSSQL Triggers and @@IDENTITY


I got curious and tried it myself.  Instead of creating a temp table,
I just created a permanent table.  That eliminates all of the extra
stuff regarding startup procs and changing guest account rights.

On Fri, 30 Mar 2001 09:09:51 -0500, "Troy Wolbrink"

Quote
<wolbr...@ccci.org> wrote:
>Thank you.  This does the trick!  I think I may have even reduced the
>solution down to a single stored procedure:
>--
>CREATE PROCEDURE sp_SetIdentity(@NewIdentity int) AS

>  CREATE TABLE #TempTable (ID int IDENTITY)
>  SET IDENTITY_INSERT #TempTable ON
>  INSERT #TempTable (ID) VALUES (@NewIdentity)
>  DROP TABLE #TempTable

Chris Miller
VersaTrans Solutions, Inc.

Re:MSSQL Triggers and @@IDENTITY


Troy,

I believe this is only a problem with SQL Server 7. SQL Server 2000 has
"fixed" this problem. Of course, Microsoft calls it an enhancement.

Randy

Quote
"Troy Wolbrink" <wolbr...@ccci.org> wrote in message

news:3ac0edf3_1@dnews...
Quote
> Is there a way to preserve the value global variable @@IDENTITY despite
the
> fact that my INSERT TRIGGER is inserting records into another table?

> (I ran into a similar problem with @@ROWCOUNT, but then I realized I could
> just SET NOCOUNT OFF/ON.)

Other Threads