IDENTITY column problems with triggers

IDENTITY column problems with triggers

Post by Magnus Lindh » Thu, 13 Jul 2000 04:00:00



I have two tables A and B and both use identity columns as primary keys.
Table A have an insert trigger in which a an insert into table B is done.
The trouble I have is that when I use ADO to add a new row into A i get the
identity value of the row insertet into B from A's insert trigger.

Is there a work-around for this behaviour?

 
 
 

IDENTITY column problems with triggers

Post by Ivan Arjentinsk » Thu, 13 Jul 2000 04:00:00



--
Ivan Arjentinski


Quote:> I have two tables A and B and both use identity columns as primary keys.
> Table A have an insert trigger in which a an insert into table B is done.
> The trouble I have is that when I use ADO to add a new row into A i get
the
> identity value of the row insertet into B from A's insert trigger.

> Is there a work-around for this behaviour?


 
 
 

IDENTITY column problems with triggers

Post by Ajax » Mon, 11 Sep 2000 17:30:47



following is an excerpt from the SQL Server Books Online:


variable is accurate after an insert into a table with an identity column;
however, this value is reset after an insert into a table without an
identity column occurs.

Improperly relying on this behavior may quickly cause changes in your
application. For example, suppose you do an insert into a table and then

then adds an insert trigger to the table to perform an insert into the audit


This problem may compound itself when you start adding nested triggers or
stored procedures.

You can employ the following strategy:

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:


   set identity_insert ##temp_identity_set on
   delete from ##temp_identity_set

   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


   ....(your code here)
   exec sys_identity_set identity
return
go
 --

Ajax



Quote:> I have two tables A and B and both use identity columns as primary keys.
> Table A have an insert trigger in which a an insert into table B is done.
> The trouble I have is that when I use ADO to add a new row into A i get
the
> identity value of the row insertet into B from A's insert trigger.

> Is there a work-around for this behaviour?

 
 
 

IDENTITY column problems with triggers

Post by Ivan Arjentinsk » Mon, 11 Sep 2000 23:05:53


Ajax,

Nice one!
Congratulations!

This is solution to old painfull problem, which forced me to never write
insert triggers which insert in another identity table.
With SQL 2000's SCOPE_IDENTITY() the problem was almost solved, but ADO/DAO

--
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------



The
> following is an excerpt from the SQL Server Books Online:


> variable is accurate after an insert into a table with an identity column;
> however, this value is reset after an insert into a table without an
> identity column occurs.

> Improperly relying on this behavior may quickly cause changes in your
> application. For example, suppose you do an insert into a table and then

administrator
> then adds an insert trigger to the table to perform an insert into the
audit


> This problem may compound itself when you start adding nested triggers or
> stored procedures.

> You can employ the following strategy:

> 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:


>    set identity_insert ##temp_identity_set on
>    delete from ##temp_identity_set

>    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


>    ....(your code here)
>    exec sys_identity_set identity
> return
> go
>  --

> Ajax



> > I have two tables A and B and both use identity columns as primary keys.
> > Table A have an insert trigger in which a an insert into table B is
done.
> > The trouble I have is that when I use ADO to add a new row into A i get
> the
> > identity value of the row insertet into B from A's insert trigger.

> > Is there a work-around for this behaviour?

 
 
 

IDENTITY column problems with triggers

Post by Umachandar Jayachandra » Tue, 12 Sep 2000 01:08:58


    Try this solution. I posted this a year back actually :-) Got the post
from my OE sent box. This is SQL70 version & uses the new CURSOR_STATUS
function etc. But the code can be used with minimal changes in SQL6x also.
HTH.

--
    This addresses the issue of inserting into other tables in the trigger.
One restriction is that you can't nest calls to this SP in other triggers
like this.

    Here is what i do. I use a cursor to store the identity value from the
trigger. Then i can fetch from this cursor after the trigger fires in any
SP. This is one alternative to using temporary tables. Create these two
SPs - one for setting the identity value from trigger & another for reading
it.

----- SPs
CREATE PROC SetIdentityVal

)
AS
IF CURSOR_STATUS ('global', 'IdentityVal') >= 0
   DEALLOCATE IdentityVal

GO

CREATE PROC GetIdentityVal

)
AS
IF CURSOR_STATUS ('global', 'IdentityVal') < 0

ELSE
BEGIN

 DEALLOCATE IdentityVal
END
GO
----- SPs

----- Your trigger code:
Create Trigger TABLE1_ITrig On dbo.TABLE1 For Insert
As



-- Other logic here, Inserts into tables without identity columns.
GO

----- Your SP:
CREATE PROCEDURE InsTable1
AS

-- Trigger fires that inserts into other tables....
-- Insert Into Table1 Values(....)


GO

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)

 
 
 

IDENTITY column problems with triggers

Post by Michael Gra » Tue, 12 Sep 2000 09:16:28


FWIW, it is a global function, not a variable.
Pedantic, but true.



The
> following is an excerpt from the SQL Server Books Online:


> variable is accurate after an insert into a table with an identity column;
> however, this value is reset after an insert into a table without an
> identity column occurs.

> Improperly relying on this behavior may quickly cause changes in your
> application. For example, suppose you do an insert into a table and then

administrator
> then adds an insert trigger to the table to perform an insert into the
audit


> This problem may compound itself when you start adding nested triggers or
> stored procedures.

> You can employ the following strategy:

> 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:


>    set identity_insert ##temp_identity_set on
>    delete from ##temp_identity_set

>    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


>    ....(your code here)
>    exec sys_identity_set identity
> return
> go
>  --

> Ajax



> > I have two tables A and B and both use identity columns as primary keys.
> > Table A have an insert trigger in which a an insert into table B is
done.
> > The trouble I have is that when I use ADO to add a new row into A i get
> the
> > identity value of the row insertet into B from A's insert trigger.

> > Is there a work-around for this behaviour?

 
 
 

IDENTITY column problems with triggers

Post by Ivan Arjentinsk » Wed, 13 Sep 2000 03:10:09


:)

Although I would measure this solution as less usable than Ajaxs', it is
amazing for something else.
Actually you are using global cursors as global variables.

So SQL Server is having global variables .... :))
Just declare a cursor for each variable...amazing!

The procs can be easily adjusted to work with dynamic SQL so that they can
handle any variable name.

--
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------


>     Try this solution. I posted this a year back actually :-) Got the post
> from my OE sent box. This is SQL70 version & uses the new CURSOR_STATUS
> function etc. But the code can be used with minimal changes in SQL6x also.
> HTH.

> --
>     This addresses the issue of inserting into other tables in the
trigger.
> One restriction is that you can't nest calls to this SP in other triggers
> like this.

>     Here is what i do. I use a cursor to store the identity value from the
> trigger. Then i can fetch from this cursor after the trigger fires in any
> SP. This is one alternative to using temporary tables. Create these two
> SPs - one for setting the identity value from trigger & another for
reading
> it.

> ----- SPs
> CREATE PROC SetIdentityVal
> (

> )
> AS
> IF CURSOR_STATUS ('global', 'IdentityVal') >= 0
>    DEALLOCATE IdentityVal

> GO

> CREATE PROC GetIdentityVal
> (

> )
> AS
> IF CURSOR_STATUS ('global', 'IdentityVal') < 0

> ELSE
> BEGIN

>  DEALLOCATE IdentityVal
> END
> GO
> ----- SPs

> ----- Your trigger code:
> Create Trigger TABLE1_ITrig On dbo.TABLE1 For Insert
> As



> -- Other logic here, Inserts into tables without identity columns.
> GO

> ----- Your SP:
> CREATE PROCEDURE InsTable1
> AS

> -- Trigger fires that inserts into other tables....
> -- Insert Into Table1 Values(....)


> GO

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> (Please reply only to the newsgroup)