Binaryworld - INSTEAD OF Triggers ... [ SQL Server -> Trigger ]

AFTER triggers (also known as FOR triggers) execute following the triggering action, such as an insert, update, or delete. For example, an AFTER trigger on an Employees table will fire after an UPDATE statement has been executed against the Employees table. Thus, the trigger does not fire until the row or rows have been inserted and constraints have been checked and passed. INSTEAD OF triggers are inherently different from AFTER triggers because INSTEAD OF triggers fire in place of the triggering action. So using the same example, if an INSTEAD OF UPDATE trigger exists on the Employees table and an UPDATE statement is executed against the Employees table, then the UPDATE statement will not change a row in the Employees table. Instead, the UPDATE statement is used to kick off the INSTEAD OF UPDATE trigger, which may or may not modify data in the Employees table.
So how do you determine the right time and place to use an INSTEAD OF trigger? Several key factors are worth considering when making this decision. AFTER triggers are more commonly used in situations where actions must be taken following data modifications on tables. For example, an AFTER trigger could be used to log any data updates to a separate auditing table. INSTEAD OF triggers could do the same job, but they are less efficient in this particular scenario since the update will be allowed exactly as it occurred after writing to the audit table.
In general, in any situation where the data modification will not be affected, an AFTER trigger is more efficient. An AFTER trigger is also a great choice when the data modification is evaluated and is either allowed to commit as a whole or denied entirely. For example, a rule could exist that any change to a product's price of more than 30 percent in the Products table must be undone. An AFTER trigger could do the job here nicely, using the inserted and deleted tables to compare the price of the product and then roll back the transaction if need be. These are ideal situations for AFTER triggers, but sometimes INSTEAD OF triggers are better.

INSTEAD OF triggers are a great feature that allow you to perform complex action queries in place of a single action query on a table or a view. Unlike AFTER triggers, which can only be created against tables, INSTEAD OF triggers can be created against both tables and views. I have often been asked how to resolve the situation in which there is a view that represents a join of multiple tables and you want to allow an update to the view. If the view exposes the primary key and required fields of a base table, it is often simple to update the view's base table. However, when there are multiple tables represented in a join, the update logic is more complicated than a single UPDATE statement. So how do you resolve this situation using alternative tools? One solution is to place an INSTEAD OF UPDATE trigger on the view. INSTEAD OF triggers can be defined on views with one or more tables. The INSTEAD OF trigger can then extend the type of modifications that will take place on the multiple base tables.
For example, if a view joins the Customers, Products, Orders, and Order Details tables together to show all of the data on a screen through an application, updates could be allowed to take place through this view. Assuming a view exists that joins these four tables in the Northwind database and is named vwCustomersOrdersOrderDetailsProducts, it could look like Figure-1

Click here to copy the following block

--------------------------------------------------------------------------------
--Figure 1 View Joining Customers and Their Order Details
--------------------------------------------------------------------------------

CREATE VIEW vwCustomersOrdersOrderDetailsProducts
AS
  SELECT  c.CustomerID,
    c.CompanyName,
    o.OrderID,
    o.OrderDate,
    od.UnitPrice,
    od.Quantity,
    od.Discount,
    p.ProductID,
    p.ProductName
  FROM Customers c
    INNER JOIN Orders o ON c.CustomerID = o.CustomerID
    INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
    INNER JOIN Products p ON od.ProductID = p.ProductID
GO

--------------------------------------------------------------------------------
--Figure 2 INSTEAD OF Trigger Updating a View
--------------------------------------------------------------------------------

CREATE TRIGGER tr_vwCustomersOrdersOrderDetailsProducts_IO_U
  ON vwCustomersOrdersOrderDetailsProducts
  INSTEAD OF UPDATE
AS
  —- Update the Customers
  UPDATE   Customers
  SET  CompanyName = i.CompanyName
  FROM  inserted i
    INNER JOIN Customers c ON i.CustomerID = c.CustomerID

  —- Update the Orders
  UPDATE   Orders
  SET  OrderDate = i.OrderDate
  FROM  inserted i
    INNER JOIN Orders o ON i.OrderID = o.OrderID

  —- Update the Order Details
  UPDATE   [Order Details]
  SET  UnitPrice = i.UnitPrice,
    Quantity = i.Quantity
  FROM  inserted i
    INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND
      i.ProductID = od.ProductID

  -- Update the Products
  UPDATE   Products
  SET  ProductName = i.ProductName
  FROM  inserted i
    INNER JOIN Products p ON i.ProductID = p.ProductID
GO

The vwCustomersOrdersOrderDetailsProducts view joins the four tables and exposes a sampling of fields from each of the tables. One key to remember when designing views that will have INSTEAD OF UPDATE triggers is that it is helpful to include the primary key fields from each table in the SELECT statement. Even if these fields are not used in the application, they can be used in the INSTEAD OF trigger to locate the row(s) that were intended to be modified and then make the appropriate changes in the base tables. Let's assume that you want to allow updates to this view to funnel down to the base tables on the non-key fields. Code would then have to be written in the INSTEAD OF UPDATE trigger to update the CompanyName in the Customers table, the OrderDate in the Orders table, the UnitPrice and Quantity in the Order Details table, and the ProductName in the Products table. At this point using an AFTER trigger won't cut it, but an INSTEAD OF trigger is a good option (see Figure 2).
Notice that the code in the INSTEAD OF UPDATE trigger in Figure 2 (tr_vwCustomersOrdersOrderDetailsProducts_IO_U) contains four UPDATE statements. Each targets one of the base tables with the purpose of modifying the exposed non-key fields. The key fields for each base table are joined in the UPDATE statements to the corresponding fields that the view exposes. This allows the UPDATE statements to locate the appropriate rows in the underlying tables and update only those rows. The following UPDATE statement puts this INSTEAD OF trigger to the test:

Click here to copy the following block

UPDATE vwCustomersOrdersOrderDetailsProducts
SET  Quantity = 100,
    UnitPrice = 20,
    CompanyName = 'Fake Name',
    OrderDate = '11/23/2001',
    ProductName = 'Widget'
WHERE  OrderID = 10265
AND   ProductID = 17

If you examine the values in the underlying tables (either through the view or in the tables themselves) it is evident that the values have been updated. Of course, several modifications could be made to this INSTEAD OF UDPATE trigger to yield different results. For example, it is not a requirement to write the INSTEAD OF trigger to modify all four base tables. Therefore, one or more of the UDPATE statements contained within the trigger could be removed. Assuming that the trigger was intended only to update the Order Details values, it could be modified to update only those fields in the Order Details table—ignoring any changes that were attempted on the other base tables. In this situation, no error is raised nor are the changes made to Customers, Products, or Orders tables. Of course, an error could be raised if one of these fields was updated. The UPDATE and COLUMNS_UPDATED functions are ideal for checking what fields have been modified, as I'll demonstrate later in this column.
Figure 2 also demonstrates how to prepare a trigger to deal with changes to multiple rows. Note how the UPDATE statements join the inserted table to each base table on the key field. This ensures that the updates will occur on all rows that were modified by the original UPDATE statement on the view. This could be done by looping through the rows in the inserted table, too. However, it is a good idea to avoid using cursors in general, and especially when using triggers. SQL Server is built to handle data in sets while cursors handle rows one at a time. Using cursors in a trigger can slow down your application, so it's better to use a more efficient alternative like the one in Figure 2 or a subquery.
Another way that this INSTEAD OF UPDATE trigger could be modified is to make it also fire on INSERT and DELETE statements on the view. This would mean the trigger would likely perform an INSERT or a DELETE where appropriate. Keep in mind, however, that a DELETE could remove several records depending on how the trigger is written. Therefore, it is critical that the requirements of the trigger be reviewed and thorough testing be performed before implementation. An INSTEAD OF INSERT trigger could be written on this view so that it inserts a new customer, order, order detail, and product. It could also be written to determine if the customer was new before inserting a new one (and so on with the other records). The opportunities are numerous when deploying INSTEAD OF triggers, but of course it is essential that the trigger is designed to resolve the appropriate requirement.
Usually when an UPDATE statement that references a table attempts to set the value of a computed, identity, or timestamp column, an error is generated because the values for these columns must be determined by SQL Server. These columns must be included in the UPDATE statement to meet the NOT NULL requirement of the column. However, if the UPDATE statement references a view with an INSTEAD OF UPDATE trigger, the logic defined in the trigger can bypass these columns and avoid the error. To do so, the trigger must not try to update the values for the corresponding columns in the base table (leave them out of the SET clause of the UPDATE statement). When a record is processed from the inserted table, the computed, identity, or timestamp column can contain a dummy value to meet the NOT NULL column requirement, but the INSTEAD OF UPDATE trigger ignores those values and the correct values are set by SQL Server.