SQL Server trigger to track annual revenue

Question

I have a simple database with 4 tables:

• Customer (cusId)
• Newspaper (papId)
• SubCost (subId)
• Subscription (cusId, papId, subId)

Newspaper has a column to track number of subscribers which is updated via a trigger on the Subscription table. It also has a column to track annual revenue which should be based on the number of subscribers and the cost associated with the subscription (subId).

I am looking for a trigger to track annual revenue. There are 3 subscription types (subId) with differing weekly costs and a paper can have more than one type of subscription so it can't just be (cost * 52 * numSubs).

Can you help me with this logic?

Answer 1

Your best bet is not using such a column at all. Instead use a view which computes the result, and index it if necessary

CREATE OR ALTER VIEW vTotalSubs
WITH SCHEMABINDING AS

SELECT
  n.papid,
  TotalRevenue = SUM(sc.Cost * 52),
  TotalSubscriptions = COUNT_BIG(*)  -- you MUST have this column here if aggregating with an index
FROM dbo.Newspaper n
JOIN dbo.Subscription s ON s.papid = n.papid
JOIN dbo.SubCost sc ON sc.subid = s.subid
GROUP BY
  n.papid;

GO

CREATE UNIQUE CLUSTERED INDEX CX_vTotalSubs ON vTotalSubs (papid);

If you decide to index the view, be aware there are many restrictions to indexed views, in particular:

• Only INNER JOIN is allowed, no other join types, no subqueries
• Must schema-bind, and specify schema on all tables.
• If aggregating, you must have COUNT_BIG(*), and the only other aggregation allowed is SUM
• Make sure to add the WITH (NOEXPAND) hint when querying, otherwise there may be performance impacts

The server will automatically maintain the index, you do not need to update it.