Home
Schema - Do you use triggers for denormalized fields?
  v2.0 Posted at 7/11/2019 5:31 AM by Tiago Araujo

​I believe that de-normalised fields are not a bad thing. When used properly and sparingly, they can actually improve your application's performance. As an example:

  • I have an Orders table containing one record per order
  • I also have an OrderItems table which contains line items linked to the main OrderID, as well as subtotals for each line item
  • In my front end I have a report showing the total for each order​

To generate this report, I can either:

  1. Calculate the Order total by summing up every single line item for the selected Order every time the report is loaded, or
  2. Store the Order subtotal as a de-normalised field in the Orders table which gets updated using trigger.

The second option will save me an expensive JOIN query each time because I can just tack the denormalised field onto the end of my SELECT query.

​1. Code: Alter Orders table​

ALTER TABLE Orders
ADD SumOfOrderItems money NULL


2. Code: Insert trigger

Alter Trigger tri_SumOfOrderItems
On dbo.OrderItems
For Insert
AS
DECLARE @OrderID varchar (5)
SELECT @OrderID = OrderID from inserted
UPDATE Orders
SET Orders.SumOfOrderItems = Orders.SumOfOrderItems +
(SELECT isnull(SUM(ItemValue),0) FROM inserted WHERE inserted.OrderID = Orders.OrderID)
WHERE Orders.OrderID = @OrderID


3. Code: Update trigger

Alter Trigger tru_SumOfOrderItems
On dbo.OrderItems
For Update
AS
DECLARE @OrderID varchar (5)
SELECT @OrderID = OrderID from deleted
--Could have used inserted table
UPDATE Orders
SET Orders.SumOfOrderItems = Orders.SumOfOrderItems
+ (SELECT isnull(SUM(ItemValue),0) FROM inserted WHERE inserted.OrderID = Orders.OrderID)
- (SELECT isnull(SUM(ItemValue),0) FROM deleted WHERE deleted.OrderID = Orders.OrderID)
WHERE Orders.OrderID = @OrderID

4. Code: Delete trigger

Alter Trigger trd_SumOfOrderItems
On dbo.OrderItems
For Delete
AS
DECLARE @OrderID varchar (5)
SELECT @OrderID = OrderID FROM deleted
UPDATE Orders
SET Orders.SumOfOrderItems = Orders.SumOfOrderItems -
(SELECT isnull(SUM(ItemValue),0) FROM deleted WHERE deleted.OrderID = Orders.OrderID)
WHERE Orders.OrderID = @OrderID

5. Code: Maintenance stored procedure

--Stored Procedure for Maintenance
Alter Procedure dt_Maintenance_SumOfItemValue
As
UPDATE Orders
SET Orders.SumOfOrderItems = Isnull((SELECT SUM (ItemValue) FROM OrderItems WHERE OrderItems.OrderID = Orders.OrderID),0)

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: