trigger stealing my @@IDENTITY!

trigger stealing my @@IDENTITY!

Post by Travis Labord » Sun, 14 Apr 2002 03:48:54



Hello, I'll post this to the MS SQL Server group as well, but I'm
wondering if this same behaviour perhaps affects other databases as
well?  I'm really only used to MS Access and SQL Server.

I have a stored procedure which does a pretty standard insert into

corresponding entry in Table B.


return the most recently added record id when you have a table with
what is called an IDENTITY column ( like an auto-number in Access).

So I create an entry in the "main" table, and I use it's key to create
an entry in the "subtable" that refers back to that entry in the main
table, doing it all in one stored procedure.

That works fine, and is used often, it's quite common.

But, now I've added a trigger to table A, so that any time a record is
added to that table, the trigger fires, and it creates an entry in a
totally unrelated table (C).

My problem is that this trigger's insert statement basically causes

procedure gets a chance to save it for later use!  So, the second half
of the original stored procedure fails, or produces bad data!

Can anyone help me with this?
Thanks

 
 
 

trigger stealing my @@IDENTITY!

Post by Philipp Stiefe » Mon, 15 Apr 2002 12:33:34



> I have a stored procedure which does a pretty standard insert into

> corresponding entry in Table B.


> return the most recently added record id when you have a table with
> what is called an IDENTITY column ( like an auto-number in Access).

[...]

> But, now I've added a trigger to table A, so that any time a record is
> added to that table, the trigger fires, and it creates an entry in a
> totally unrelated table (C).

> My problem is that this trigger's insert statement basically causes

> procedure gets a chance to save it for later use!  So, the second half
> of the original stored procedure fails, or produces bad data!

If you are using SQL-Server 2000, you can get the identity-value
with the SCOPE_IDETITY()-Function. It'll return only identity-
values generated in the stored procedure, not those generated in
the trigger.

ciao
Phil

 
 
 

1. # of page steal cleaner will trigger page threshold?

Hello,
Is it db2 the same thing like informix in this way when a couple of
dirty page steal cleaner triggers happen, db2 will trigger dirty page
threshold cleaner trigger, even the dirty pages in dirty queue have
touched the page threshold???

From my test, it looks like db2 does not use this way. But I am not
sure. If db2 use is like this, how many number of dirty page steal
cleaner trggers will cause the dirty page threshold cleaner trigger???

Best Regards,
Fan Ruo Xin

2. Help with Function

3. Trigger problem w/ @@Identity...Please Help!!

4. OLE DB for Oracle on Unix

5. Passing identity values outside triggers

6. VB4/16 and SQLServer Image columns/Access

7. help! @@IDENTITY and nested triggers

8. TN-NASHVILLE-32987--ORACLE-Architecture Design-DB2-Overtime Required-EDI-TSI exc

9. triggers and @@Identity vs. SCOPE_IDENTIY()

10. Trigger is causing my SP to return the wrong @@IDENTITY

11. Problem with @@identity and trigger

12. @@identity value in a stored prc and a trigger