Last week I saw yet another post proposing that you always use SCOPE_IDENTITY to return the value of the last identity column inserted into table. Unfortunately what these posts often ignore is that as well as AFTER triggers, there are also INSTEAD of triggers; and in this situation the advice given may not be correct. If you have never used INSTEAD of triggers I will re-phrase books online: “INSTEAD OF triggers can be defined on a view or table to replace the standard action of the triggering statement.”
Most articles will show a situation when @@IDENTITY does not return the correct value for the identity value inserted into the table. In this example I’ve also included what can be returned using OUTPUT parameters which is often ignored by articles that talk about methods of returning identity values:
USE tempdb
GO
SET NOCOUNT ON
;
GO
CREATE TABLE TriggerTable (
ID INT
IDENTITY(100,1) NOT NULL,
Description NVARCHAR(100));
GO
CREATE TABLE AlternateTriggerTable (
ID INT
IDENTITY(100,1) NOT NULL,
[Description] NVARCHAR(100));
GO
DECLARE @ID TABLE ( id int ) ;
INSERT INTO
TriggerTable ( [Description]
)
OUTPUT INSERTED.id
INTO @id
VALUES ( 'Expected identity' ) ;
SELECT @@IDENTITY
AS [@@IDENTITY],
SCOPE_IDENTITY()
AS [SCOPE_IDENTITY],
IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT],
ID
FROM @id ;
GO
/*
@@IDENTITY SCOPE_IDENTITY IDENT_CURRENT ID
---------- -------------- ------------- -----------
100 100 100 100
*/
SELECT *
FROM TriggerTable ;
GO
/*
ID Description
---- ------------------
100 Expected identity
*/
CREATE TRIGGER
InsTrigger ON TriggerTable
AFTER INSERT AS
INSERT INTO TriggerTable VALUES ('Additional Data');
GO
DECLARE @ID TABLE ( id int ) ;
INSERT INTO
TriggerTable ( [Description]
)
OUTPUT INSERTED.id
INTO @id
VALUES ( 'Unexpected Values' ) ;
SELECT @@IDENTITY
AS [@@IDENTITY],
SCOPE_IDENTITY()
AS [SCOPE_IDENTITY],
IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT],
ID
FROM @id ;
GO
/*
@@IDENTITY SCOPE_IDENTITY IDENT_CURRENT
ID
----------- --------------- -------------- ----
102 101 102 101
*/
SELECT *
FROM TriggerTable ;
GO
/*
ID Description
---- ------------------
100 Expected identity
101 Unexpected Values
102 Additional Data
*/
DROP TRIGGER
insTrigger ;
GO
CREATE TRIGGER InsTrigger ON TriggerTable
AFTER INSERT AS
INSERT INTO AlternateTriggerTable VALUES ('Alternate Trigger Data');
GO
DECLARE @ID TABLE ( id int ) ;
INSERT INTO
TriggerTable ( [Description]
)
OUTPUT INSERTED.id
INTO @id
VALUES ( 'Expected @@Identity' )
;
SELECT @@IDENTITY
AS [@@IDENTITY],
SCOPE_IDENTITY()
AS [SCOPE_IDENTITY],
IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT],
ID
FROM @id ;
GO
/*
@@IDENTITY SCOPE_IDENTITY IDENT_CURRENT
ID
----------- --------------- -------------- ----
100 103 103 103
*/
SELECT *
FROM TriggerTable ;
GO
/*
ID Description
---- --------------------
100 Expected identity
101 Unexpected Values
102 Additional Data
103 Expected @@Identity
*/
SELECT *
FROM AlternateTriggerTable ;
GO
/*
ID Description
---- -----------------------
100 Alternate Trigger Data
*/
DROP TRIGGER
insTrigger ;
GO
In the following examples with an AFTER trigger @@IDENTITY will return the expected value and SCOPE_IDENTITY returns NULL instead.
/* Effect of INSTEAD of triggers */
CREATE TRIGGER insTrigger ON TriggerTable
INSTEAD OF INSERT AS
BEGIN
SELECT
* FROM inserted
;
INSERT
INTO TriggerTable ( [Description] )
SELECT
[Description] + ' -
Adjusted' FROM INSERTED ;
END
GO
DECLARE @ID TABLE ( id int ) ;
INSERT INTO
TriggerTable ( [Description]
)
OUTPUT INSERTED.id
INTO @id
VALUES ( 'Unexpected Scope_Identity' )
;
SELECT @@IDENTITY
AS [@@IDENTITY],
SCOPE_IDENTITY()
AS [SCOPE_IDENTITY],
IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT],
ID
FROM @id ;
GO
/*
ID Description
--- --------------------------
0 Unexpected Scope_Identity
@@IDENTITY SCOPE_IDENTITY
IDENT_CURRENT ID
---------- --------------- -------------- ----
104 NULL 104 0
*/
SELECT *
FROM TriggerTable ;
GO
/*
ID Description
---- -------------------------------------
100 Expected identity
101 Unexpected Values
102 Additional Data
103 Expected @@Identity
104 Unexpected Scope_Identity - Adjusted
*/
DROP TRIGGER
insTrigger ;
GO
So if you want a more robust code pattern to get the last identity value you may want a construct something similar to:
SET @NewIdentity = ISNULL(SCOPE_IDENTITY(),@@IDENTITY) ;
This would cope with the scenario where the value inserted into the identity column is overridden.
CREATE TRIGGER insTrigger ON
TriggerTable
INSTEAD OF INSERT AS
BEGIN
SET
IDENTITY_INSERT TriggerTable ON ;
SELECT
* FROM inserted
;
INSERT
INTO TriggerTable ( ID, [Description] )
SELECT
-1,
[Description] FROM INSERTED ;
SET
IDENTITY_INSERT TriggerTable OFF ;
END
GO
DECLARE @ID TABLE ( id int ) ;
INSERT INTO
TriggerTable ( [Description]
)
OUTPUT INSERTED.id
INTO @id
VALUES ( 'Unexpected IDENT_CURRENT' )
;
SELECT @@IDENTITY
AS [@@IDENTITY],
SCOPE_IDENTITY()
AS [SCOPE_IDENTITY],
IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT],
ID
FROM @id ;
GO
/*
ID Description
--- -------------------------
0 Unexpected IDENT_CURRENT
@@IDENTITY SCOPE_IDENTITY IDENT_CURRENT ID
---------- --------------- --------------- ---
-1 NULL 104 0
*/
SELECT *
FROM TriggerTable ;
GO
/*
ID Description
---- -------------------------------------
100 Expected identity
101 Unexpected Values
102 Additional Data
103 Expected @@Identity
104 Unexpected Scope_Identity - Adjusted
-1 Unexpected IDENT_CURRENT
*/
DROP TRIGGER
insTrigger ;
GO
But the code pattern may be inadequate if the INSTEAD of trigger subsequently inserts data into tables that do also have identity columns e.g.
CREATE TRIGGER
insTrigger ON
TriggerTable
INSTEAD OF INSERT AS
BEGIN
SELECT
* FROM inserted
;
INSERT
INTO AlternateTriggerTable ( [Description] )
SELECT
[Description] FROM INSERTED ;
END
GO
DECLARE @ID TABLE ( id int ) ;
INSERT INTO
TriggerTable ( [Description]
)
OUTPUT INSERTED.id
INTO @id
VALUES ( 'Unexpected everything!' )
;
SELECT @@IDENTITY
AS [@@IDENTITY],
SCOPE_IDENTITY()
AS [SCOPE_IDENTITY],
IDENT_CURRENT('TriggerTable') AS [IDENT_CURRENT],
ID
FROM @id ;
GO
/*
ID Description
--- -----------------------
0 Unexpected everything!
@@IDENTITY SCOPE_IDENTITY
IDENT_CURRENT ID
---------- --------------- -------------- ---
101 NULL 104 0
*/
SELECT *
FROM TriggerTable ;
GO
/*
ID Description
--- -------------------------------------
100 Expected identity
101 Unexpected Values
102 Additional Data
103 Expected @@Identity
104 Unexpected Scope_Identity - Adjusted
-1 Unexpected IDENT_CURRENT
*/
SELECT *
FROM AlternateTriggerTable ;
GO
/*
ID Description
---- -----------------------
100 Alternate Trigger Data
101 Unexpected everything!
*/
DROP TABLE
AlternateTriggerTable ;
DROP TABLE
TriggerTable ;
In my previous article I showed how the OUTPUT parameter can be used to get GUID values inserted into a table using a default. From the above code example you can also see that triggers may also cause problems for this method as well. For example:
CREATE TABLE NewTriggerTable (
id int
not null identity,
guidid uniqueidentifier not null default NEWSEQUENTIALID()
) ;
DECLARE @ID TABLE
( id int, guidid uniqueidentifier
) ;
INSERT INTO
NewTriggerTable
OUTPUT INSERTED.id, INSERTED.guidid INTO @id
DEFAULT VALUES ;
SELECT *
FROM @id ;
GO
/*
id guidid
--- ------------------------------------
1 FFBA6AB8-BD75-E011-A8A1-001E377AFE56
*/
SELECT *
FROM NewTriggerTable ;
GO
/*
id guidid
--- ------------------------------------
1 FFBA6AB8-BD75-E011-A8A1-001E377AFE56
*/
CREATE TRIGGER
InsTrigger ON NewTriggerTable
AFTER INSERT AS
SELECT * FROM
inserted ;
UPDATE A
SET guidid = '00000000-0000-0000-0000-000000000000'
FROM NewTriggerTable A
JOIN inserted I ON
a.id = i.id ;
GO
DECLARE @ID TABLE ( id int, guidid uniqueidentifier
) ;
INSERT INTO
NewTriggerTable
OUTPUT INSERTED.id, INSERTED.guidid INTO @id
DEFAULT VALUES ;
SELECT *
FROM @id ;
GO
/*
id guidid
--- ------------------------------------
2 FB3039D1-BD75-E011-A8A1-001E377AFE56
id guidid
--- ------------------------------------
2 FB3039D1-BD75-E011-A8A1-001E377AFE56
*/
SELECT *
FROM NewTriggerTable ;
GO
/*
id guidid
--- ------------------------------------
1 FFBA6AB8-BD75-E011-A8A1-001E377AFE56
2 0000000-0000-0000-0000-000000000000
*/
DROP TRIGGER
InsTrigger ;
GO
CREATE TRIGGER InsTrigger ON NewTriggerTable
INSTEAD OF INSERT AS
SELECT * FROM
inserted ;
INSERT INTO
NewTriggerTable (
guidid ) VALUES ('00000000-0000-0000-0000-000000000000');
GO
DECLARE @ID TABLE ( id int, guidid uniqueidentifier
) ;
INSERT INTO
NewTriggerTable
OUTPUT INSERTED.id, INSERTED.guidid INTO @id
DEFAULT VALUES ;
SELECT *
FROM @id ;
GO
/*
id guidid
--- ------------------------------------
0 D8CBF71C-BE75-E011-A8A1-001E377AFE56
id guidid
--- ------------------------------------
0 D8CBF71C-BE75-E011-A8A1-001E377AFE56
*/
SELECT *
FROM NewTriggerTable ;
GO
/*
id guidid
----------- ------------------------------------
1
FFBA6AB8-BD75-E011-A8A1-001E377AFE56
2
00000000-0000-0000-0000-000000000000
3
00000000-0000-0000-0000-000000000000
*/
DROP TABLE
NewTriggerTable ;
Triggers have traditionally been held up as a means of extending the functionality of a system without changing the system itself. Hopefully this article has shown that this may not be the case, in fact the consequence of adding a trigger without knowing the impact the trigger can have could be quite dangerous.