Sometimes we need to maintain summarised data, this can involve reading data from several main tables and performing complex calculations. The main reasons for doing this are to avoid performance issues associated with dynamically generating the data, or to simplify the application design. Keeping the summarised data synchronised with the real data in the system, means that when data in the main tables is modified, the summarised data needs to be updated. In this article we will look at how to construct triggers in SQL Server to maintain denormalized tables of data.

Consider a table called [Orders] and a related table called [Order Details] (examples of these tables can be found in Northwind). The [Order Details] table contains multiple records for each order with a [Quantity] and [UnitPrice] and [ProductId] for each orderline.

We want a denormalized table that contains several fields from the [Orders] table and the total order value from the [Order Details] table for the order, we also want a record entered in this table with 0 for the [Ordervalue] when an Order has no order details.

Data Synchronisation

First construct a stored procedure which will empty and repopulate the [OrdersSummary] table for all data in the system, then we will add Triggers to the main tables so that when an order or [order details] is changed the [OrdersSummary] table is updated to reflect these changes. Keeping our denormalised data table in-step with the dependent tables.

The stored procedure used to initially populate our summary table is shown below: -

CREATE PROC usp_InitialiseOrdersSummary AS BEGIN -- empty the summary table TRUNCATE TABLE OrdersSummary -- re-populate the table INSERT INTO OrdersSummary(OrderId,OrderDate,Ordervalue) SELECT Orders.OrderID, Orders.OrderDate, ISNULL(SUM([UnitPrice] * [Quantity]),0) As Ordervalue FROM Orders    LEFT OUTER JOIN [Order Details]       ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID, Orders.OrderDate END GO

Because our denormalised table does not participate in any relationships we can use a TRUNCATE TABLE to quickly empty the table. The use of the ISNULL function generates an [Ordervalue] of 0 for [Orders] which have no [order details].

To keep our table synchronised we require triggers on both the [Orders] and [Order Details] tables.

Top of Page

Orders Table Trigger

On the orders table we need to consider the following actions: -

• Insert : generate a new summary row
• Update : update any details in the summary row
• Delete : remove the summary row

We can use the before image virtual table DELETED to see what the data looked like before it was changed (including any deleted rows) and the INSERTED image to see what the data looks like after it has been changed (including any inserted rows).

CREATE TRIGGER TR_Orders ON Orders FOR INSERT, UPDATE, DELETE AS BEGIN -- Remove existing rows DELETE FROM OrdersSummary WHERE OrderId IN (SELECT OrderId FROM DELETED) -- Regenerate changed rows INSERT INTO OrdersSummary(OrderId,OrderDate,Ordervalue) SELECT Orders.OrderID, Orders.OrderDate, ISNULL(SUM([UnitPrice] * [Quantity]),0) As Ordervalue FROM Orders    LEFT OUTER JOIN [Order Details]       ON Orders.OrderID = [Order Details].OrderID    INNER JOIN (SELECT OrderId FROM INSERTED) As Thekeys       ON Orders.OrderId = Thekeys.OrderId GROUP BY Orders.OrderID, Orders.OrderDate END

In the part of the code deleting data, we have WHERE OrderId IN (SELECT OrderId FROM DELETED), it is worth noting that we are allowing for multiple orders to have been changed in a single operation. Because we only need to delete for any existing rows we can use the DELETED virtual table (before image of the data). When it comes to inserting the new row we use the INSERTED virtual table because we want to include new and updated rows but not deleted rows.

Top of Page

Order Details Table Trigger

Now we require an almost identical trigger on the [Order Details] table. Because we need to recomputed when detail lines are removed we use a UNION on the INSERTED and DELETED virtual tables to pick up all Order id’s. the UNION will also naturally remove any duplicate values.

CREATE TRIGGER TR_OrderDetails ON [Order Details] FOR INSERT, UPDATE, DELETE AS BEGIN -- Remove existing rows DELETE FROM OrdersSummary WHERE OrderId IN (SELECT OrderId FROM INSERTED          UNION SELECT OrderId FROM DELETED) -- Regenerate changed rows INSERT INTO OrdersSummary(OrderId,OrderDate,Ordervalue) SELECT Orders.OrderID, Orders.OrderDate, ISNULL(SUM([UnitPrice] * [Quantity]),0) As Ordervalue FROM Orders    LEFT OUTER JOIN [Order Details]       ON Orders.OrderID = [Order Details].OrderID    INNER JOIN (SELECT OrderId FROM INSERTED          UNION SELECT OrderId FROM DELETED) As Thekeys       ON Orders.OrderId = Thekeys.OrderId GROUP BY Orders.OrderID, Orders.OrderDate END

Top of Page

Options in the Join

You will notice that there is an additional join in the above trigger code that is not required, where we have for the join in the INSERT.

FROM Orders    LEFT OUTER JOIN [Order Details]       ON Orders.OrderID = [Order Details].OrderID    INNER JOIN (SELECT OrderId FROM INSERTED UNION          SELECT OrderId FROM DELETED) As Thekeys       ON Orders.OrderId = Thekeys.OrderId

This is because we took our original block of SQL and added at the end a filter to pick out selected order id’s. This could more efficiently have been written as the following:-

FROM Orders    INNER JOIN (SELECT OrderId FROM INSERTED UNION          SELECT OrderId FROM DELETED) As Thekeys       ON Orders.OrderId = Thekeys.OrderId

As the INSERT SQL can be quite complicated involving a number of tables, when developing your Triggers it is often simpler to keep this redundancy (so that copy and paste for changes involves a minimum amount of work), and then once you have all the triggers working remove any redundant joins.

Updating Only When Specific Columns Data Changes

Looking back at TR_Orders we can see that the triggers will execute regardless of the fields that get changed in the underlying tables, as the [Orders] table will likely have a large number of fields which are often being updated, we could improve efficiency so that the records only get regenerated when the dependent fields get changed.

There are two functions you could consider using for this. The first is COLUMNS_UPDATED(); (which is more complicated to use), so we will look at the second function UPDATED() which is simpler and is used against a single column but can be repeatedly used for multiple fields.

For our orders trigger, we now need to consider the following actions: -

• On an insert we need to generate a new record
• On a delete we need to remove a record
• On an update we need to regenerate the record only when the [OrderDate] is changed

If we do not have any records in INSERTED, then the action has been to only delete rows and we remove the summary rows, otherwise we regenerate the rows when the [OrderDate] has been changed (or a new record created).

ALTER TRIGGER TR_Orders ON Orders FOR INSERT, UPDATE, DELETE AS BEGIN IF NOT EXISTS(SELECT OrderId FROM INSERTED) BEGIN    -- an order is deleted    DELETE FROM OrdersSummary    WHERE OrderId IN (SELECT OrderId FROM DELETED) END ELSE BEGIN    -- the orderdate has been changed    IF UPDATE([OrderDate])    BEGIN    DELETE FROM OrdersSummary    WHERE OrderId IN (SELECT OrderId FROM INSERTED WHERE UPDATE([OrderDate]))    -- Regenerate changed rows    INSERT INTO OrdersSummary(OrderId,OrderDate,Ordervalue)    SELECT Orders.OrderID, Orders.OrderDate,    ISNULL(SUM([UnitPrice] * [Quantity]),0) As Ordervalue    FROM Orders       LEFT OUTER JOIN [Order Details]          ON Orders.OrderID = [Order Details].OrderID       INNER JOIN (SELECT OrderId FROM INSERTED) As Thekeys          ON Orders.OrderId = Thekeys.OrderId    GROUP BY Orders.OrderID, Orders.OrderDate    END END END GO

Top of Page

Complexity When Keys Can Change

Now consider what happens if the [OrderID] (primary key) can be changed ( whilst in this example this may appear unreasonable there are situations involving more complex combinations of tables where this kind of structural change can occur). Now we need to generate our rows for both the original order id and the new order id. We also assume we have a cascade update so that the appropriate [Order Details] lines receive any changes to the foreign key OrderId.

ALTER TRIGGER TR_Orders ON Orders FOR INSERT, UPDATE, DELETE AS BEGIN -- Remove existing rows DELETE FROM OrdersSummary WHERE OrderId IN (SELECT OrderId FROM INSERTED UNION          SELECT OrderId FROM INSERTED) -- Regenerate changed rows INSERT INTO OrdersSummary(OrderId,OrderDate,Ordervalue) SELECT Orders.OrderID, Orders.OrderDate, ISNULL(SUM([UnitPrice] * [Quantity]),0) As Ordervalue FROM Orders    LEFT OUTER JOIN [Order Details]       ON Orders.OrderID = [Order Details].OrderID    INNER JOIN (SELECT OrderId FROM INSERTED) As Thekeys       ON Orders.OrderId = Thekeys.OrderId GROUP BY Orders.OrderID, Orders.OrderDate END GO

The union over both INSERTED and DELETED ensures that records associated with both the old and new order id’s are removed, and the insert only re-generates these for the new order id.

Note, in some parts of a design, we may need to regenerate the results for both old and new records, this is described below.

Top of Page

More Complex Dependencies

In this example we have a denormalised table which tells us which product is the most popular for customers to buy, but only for orders which have not been completed (as judged by an [OrderCompleted] date in the Orders table. We have the following summary table: -

We have the added complexity that when a product on an [Order Details] is changed to a different product all the other product sales quantities need changing on both the old [ProductID] as well as on the new [ProductID].

We start with the code to prime our denormalised table.

CREATE PROC usp_InitialiseSummaryProductSales AS BEGIN -- empty the summary table TRUNCATE TABLE SummaryProductSales -- re-populate the table INSERT INTO SummaryProductSales(ProductId,SalesQuantity) SELECT ProductId, SUM([Quantity]) As SalesQuantity FROM [Order Details]    INNER JOIN Orders       ON [Order Details].OrderID =Orders.OrderID WHERE Orders.OrderCompleted is null GROUP BY [Order Details].ProductId END GO

Looking at the [Orders] table we have the following rules to observe

• If the [OrderCompleted] date is changed, then recomputed for all associated products
• If a new order is created then no action is required (handled on the order details trigger)
• If an order is deleted then no action is required (handled on the order details trigger)

In this case we only require the trigger for the UPDATE event. There is some extra complexity in that we need to link to the [order details] to find the appropriate product id’s.

CREATE TRIGGER [dbo].[TR_OrdersTrigger2] ON [dbo].[Orders] FOR UPDATE AS BEGIN -- if OrderCompleted changed then re-compute all associated -- product quantities DELETE FROM SummaryProductSales FROM SummaryProductSales    INNER JOIN [Order Details]       ON SummaryProductSales.ProductId = [Order Details].ProductId WHERE [Order Details].OrderId IN       (SELECT OrderId FROM INSERTED WHERE UPDATE([OrderCompleted])) -- Regenerate changed rows INSERT INTO SummaryProductSales(ProductId,SalesQuantity) SELECT [Order Details].ProductId, ISNULL(SUM([Quantity]),0) As SalesQuantity FROM [Order Details]    INNER JOIN Orders       ON [Order Details].OrderID =Orders.OrderID    INNER JOIN (SELECT ProductId       FROM INSERTED          INNER JOIN [Order Details]             ON INSERTED.OrderId = [Order Details].OrderId          WHERE UPDATE([OrderCompleted])) TheKeys       ON [Order Details].ProductId = TheKeys.ProductId WHERE Orders.OrderCompleted is null GROUP BY [Order Details].ProductId END GO

The above trigger does not allow for the [OrderId] to change, in which case we would require to change the condition UPDATE([OrderCompleted])) to UPDATE([OrderCompleted]) OR UPDATE([OrderId])

On the order details we have the following trigger:-

CREATE TRIGGER [dbo].[TR_OrdersDetailsTrigger2] ON [dbo].[Order Details] FOR UPDATE, INSERT,DELETE AS BEGIN -- recalculate when quantity changed or line added or deleted DELETE FROM SummaryProductSales FROM SummaryProductSales Where ProductId IN    (SELECT ProductId FROM INSERTED UNION    SELECT ProductId FROM DELETED) -- Regenerate changed rows INSERT INTO SummaryProductSales(ProductId,SalesQuantity) SELECT [Order Details].ProductId, ISNULL(SUM([Quantity]),0) As SalesQuantity FROM [Order Details]    INNER JOIN Orders       ON [Order Details].OrderID =Orders.OrderID    INNER JOIN (SELECT ProductId FROM INSERTED UNION          SELECT ProductId FROM DELETED) TheKeys       ON [Order Details].ProductId = TheKeys.ProductId WHERE Orders.OrderCompleted is null GROUP BY [Order Details].ProductId END GO

Note in the above we need to regenerate the summary for both the old and new product id’s.

Summary

Triggers provide an elegant mechanism for enabling a denormalised table of reference data to be dynamically synchronised with the main application tables.

Code Download

The sample code described in this article can be downloaded using the following link. (click here)

Top of Page Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz