Binaryworld - Updating Derived Columns using Triggers ... [ SQL Server -> Trigger ]

INSTEAD OF triggers are also commonly used to UPDATE the base columns in calculated columns. For example, assume that a view exists called vwOrdersOrderDetailsProducts as shown here:

Click here to copy the following block

CREATE VIEW vwOrdersOrderDetailsProducts
AS
  SELECT  o.OrderID,
    o.OrderDate,
    od.UnitPrice * od.Quantity AS ExtendedPrice,
    p.ProductID,
    p.ProductName
  FROM Orders o
    INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
    INNER JOIN Products p ON od.ProductID = p.ProductID
GO

This view exposes a calculated column called ExtendedPrice that cannot be updated directly because it does not resolve itself to a single column in a table. However, you could implement a business rule that says that if the ExtendedPrice field is updated through this view, then Quantity should not change but the UnitPrice is modified. (I know this rule is a bit odd, but bear with me on this.) An INSTEAD OF UPDATE trigger could be written to enforce this business rule using the code shown here:

Click here to copy the following block

CREATE TRIGGER tr_vwOrdersOrderDetailsProducts_IO_U
  ON vwOrdersOrderDetailsProducts
  INSTEAD OF UPDATE
AS
  UPDATE   [Order Details]
  SET    UnitPrice = i.ExtendedPrice / Quantity
  FROM    inserted i
    INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND
        i.ProductID = od.ProductID

GO

This code shows how to replace an update to a calculated column with logic in INSTEAD OF triggers. Assuming that the Quantity for a product on a particular order is 100 and the ExtendedPrice is updated to 200, the new UnitPrice value will be 2. In this scenario, when the UPDATE statement is executed that modifies the ExtendedPrice column, the net effect is that the UnitPrice is set to the ExtendedPrice value divided by the Quantity. The following code can be used to test this situation:

Click here to copy the following block

UPDATE vwOrdersOrderDetailsProducts
SET   ExtendedPrice = 200
WHERE  OrderID = 10265
AND   ProductID = 17