problem with @@identity and trigger

problem with @@identity and trigger

Post by Jaco » Sun, 16 Jun 2002 03:43:43



Hi

We run under SQL7

I insert Data into table A
An insert trigger exist for this table
This trigger insert into an historic table


I got the ID of the insert of Historic table

what can I do ?

thanks

 
 
 

problem with @@identity and trigger

Post by Eric Newto » Sun, 16 Jun 2002 04:48:17


here's a shot in the dark, but here goes


the trigger finishes, ie:

create trigger... blahblah
begin


    insert into historic ( data1, data2 ) select data1, data2 from inserted

end

i have no idea if that'll work

only other option i can think of would be to make that a business rule and
utilitize Distributed Tx's

--
Eric Newton
.net, ASPX Developer


> Hi

> We run under SQL7

> I insert Data into table A
> An insert trigger exist for this table
> This trigger insert into an historic table


> I got the ID of the insert of Historic table

> what can I do ?

> thanks


 
 
 

problem with @@identity and trigger

Post by Jeff Dillo » Sun, 16 Jun 2002 05:43:41


IDENT_CURRENT (I'm looking at SQL2000 BOL)


> Hi

> We run under SQL7

> I insert Data into table A
> An insert trigger exist for this table
> This trigger insert into an historic table


> I got the ID of the insert of Historic table

> what can I do ?

> thanks

 
 
 

problem with @@identity and trigger

Post by Anith Se » Sun, 16 Jun 2002 05:53:59



- Anith


> here's a shot in the dark, but here goes


> the trigger finishes, ie:

> create trigger... blahblah
> begin


>     insert into historic ( data1, data2 ) select data1, data2 from
inserted

> end

> i have no idea if that'll work

> only other option i can think of would be to make that a business rule and
> utilitize Distributed Tx's

> --
> Eric Newton
> .net, ASPX Developer



> > Hi

> > We run under SQL7

> > I insert Data into table A
> > An insert trigger exist for this table
> > This trigger insert into an historic table


> > I got the ID of the insert of Historic table

> > what can I do ?

> > thanks

 
 
 

problem with @@identity and trigger

Post by Doo » Sun, 16 Jun 2002 06:14:40


Don't use IDENT_CURRENT.  It IS NOT scoped to the Session so you get the
last ID *anybody* inserted.  So if another session INSERTS between your
INSERT and your IDENT_CURRENT call you get *their* ID for the table
specified.

User SCOPE_IDENTITY.  It gives you the last ID you generated via INSERT

Doo


> IDENT_CURRENT (I'm looking at SQL2000 BOL)



> > Hi

> > We run under SQL7

> > I insert Data into table A
> > An insert trigger exist for this table
> > This trigger insert into an historic table


> > I got the ID of the insert of Historic table

> > what can I do ?

> > thanks

 
 
 

problem with @@identity and trigger

Post by Mike Joh » Sun, 16 Jun 2002 06:07:28


I think you mean SCOPE_IDENTITY(), at that was not iun SQL 7 - sorry. Known
problem at 7, avoid triggers inserting into tables with identity property to
work round it.  - does the historic table really need an identity colimn -
from the name (some guesswork here) it sounds like a second table being a
copy of stuff from table A so maybe it would be best to lose the identity on
the second table.

Mike John


> IDENT_CURRENT (I'm looking at SQL2000 BOL)



> > Hi

> > We run under SQL7

> > I insert Data into table A
> > An insert trigger exist for this table
> > This trigger insert into an historic table


> > I got the ID of the insert of Historic table

> > what can I do ?

> > thanks

 
 
 

problem with @@identity and trigger

Post by Doo » Sun, 16 Jun 2002 06:31:32


Try this:

In the SP *before* the INSERT do:

In the trigger *before* any INSERT do:

In the SP *after* the INSERT (and any other statements) do either or both of
these:



> I think you mean SCOPE_IDENTITY(), at that was not iun SQL 7 - sorry.
Known
> problem at 7, avoid triggers inserting into tables with identity property
to
> work round it.  - does the historic table really need an identity colimn -
> from the name (some guesswork here) it sounds like a second table being a
> copy of stuff from table A so maybe it would be best to lose the identity
on
> the second table.

> Mike John



> > IDENT_CURRENT (I'm looking at SQL2000 BOL)



> > > Hi

> > > We run under SQL7

> > > I insert Data into table A
> > > An insert trigger exist for this table
> > > This trigger insert into an historic table


> > > I got the ID of the insert of Historic table

> > > what can I do ?

> > > thanks