identity and triggers

Emil Damian

Delphi Developer

Sat, 27 Sep 2003 18:22:50 GMT

identity and triggers

I'm trying to get an identity value from server(MSSQL7), after an insert
operation in a table T1. A trigger on table T1 will also insert a record in
a log table T2. The log table also have an identity field.
The @@IDENTITY value contains the last generated value from the T2 table.
I need the last generated value from the T1 table.

What can I do?

Thank you

Andy Macki

Delphi Developer

Sat, 27 Sep 2003 21:15:02 GMT

Re:identity and triggers

Emil,

Options include:

1. Upgrade to SQLServer 2000 and use SCOPE_IDENTITY which addresses this
issue.
2. Use this SQLServer 7.0 workaround.
http://support.microsoft.com/support/kb/articles/Q163/4/46.ASP
3. Don't insert into the audit table in the trigger. Use a stored procedure
to update table T1 then insert into table T2.

Cheers,
Andy Mackie

Quote

Emil Damian <emil.dam...@usa.net> wrote in message news:3ad2de6f_1@dnews...
> Hi

> I'm trying to get an identity value from server(MSSQL7), after an insert
> operation in a table T1. A trigger on table T1 will also insert a record
in
> a log table T2. The log table also have an identity field.
> The @@IDENTITY value contains the last generated value from the T2 table.
> I need the last generated value from the T1 table.

> What can I do?

> Thank you

Chris Mille

Delphi Developer

Sun, 28 Sep 2003 21:47:07 GMT

Re:identity and triggers

Look for the messages under the subject "MSSQL Triggers and
@@IDENTITY" in the borland.public.delphi.database.sqlservers group.

On Tue, 10 Apr 2001 13:22:50 +0300, "Emil Damian"

Quote

<emil.dam...@usa.net> wrote:
>Hi

>I'm trying to get an identity value from server(MSSQL7), after an insert
>operation in a table T1. A trigger on table T1 will also insert a record in
>a log table T2. The log table also have an identity field.
>The @@IDENTITY value contains the last generated value from the T2 table.
>I need the last generated value from the T1 table.

>What can I do?

>Thank you

Chris Miller
VersaTrans Solutions, Inc.

Emil Damia

Delphi Developer

Sun, 28 Sep 2003 16:01:15 GMT

Re:identity and triggers

Thank you, Andy.

I think I need an upgrade.

Other Threads

1. ADO, insert in trigger and identity

2. @@IDENTITY and Insert Triggers

3. MSSQL Triggers and @@IDENTITY

4. MSSQL Trigger and @@IDENTITY

5. MS SQL 6.5 Identity columns and triggers

6. How to get Publisher identity and Subscribers identity

7. Triggering OnDoubleClick Event without triggering OnClick Event

8. Triggers, TRIGGERS? Helpppppppppppppppppppppppppp

9. Converting (IBase)Trigger to MS SQL Trigger

10. Unknown trigger in RDB$Triggers (Cross post)