Tricks with INSTEAD OF Triggers

Use SQL Server 2000's new functionality to solve tough problems

Editor's Note: Congratulations to SQL Server MVP Fernando G. Guerrero, who submitted a T-SQL Black Belt solution that contributing editor Itzik Ben-Gan used in this article. Fernando will receive $100 and a T-SQL Black Belt shirt. Send your experts-only T-SQL tips to Itzik Ben-Gan at [email protected].

INSTEAD OF triggers, which Microsoft introduced with SQL Server 2000, replace the modification action that a user sends to a table or a view. Like AFTER triggers, INSTEAD OF triggers have access to the Inserted and Deleted tables. But unlike AFTER triggers, which hold the before and after images of the rows that the modification already affected, INSTEAD OF triggers hold what would have been the before and after images of the rows if the change had taken place. Keep in mind that the user action doesn't affect the base table unless the INSTEAD OF trigger performs an action against it. The INSTEAD OF trigger provides new options that you can use to extend the overall functionality of your database and supplies neat solutions to problems that otherwise would be hard to solve. SQL Server MVP Fernando G. Guerrero provided the idea for one of the solutions that I present in this article.

Firing Per-Row AFTER Triggers in Multirow Actions

First, let's look at how to use an INSTEAD OF trigger to address the problem that occurs when you use a regular AFTER trigger that supports only single-row inserts. A third-party solution provider developed this AFTER trigger and encrypted it so that no one could access its source code. Now, suppose you want to allow multirow inserts to your table, but these inserts fail. You can't drop or disable the trigger because you need the trigger to perform its tasks. You simply want to make the trigger support multirow inserts. To see how the trigger works in such a situation, run the code in Listing 1, which creates the T1 table and an AFTER trigger that prints the data of the inserted row. Now try to insert one row:

INSERT INTO T1 VALUES(10)

You'll get the following output:

Who are you?I'm 10

Try to insert three rows at once:

INSERT INTO T1  SELECT 20 AS data_col  UNION ALL  SELECT 30 AS data_col  UNION ALL  SELECT 40 AS data_col

And you'll get the following error:

Server: Msg 512, Level 16, State 1, Procedure trg_T1_i_halo, Line 7Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery isused as an expression.The statement has been terminated.

If the table didn't have an IDENTITY column, the INSTEAD OF trigger that Listing 2 shows would have provided a solution to the problem. The trigger would simply loop through all the key values in the inserted table and insert the rows, one at a time, into the base table. This action would cause the AFTER trigger to fire separately for each row. Note that the trigger in Listing 2 provides multirow support with single-row triggers on tables that don't have an IDENTITY column. The problem with this solution is that it doesn't work for the T1 table in this example because the key_col column is an IDENTITY column. The key_col column in the Inserted table has a zero value in all the rows because the modification never reaches the table when the trigger's code runs. To solve this problem, you can use the trick that Listing 3 shows. This code uses the IDENTITY() function to generate your key values in a temporary table. You populate the temporary table with the data from the Inserted table and from the newly generated keys. Now the trigger has logic similar to the previous trigger, but the new trigger's loop runs against the temporary table instead of against the Inserted table.

Now try the following multirow insert:

INSERT INTO T1  SELECT 20 AS data_col  UNION ALL  SELECT 30 AS data_col  UNION ALL  SELECT 40 AS data_col

And you'll get the following output:

Who are you?I'm 20Who are you?I'm 30Who are you?I'm 40

Avoiding Constraints Violation by Saving Illegal Rows to a Log Table

You can also use INSTEAD OF triggers to enforce data integrity. If you have constraints in your table and you issue an insert operation in which one of the rows fails one of the constraints, then the whole insert operation rolls back. You might prefer to have the invalid rows sent to a log table, along with the reason for the failure, and have only the valid rows inserted into the table. You can accomplish this task by using INSTEAD OF triggers. To see how this functionality works, create the OrderDetails and OrderDetailsLog tables that Listing 4 shows. Note that this example uses the original Orders table from the Northwind database, and you create the OrderDetails and OrderDetailsLog tables.

Now let's create an INSTEAD OF INSERT trigger on the OrderDetails table that logs invalid rows to the OrderDetailsLog table. You usually would also need an INSTEAD OF UPDATE trigger, but this example addresses only inserts to demonstrate the general idea of logging. The trigger performs a series of existence checks to determine whether a row violates the primary key constraint, one of the foreign key constraints, and the check constraint in the OrderDetails table. If invalid rows exist, the trigger inserts them into the OrderDetailsLog table. Finally, the trigger inserts all valid rows into the OrderDetailsLog table. Listing 5 shows the trigger's code.

Note that a certain row might violate several constraints. In such a case, the trigger will log the row several times, once for each violation. Now try to insert a few rows into the OrderDetails table, as Listing 6 shows. This trigger doesn't generate a message when an insert fails; it only logs the errors in the OrderDetailsLog table. If you want to generate error messages when an insert fails, you can add PRINT or RAISERROR statements to the trigger.

Only the first row violates no constraints, so it's the only one you'll find in the OrderDetails table. Rows 2 through 5 in the insert script violate one constraint each, so the trigger will log each of those rows once with the description of the error. The last row in the script violates three constraints, so the trigger will log it three times. Take a look at the content of OrderDetailsLog, which Table 1 shows, after you run the insert script.

INSTEAD OF Triggers on Views

SQL Server MVP Fernando G. Guerrero provided the idea for the next example. One great benefit of INSTEAD OF triggers is that you can create them on views. This functionality lets you support modifications to views that usually wouldn't allow modifications in certain situations—for example, views that calculate aggregations. To explore this functionality, run the script in Listing 7, which adds a few more rows to the OrderDetails table that I used in the previous example. Now create the VTotalOrders view that Listing 8 shows, which calculates the total quantity for each order.

Suppose you want to allow updates through that view. You usually wouldn't want to let users modify the OrderID column through the view, but would you want to let them modify the TotalQty column? After all, the total quantity for each order is calculated by summarizing the quantities of the participating products. You can specify an algorithm that distributes the modification of the TotalQty column in the view to all the affected products in the OrderDetails table. For example, you can distribute the value change according to the respective quantity of each affected product of each affected order. Listing 9 shows the INSTEAD OF UPDATE trigger that implements this distribution algorithm.

The formula that calculates the new quantity for each affected product is simple. The formula calculates the percentage of each product's quantity from the total quantity of the parent order before the modification. The formula then multiplies the result by the new total quantity of the parent order. Note that all the affected values are integers, which means that the formula would have performed integer arithmetic had we not initially multiplied 1. by OD.Quantity. This trick ensures that the formula will cast the integer values to decimal values, so you won't lose precision of values along the way. Because you want to store the most precise value possible in the Quantity column, you need to round the result to avoid truncation. Before you update the view, take a look at the content of the OrderDetails table, which Table 2 shows, and of the VTotalOrders view, which Table 3 shows.

Now issue the following update against the view:

UPDATE VTotalOrders  SET TotalQty = TotalQty * 2

Review the content of the table and the view, which Table 4 and Table 5 show, respectively. The trigger performed its task accurately.

This article demonstrated how you can use INSTEAD OF triggers to support multirow inserts that fire an AFTER trigger for each row, to log invalid rows to a log table instead of rejecting the whole modification, and to support updates to a view that calculates aggregations. These are just a few examples of solutions that are now possible with INSTEAD OF triggers.