TRIGGERS, IDENTITY, INSERTED and OUTPUT

Published 03 May 11 08:55 PM | MartinBell

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.

Comments

# Dew Drop – May 4, 2011 | Alvin Ashcraft's Morning Dew said on May 4, 2011 01:45 PM:

Pingback from  Dew Drop – May 4, 2011 | Alvin Ashcraft's Morning Dew

This Blog

SQL Blogs

Syndication