UDFs Provide a New IDENTITY

The error occurs because SQL Server applies the default constraint first, so fn_GetNewID() executes for each row to retrieve the next valid ID value. However, the function retrieves the same value each time, because the trigger hasn't modified the sysIDDiffs table yet. After fn_GetNewID() evaluates the default value, the primary key checks for uniqueness and raises the error because the three rows in IDTest have exactly the same ID.

SQL Server 2000's new INSTEAD OF trigger can help with this error. INSTEAD OF INSERT triggers fire before any actual data modification but after fn_GetNewID() produces the default values. In the virtual table inserted, the INSTEAD OF INSERT trigger shows the rows you just tried to insert, including the default values that the fn_GetNewID() function already generated for the ID column. You can also check inside the trigger to determine whether the original INSERT operation affected only one row; if so, you can proceed with a normal INSERT operation. Otherwise, you can convert the multirow INSERT operation into multiple single-row INSERT operations.

Web Listing 4 shows the code for the isr_IDTest INSTEAD OF INSERT trigger. You use a cursor on the inserted table to evaluate the INSERT operation, row by row. An extra benefit of the INSTEAD OF INSERT trigger for multirow operations is that the trigger checks to determine whether the user tried to insert duplicate values in the ID field, in which case the trigger uses the fn_GetNewID() function to generate new values. After you execute the code in Web Listing 4 to create the INSTEAD OF INSERT trigger, execute the INSERT operation again:

INSERT IDTest (Value)
SELECT Value
FROM #Multirow

SELECT *
FROM IDTest

SELECT ID
FROM sysIDDiffs
WHERE TableID = OBJECT_ID('IDTest')

Running this code produces no errors and returns the results that Figure 3 shows.

Another potential problem occurs if you use the TRUNCATE statement to remove every row in the table. Because SQL Server doesn't register in the transaction log any deletion of rows during the execution of the TRUNCATE statement, TRUNCATE doesn't fire any trigger (not even an INSTEAD OF trigger). Therefore, you can't automatically reset the entries in the sysIDDiffs table after the execution of the TRUNCATE statement, in the same way that SQL Server resets the IDENTITY values after TRUNCATE. However, you can use a stored procedure called sp_RebuildIDDiffs to regenerate the values in sysIDDiffs after an unlogged operation. The sp_RebuildIDDiffs stored procedure accepts a table name as a parameter, searches for end-of-sequence values in the selected table's ID field, and inserts those values into sysIDDiffs.

You can also use sp_RebuildIDDiffs to generate entries in sysIDDiffs for any preexisting table if the table has a unique integer field called ID with no defined default value. In this case, you create triggers similar to those for IDTest to keep the data in your new table and sysIDDiffs consistent. Web Listing 5 shows the code for sp_RebuildIDDiffs, which has three steps:

1. Delete all previous entries in sysIDDiffs related to the selected table; the @Table parameter contains the name of the selected table.
2. Build a dynamic T-SQL statement to insert into sysIDDiffs every end-of-sequence value from the ID field in the @Table table. You must use dynamic execution to execute this statement because the INSERT...SELECT statement doesn't accept a variable as a table name.
3. Build a dynamic T-SQL statement to insert the ID = 0 entry in sysIDDiffs if the ID field from @Table doesn't contain the value ID = 1. You execute this statement dynamically.

After executing the sp_RebuildIDDiffs stored procedure, you can continue to insert rows into the selected table. The fn_GetNewID() function will automatically provide new values for the ID field.

UDFs Everywhere
SQL Server 2000 gives you more flexibility than earlier releases of SQL Server by letting you define UDFs as table or column constraints. And using the new INSTEAD OF trigger feature helps programmers solve complex updating operations. The more you explore UDFs, the more you realize that you can solve many database programming problems by using UDFs as scalar values or dynamic resultsets in the same way that you define and use functions in programming languages.

UDFs give you more flexibility than stored procedures because you can use UDFs on almost any T-SQL statement. UDFs are more efficient than views because UDFs accept parameters, and they're more flexible because a UDF's definition can contain more than one SELECT statement. If you have to design a new stored procedure or a new view, ask yourself: Could I achieve the same results with a UDF?