Fullscreen
Loading...
 
[Show/Hide Left Column]
[Show/Hide Right Column]

(Cached)
Refresh Print

Scope_Identity in SQL Server with nested and INSTEAD OF triggers

There is a problem getting the last inserted identity from SQL Server in an ODBC/native driver when triggers are involved and with prepared statements having bound arguments.

The short summary:

  • Use SET NOCOUNT ON in stored procedures, especially if they are being used as triggers
  • In INSTEAD OF Triggers for INSERTS:
    • Add the statement SELECT SCOPE_IDENTITY(); to the end of an INSTEAD OF trigger. This will return a result set with the correct identity. If multiple inserts are done, it is the responsibility of the trigger author to save the correct one and return that value with a SELECT statement.
    • Add SET NOCOUNT ON to each trigger to prevent addition of other result sets that will cause the wrong value to be interpreted as the returned identity.

The long story (for DBAs and advanced database programmers)

At this point, and subject to internal and customer testing, we are able to return the newly inserted identity in SQL::Connection::CallResult::LastInsertedIdentity for:
  • INSERT statements with literal values
  • INSERT statements with bound values
  • INSERT statements with triggers that do inserts, including nested triggers
  • INSERT statements with INSTEAD OF triggers subject to the last executed SQL statement in the trigger being SELECT SCOPE_IDENTITY();

Customers should not care about most of this unless they are using INSTEAD OF triggers. That said, anyone writing triggers should be encouraged to use SET NOCOUNT ON in stored procedures.

What Customers Need To Do to Deal With INSTEAD OF Triggers for INSERTS (See below for more detail):

  • Add the statement SELECT SCOPE_IDENTITY(); to the end of an INSTEAD OF trigger.. This will return a result set with the correct identity. If multiple inserts are done, it is the responsibility of the trigger author to save the correct one and return that value with a SELECT statement.
  • Add SET NOCOUNT ON to each trigger to prevent addition of other result sets that will cause the wrong value to be interpreted as the returned identity.

The Problem in Detail

Three Ways of Retrieving Identities in SQL Server

SQL Server provides three ways to retrieve the most recent identity inserted into a table. Unfortunately there are several scenarios where the value returned is not what is expected and is not available after the call.

Identity ValueContains/ReturnsIssues
@@IDENTITYThe last identity inserted into any table in the current session.You can't specify the table. Nested triggers change the value.
IDENT_CURRENT('tablename')The last identity inserted into the table requested from any session.You can't specify the session. Other users may change the value by doing another insert between the time you do the insert and the time you request the value.
SCOPE_IDENTITY()The last identity inserted into any table in the current scope.Each procedure has its own scope. Prepared statements create an additional scope that goes away when the statement completes, causing scope_identity() to return NULL.


Note: Many of the issues discussed above can be dealt with in stored procedures and in writing special client code. The problem for tools like Alpha Five is that we do not know at execution time whether triggers have been attached, what type of triggers are attached, and whether they do nested inserts. We have to treat the situation generically.
With the exception of INSTEAD OF triggers (discussed below), Microsoft recommends the following resolution, although we had to try a number of things out to get it right:
1. Prepare statements in the usual way.
2. Instead of using the ODBC function call SQLExecute(), invoke SQLExecDirect() passing it the original query with ;SELECT SCOPE_IDENTITY() concatenated onto the original query.
3. After the call, fetch through the result sets, attempting to retrieve the first column of the first row of each result set until there are no more result sets. The last result set will have the contents of the SELECT SCOPE_IDENTITY() from the executed with the insert.

Note: SET NOCOUNT OFF will result in any nested triggers returning a result set with counts, so it is necessary to skip over these result sets.

INSTEAD OF Triggers

INSTEAD OF triggers pose a special problem because they execute the actual insert in a nested scope rather than that of the prepared statement. As a result, the only way to get an identity back is for the INSTEAD OF trigger to return one.

The trigger below does an insert into the affected table, which then fires a nested trigger that inserts into another table. After the insert executes:
  • @@IDENTITY contains the identity of the last child inserted.
  • Scope_Identity() at the outer scope will return NULL. This is because the insert is done from within a trigger and the scope of the trigger is different than the scope of the originally executed insert statement that fires it.
  • IDENT_CURRENT('mytable') may be changed by the time we can retrieve it.

CREATE TRIGGER [dbo].[table3_tri] ON [dbo].[table3]
WITH EXECUTE AS CALLER
   INSTEAD OF INSERT
AS
BEGIN
DECLARE @FIELD AS VARCHAR(20);
SELECT @FIELD = i.field1 FROM inserted i;
INSERT INTO table1 (field1) VALUES ( @FIELD );
END


To return the correct identity to Alpha Five:
  • Add SELECT SCOPE_IDENTITY(); at the end of the stored procedure. A result set is returned for the outer scope (the one we can read).
  • Add SET NOCOUNT ON to the procedure to stop the return of additional result sets with counts that could look like identities.

Note: If you do more than one insert, you will need to save the identity by assigning/selecting it from SCOPE_IDENTITY(), and then returning the saved value at the end of your procedure.

The stored procedure below shows the changes required to work with Alpha Five to automatically return identity values:

CREATE TRIGGER [dbo].[table3_tri] ON [dbo].[table3]
WITH EXECUTE AS CALLER
   INSTEAD OF INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @FIELD AS VARCHAR(20);
SELECT @FIELD = i.field1 FROM inserted i;
INSERT INTO table1 (field1) VALUES ( @FIELD );
-- Added select scope_identity(); to explicitly return the 
-- inner scope identity to the outer scope
SELECT scope_identity();
END




Created by mheller. Last Modification: Wednesday 05 of October, 2011 11:32:09 EDT by mheller.

(Cached)
Refresh Print