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 20
Who are you?
I'm 30
Who 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.