Okay, I just got burned by an @@Identity bug in SQL Server. What is that?
I had a written a spec that made it necessary for the developer to create a row in a database and return the ID to the application, then this ID would trigger an email to be sent in the site.
Suddenly it breaks, why? Check out the @@Identity anti-pattern
CREATE TABLE main
(
id INT IDENTITY(1, 1),
name VARCHAR(50)
)
CREATE TABLE audit
(
id INT IDENTITY(1, 1),
name VARCHAR(50)
)
INSERT INTO main (name) VALUES ('Hello')
SELECT 'Number 1',@@IDENTITY
INSERT INTO main (name) VALUES ('World')
SELECT 'Number 2',@@IDENTITY
GO
Output is correctly showing "Number 1, 1", "Number 2, 2"
So a new development requires the data to be transformed for some fast access audit tables elsewhere in the site. So we put in a trigger to deal with this new requirement.
CREATE TRIGGER trig
ON main
FOR INSERT
AS
INSERT INTO audit
(name)
VALUES ('Please ignore this item')
GO
INSERT INTO main (name) VALUES ('ID number 3')
SELECT 'Number 3', @@IDENTITY
Output is "Number 3, 1" - not "Number 3, 3", WHY? Well the @@Identity function returns the Last inserted Identity. Because the trigger has caused another ID to be inserted in the database before the next line is run.
INSERT INTO main (name) VALUES ('ID number 4')
SELECT 'I expect ID number 4',Scope_identity()
Output is "Number 4, 4". Scope_identity only includes the last identity from the scope you are running in. I fixed the rouge procedure, and then ran the excellent
SQL Cop tool which is a small executable that searches your database to find common SQL issues. We had another 78 procedures in the database using the bad Identity syntax. Oh well, long evening for me, and a nice educational email to all the Devs.
You know - I cannot think of a reason when you would want to get the ID of a value outside the scope, sounds like some crazy coding if you ask me!
Now cleanup after yourself.
DROP TRIGGER trig
DROP TABLE main
DROP TABLE audit