Schema - Do you use triggers for denormalized fields?

Loading last updated info...

Ideally you should be using computed columns as per Do you use computed columns rather than denormalized fields?

You can also have a denormalized field that is manually updated. This should be the exception and not the rule. When used properly and sparingly, they can actually improve your application's performance.

As an example:

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

To generate this report, you can either:

Calculate the Order total by summing up every single line item for the selected Order every time the report is loaded, or
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 you 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

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

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

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

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)

Alter Trigger tri_SumOfOrderItems

On dbo.OrderItems

For Insert

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

Alter Trigger tru_SumOfOrderItems

On dbo.OrderItems

For Update

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

Alter Trigger trd_SumOfOrderItems

On dbo.OrderItems

For Delete

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

--Stored Procedure for Maintenance

Alter Procedure dt_Maintenance_SumOfItemValue

UPDATE Orders

SET Orders.SumOfOrderItems = Isnull((SELECT SUM (ItemValue) FROM OrderItems WHERE OrderItems.OrderID = Orders.OrderID),0)

Schema - Do you use triggers for denormalized fields?

Categories

Authors

Need help?

Schema - Do you use triggers for denormalized fields?

Categories

Authors

Need help?