Problem with @@identity and trigger

Problem with @@identity and trigger

Post by Jaco » Sun, 16 Jun 2002 03:42:18



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

Jacob

 
 
 

Problem with @@identity and trigger

Post by Anith Se » Sun, 16 Jun 2002 03:56:03


Since you are using SQL 7.0 you cannot use IDENT_CURRENT.
A workaround would be, getting the newly inserted key
using a




- Anith

>-----Original Message-----
>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

>Jacob

>.


 
 
 

Problem with @@identity and trigger

Post by linda deng[M » Wed, 19 Jun 2002 16:30:59


Hi Jacob,

        Please try the sample code below.

        --Create the testing table
        CREATE TABLE t6(id int IDENTITY)
        CREATE TABLE t7(id int IDENTITY(100,1))

        --Create the testing trigger as this:
        CREATE TRIGGER t6ins ON t6 FOR INSERT
        AS
        BEGIN

                INSERT t7 DEFAULT VALUES
        END

        --Insert value into t6 and fire the trigger
        INSERT t6 DEFAULT VALUES

        You can get last identity value of table t6 by adding one statement before
the insert operation on t7 in the trigger.

        BTW: Microsoft SQL Server 2000 supports another two functions:

expected result by utilizing the statement 'SELECT SCOPE_IDENTITY()' or
'SELECT IDENT_CURRENT('t6')' after the insert operation on table t6.

Sincerely,

Linda Deng
Microsoft Support Engineer

This posting is provided "AS IS" with no warranties, and confers no rights.