SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Alternatives to @@IDENTITY in SQL Server 2000		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 08/08/2000 :  17:46:37  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Mark writes: "I was asked a question today by a developer having problems getting the right identity value from @@identity when a table has a trigger which has an additional insert - Post the insert statement the select @@identity returns the wrong value (which is behaviour I would expect).

Is there a trick to get round this, apart from not using triggers and/or not using identity columns - which is what I suggested ..."

Prior to SQL Server 2000, the answer was "no". However, SQL Server 2000 adds two cool new functions to help you get around this problem. Read on...

Article Link.

Henrysu
Starting Member

1 Posts
Posted - 06/24/2002 :  16:31:39  Show Profile  Reply with Quote
Hi,

It's nice to see your article, I have another question, can I get the last result regarding to the specified table and only on my connection? cause I have a table, I wanna know the last identity I have inserted, it sounds no way to do that.

Henry
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4128 Posts
Posted - 06/24/2002 :  17:13:26  Show Profile  Visit graz's Homepage  Reply with Quote
This article should answer your question (http://www.sqlteam.com/item.asp?ItemID=8003).

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

dim1r
Starting Member

Russia
1 Posts
Posted - 07/25/2002 :  07:07:40  Show Profile  Reply with Quote
I use batch updates/inserts in my program so I need to generate unique values prior insertions.

How can create a procedure which will return new incremental numbers after every calls for all sessions and transaction ?

I know that there are special objects in oracle and interbase servers.
Go to Top of Page

versaggi
Starting Member

1 Posts
Posted - 05/28/2003 :  22:32:09  Show Profile  Reply with Quote
Wonderful article! A big help....kudos!
Go to Top of Page

rawheiser
Starting Member

1 Posts
Posted - 09/07/2004 :  13:45:23  Show Profile  Visit rawheiser's Homepage  Send rawheiser an AOL message  Send rawheiser a Yahoo! Message  Reply with Quote
How to reset @@IDENTITY within a trigger in SQL 7.0 .....
CREATE TRIGGER dbo.tI_Application
ON dbo.Application
FOR INSERT AS

-- SAVE AT AT IDENTITY
DECLARE @IV INT
SET @IV = @@IDENTITY

--<<< Insert Statements That Reset @@Identity go Here >>>--

-- RESET AT AT IDENTITY
DECLARE @SQL VARCHAR(255)
SET @SQL = 'SELECT IDENTITY( int, ' + CONVERT( VARCHAR(10) , @IV ) + ' , 1 ) AS IDVal INTO #IVSTACK '
EXEC ( @SQL )
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 09/07/2004 :  13:49:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by rawheiser

How to reset @@IDENTITY within a trigger in SQL 7.0 .....


DBCC CHECKIDENT

Tara
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000