Triggers and Denormalized Tables of Data (3rd February 2010)

support@ascassociates.biz , Sales Enquiries

Triggers and Denormalized Tables of Data (3rd February 2010)

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.

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.

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

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

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.

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)

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

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz