help! @@IDENTITY and nested triggers

help! @@IDENTITY and nested triggers

Post by eotoo » Sat, 25 Jul 1998 04:00:00



Hi.

I have a stored procedure,  the inserts a row into tableX with an
identity column. I need to know the new identity value for this row,
(to insert into another table)


A trigger attached to tableX inserts data into tableY with a different

longer valid for tableX (It's valid for tableY).

Is there any way, within my stored procedure, to get the identity of
the inserted row in tableX?????

Thanks.


 
 
 

help! @@IDENTITY and nested triggers

Post by David Hanle » Sun, 26 Jul 1998 04:00:00


A useful alternative to using IDENTITY is to use a look up table that
contains the next "id" value to insert into your table. For example:

tblSeed
TableName        sysname
NextId                int

Then use the following:


/*    (other parameters) */

begin tran


    from tblSeed (TABLOCKX)        /* ensure no one else can access this
value */
    where tablename = 'tblA'

   insert into tblA (Id,...)


  begin
        rollback tran
        return
  end

   update tblSeed
  set NextId = NextId + 1

  begin
    rollback tran
    return
  end

commit tran                    /* locks released */

Hope this helps

David Hanley.


>Hi.

>I have a stored procedure,  the inserts a row into tableX with an
>identity column. I need to know the new identity value for this row,
>(to insert into another table)


>A trigger attached to tableX inserts data into tableY with a different

>longer valid for tableX (It's valid for tableY).

>Is there any way, within my stored procedure, to get the identity of
>the inserted row in tableX?????

>Thanks.




 
 
 

help! @@IDENTITY and nested triggers

Post by eotoo » Tue, 28 Jul 1998 04:00:00


On Sat, 25 Jul 1998 21:23:53 GMT, in comp.databases.ms-sqlserver you

Quote:>Look into MS KnowledgeBase - they posted workaround for this problem.

Thanks for the tip. I'll look in there.

>The simplest solution (in simplest case of single process
>updating table) is to use

>right after insert.

Beware of this. The following real output shows that this can cause
problems. I've changed only the tablenames and columns

Query:
Select ID, whenX FROM xxxxxxxx ORDER BY whenX
It's pretty definite that when represents chronological order of
inserts

here's a subset of the results of this query
ID          whenX
----------- ---------------------------
..              ...
170         Jul 23 1998 12:10PM        
171         Jul 23 1998  3:40PM        
172         Jul 23 1998  6:39PM        
132         Jul 24 1998  1:37AM        
133         Jul 24 1998  1:45AM  
..              ...

Seems that when you delete rows, new inserts may reuse the deleted
ID's.  I'm not sure why.

Eoin

 
 
 

1. help! nested table, trigger and OR way

Hello Everyone:

Question is following. Any help will be appreciated!

1.
With Oracle8, we have 3 ways to approach the problem. One is relational
approach. Second is object way. Third is O-R way. So, what is the line
between those approaches. It seems much overhead and complex that create
everyting as object table and use object ref to link objects
relationship.

2.
The nested table is well suited to the master-detail case. The nested
table can not have triggers. So, how to use trigger to auto update the
total charge(something like that) in master object table.

Thank you very much!

Tom

2. datetime type

3. Update trigger,nested trigger

4. PFW 5.0 crashes after SQL window -- WHY?

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

6. Tempdb, log & devices (SQL 6.5)

7. insert, @@identity and triggers... HELP

8. References to Informix !

9. Nested Triggers - How avoid loop?

10. Maximum Nested Trigger - what is the parameter name ?

11. Maximum nested triggers - What is the parameter name ?

12. Raiserror does not report error in VB6 but transaction rolled back, nested SP and Triggers

13. Nested Trigger problem