Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Record ID generator question
Message
From
29/01/2002 14:45:33
 
 
To
29/01/2002 13:05:56
Jason Dalio
Northern Interior Regional Health Board
Prince George, British Columbia, Canada
General information
Forum:
Microsoft SQL Server
Category:
Database design
Miscellaneous
Thread ID:
00605839
Message ID:
00612382
Views:
28
Under SQL Server 2000, I STRONGLY recommend that you use SCOPE_IDENTIY().

Try the following:
CREATE TABLE a (a int IDENTITY(1,2))
CREATE TABLE b (b int IDENTITY(2,2))
GO

CREATE TRIGGER InsertA ON a FOR INSERT
AS
INSERT INTO b DEFAULT VALUES
GO

INSERT INTO a DEFAULT VALUES
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
While @@IDENTITY is scoped to the connection, it is not limited to the session. After running the above script, you should see that @@IDENTITY contains a two (2) which was the value inserted into the b table. Probably not what you were expecting.

-Mike
Michael Levy
MCSD, MCDBA
ma_levy@hotmail.com
Previous
Reply
Map
View

Click here to load this message in the networking platform