On Thu, 17 Mar 2005 22:57:45 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.se> wrote:
(br********@gma il.com) writes: insert into <table>;
select @@identity;
This doesn't work because the select @@identity might give me the value
of an insert from someone else's request.
No, @@identity is local to the connection, so it cannot be someone
else's value. Well, if you submit to queries and close your connection
in between, it won't work, but that would be poor practice anyway.
Hugo's suggestion of using a stored procedure is an excellent idea.
Excuse me for butting in here, Erland, but there is one 'little'
problem that I have found with @@IDENTITY that I can't see referred to
anywhere, and that anyone relying on it should know about, and that is
that @@IDENTITY can return unexpected values in certain circumstances.
In the supplied example:
insert into <table>
select @@identity
BEAWRE!
If there is a trigger fired during the insert on <table>, and the
trigger performs an insert itself, then @@IDENTITY will return the ID
from the Trigger's insert, not the <table> insert.
This caused me many to lose much more hair than I can afford!
It behaves this way in SQL Server 7, and 2000.
Here is a script to create a test data base:
(Make a new blank database, I called it "Test")
=============== ==============
/****** Object: Table [dbo].[MainTable] Script Date: 18/03/2005
3:10:38 PM ******/
CREATE TABLE [dbo].[MainTable] (
[MainTableId] [int] IDENTITY (1, 1) NOT NULL ,
[LongName] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TriggerTable] Script Date: 18/03/2005
3:10:39 PM ******/
CREATE TABLE [dbo].[TriggerTable] (
[TriggerTableId] [int] IDENTITY (666, 1) NOT NULL ,
[TriggerRowLongN ame] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TriggerTable] WITH NOCHECK ADD
CONSTRAINT [PK_TriggerTable] PRIMARY KEY CLUSTERED
(
[TriggerTableId]
) ON [PRIMARY]
GO
/****** Object: Stored Procedure dbo.Test_sp Script Date:
18/03/2005 3:10:39 PM ******/
CREATE PROCEDURE dbo.Test_sp
AS
INSERT INTO MainTable (LongName) VALUES ('TestLongName' )
SELECT @@IDENTITY
GO
/****** Object: Trigger dbo.MainTable_T rigger1 Script Date:
18/03/2005 3:10:39 PM ******/
CREATE TRIGGER MainTable_Trigg er1
ON dbo.MainTable
FOR INSERT,UPDATE,D ELETE
AS
INSERT INTO TriggerTable (TriggerRowLong Name) VALUES ('Stuff')
GO
=============== ==============
Then, if one executes [Test_sp] in Query Analyser,
EXEC Test_sp
the returned @@IDENTITY is not 1, as you would expect, (this is ID of
the new MainTable row), but 666, which is the ID of the row inserted
via the trigger!
(I seeded this table's identity to begin at 666, in order to show up
clearly)
I would be interested if you were aware of this tiny problemette.