Managing Sequential Values

for RuBoard

There was a time when dealing with identity columns in triggers was a black art. You had to jump through numerous hoops to get identity columns to work properly, and even then there were situations when it wasn't airtight. Thanks to the addition of SCOPE_IDENTITY() and IDENT_CURRENT() to Transact-SQL, this is no longer the case. As its name suggests, SCOPE_IDENTITY() retrieves the current value for an identity column within the current scope. IDENT_CURRENT(), on the other hand, doesn't care about the current scope. It just returns the latest identity value inserted into a table. These are both better tools than the venerable @@IDENTITY function. @@IDENTITY returns the last identity value inserted for the current connection, regardless of scope and regardless of the table in which the insert occurred. So if you insert a row into a table that has an Identity column, and that table has a trigger on it that inserts into another table that also has an Identity column, @@IDENTITY will return the value inserted into the second table immediately after the row insertion. This is not good. We probably don't care about the nested insert. Typically we want the value that was generated by our explicit insertion. This is what SCOPE_IDENTITY() is for. It is unaffected by identity value inserts in other scopes (e.g., within insert triggers). IDENT_CURRENT() is also useful in this situation because we can specify what table to retrieve the last identity for. Because a different table is being inserted into via the trigger in our scenario, IDENT_CURRENT() will return the identity value generated by our explicit insert.

This is best understood by way of example. Listing 8-6 is a sample script that illustrates a few of the nuances of dealing with identity values in triggers:

Listing 8-6 The nuances of identity values and triggers.

 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity,  Type int,  Onhand int ) CREATE TABLE ToyAudit (ToyAudit int identity,  Operation varchar(10),  Toy int,  Type int,  Change int ) GO -- Seed the tables INSERT ToyInventory DEFAULT VALUES INSERT ToyInventory DEFAULT VALUES INSERT ToyInventory DEFAULT VALUES GO INSERT ToyAudit DEFAULT VALUES GO CREATE TRIGGER ToyInventory_INSERT ON ToyInventory AFTER INSERT AS IF @@ROWCOUNT=0 RETURN INSERT ToyAudit SELECT 'INSERT', * FROM inserted PRINT 'From within the trigger:' PRINT '@@IDENTITY='+CAST(@@IDENTITY AS varchar)+       ' SCOPE_IDENTITY()='+CAST(SCOPE_IDENTITY() AS varchar)+ ' IDENT_CURRENT(''ToyAudit'')='+CAST(IDENT_CURRENT('ToyAudit') AS varchar) GO INSERT ToyInventory DEFAULT VALUES PRINT 'After the insert:' PRINT '@@IDENTITY='+CAST(@@IDENTITY AS varchar)+       ' SCOPE_IDENTITY()='+CAST(SCOPE_IDENTITY() AS varchar)        +' IDENT_CURRENT(''ToyInventory'')='+        CAST(IDENT_CURRENT('ToyInventory') AS varchar) 

(Results)

 From within the trigger: @@IDENTITY=2 SCOPE_IDENTITY()=2 IDENT_CURRENT('ToyAudit')=2 After the insert: @@IDENTITY=2 SCOPE_IDENTITY()=4 IDENT_CURRENT('ToyInventory')=4 

Here we insert a row into a table on which an INSERT trigger has been defined that, in turn , inserts a row into another table. We display the values of @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() during the trigger execution as well as after the insert. Notice that the value of @@IDENTITY is the same in both cases. This is because it reflects the last identity value inserted for the current connection, regardless of the table or scope. In this case, the last identity value went into the ToyAudit table. What we're likely more interested in is the identity value generated by our insert into the ToyInventory table. This is where SCOPE_IDENTITY() and IDENT_CURRENT() come into play. After the insert, they both reflect the value we've just inserted into the table; however, SCOPE_IDENTITY() is a better choice in this case because IDENT_CURRENT() spans sessionsthat is, another user could insert a row into ToyInventory just after we did, and we'd see their identity value in IDENT_CURRENT(). This is not true of SCOPE_IDENTITY(), so I think you'll find it generally more airtight and therefore more useful.

for RuBoard