Very Computer

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

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

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

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

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

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

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

Very Computer

problem with @@identity and trigger

problem with @@identity and trigger

problem with @@identity and trigger

problem with @@identity and trigger

problem with @@identity and trigger

problem with @@identity and trigger

problem with @@identity and trigger

problem with @@identity and trigger