I need to retrieve the identity field generated by SQL Server 2005. Usually I would just use SCOPE_IDENTITY or add an OUTPUT CLAUSE to the insert, however neither of these method help in this situation: as there is an INSTEAD OF trigger attached to the table. Next I considered @@IDENTITY, but as there is also another trigger attached to the table, this is no good either. So I figured I'd have to use IDENT_CURRENT. However as this is not session safe I need some way to ensure no other session can insert into the table until I have retrieved the new ID. I've experimented with using the TABLOCK and HOLDLOCK hints, this seems to work, but I'm not a DBA. So my question is, will using the TABLOCK and HOLDLOCK hints in SQL Server 2005 COMPLETELY prevent inserts until the end of the transaction?
Hopefully this contrived example will explain the situation. (I realise that, functionally, a stored procedure could replace both the triggers in this case.)
CREATE TABLE Person
(
PersonID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(50)
);
GO
CREATE TABLE PersonHistory
(
PersonHistoryID INT IDENTITY(5,1) PRIMARY KEY,
PersonId INT,
FirstName VARCHAR(50)
);
GO
CREATE TRIGGER PersonAudit ON Person
AFTER Insert
AS
INSERT INTO PersonHistory (PersonID, FirstName)
SELECT Inserted.PersonID, Inserted.FirstName
FROM Inserted;
GO
CREATE TRIGGER PersonCleaner ON Person
INSTEAD OF Insert
AS
INSERT INTO Person (FirstName)
SELECT UPPER(Inserted.FirstName)
FROM Inserted;
GO
BEGIN TRAN;
INSERT INTO Person WITH (TABLOCK, HOLDLOCK) (FirstName)
VALUES ('George');
--SELECT @@IDENTITY AS '@@IDENTITY'; -- 5
--SELECT IDENT_CURRENT('Person') AS 'IDENT_CURRENT'; -- 1
--SELECT SCOPE_IDENTITY() AS 'SCOPE_IDENITIY'; -- NULL
DECLARE @PersonID int;
SELECT @PersonID = IDENT_CURRENT('Person');
SELECT @PersonID; -- 1
COMMIT TRAN;
--SELECT * FROM Person;
--SELECT * FROM PersonHistory;
DROP TABLE Person;
DROP TABLE PersonHistory;