SQL Server trigger to track annual revenue

user_17641502 0
Viewed 37

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?

ssms-2012 sql-server database
1 Answers

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.

user_148689970
Related