Update Table A row if Table B row is changed

Question

Is is anyhow possible to tell the database to execute a little script, if a row is changed in a table?

Scenario: The database constantly monitoring Table B to see if rows are inserted, updated or deleted. If a row is inserted, updated or deleted: Update the row in Table A with the referring identifier.

Setup:

Table A
------------------------
UserID   Name   SalesSum
1        Carl   5
2        Peter  0
3        Oscar  3


Table B
--------------------------
UserID   Timestamp   Sales
1        01:00:00    3
1        02:01:00    1
1        03:54:00    1
3        01:20:00    2
3        02:45:00    1

The point of this being not to do a subquery every time the SalesSum is needed in the application, like:

Select A.Name, SalesSum = (select sum(B.Sales) from [Table B] as B where A.UserID = B.UserID) from [Table A] as A

But instead "just" doing it easy, nice and clean:

Select Name, SalesSum from [Table A]

Or is there anything I might have overseen or am wrong about?

EDIT:

Many of described the uses of inner joins for these sub-calculations. But when "trying" this, I get a couple of errors: First I have to include each column in the GROUP BY clause unless it is defined by via an Aggregate function. But when trying to do this, then it errors when having a NTEXT datatype column in the clause, because it cannot be compared.

Here is an example of the original query:

Select
 U.UserID,                                                                 ' Int - Primary Key
 U.Name,                                                                   ' Nvarchar(MAX)
 B.OrderID,                                                                ' Int - Primary Key
 B.Comment,                                                                ' Ntext
 SalesAmount = (select sum(C.Sales) from OrdersDetails as C),
 SalesPriceSum = (select sum(C.Sales * C.Price) from OrdersDetails as C)
From
 Orders as B
Inner join
 Users as U on B.UserID = u.UserID
Where
 ' Clauses on finding specific columns based on the Orders table and the Users table
Order By
 U.Name asc
 B.Period asc

The "new" query, with another inner join, could be something like:

Select
 U.UserID,                                                                 ' Int - Primary Key
 U.Name,                                                                   ' Nvarchar(MAX)
 B.OrderID,                                                                ' Int - Primary Key
 B.Comment,                                                                ' Ntext
 SalesAmount = sum(C.Sales),
 SalesPriceSum = sum(C.Sales * C.Price)
From
 Orders as B
Inner join
 Users as U on B.UserID = u.UserID
Inner join
 OrdersDetails as C on B.OrderID = C.OrderID
Where
 ' Clauses on finding specific columns in Order table
Group By
 U.UserID
 U.Name
 B.OrderID
 B.Comment     <------- This errors because of the Ntext datatype!
Order By
 U.Name asc
 B.Period asc

As written, this is only an example, and therefore there can be a large number of selected columns - specially from the Orders table. All these needs to be in the Group By clauses. But this cannot be done with the Ntext datatypes. So what do I do?

Answer 1

I don't think you need to use a trigger for this at all. With an index on B(UserID) INCLUDE(Sales) (or if there is already a clustered index leading with UserID), this query will get what you need, pretty efficiently, without having unnecessary maintenance happening all the time (even when no queries are running to calculate sums):

SELECT a.UserID, a.Name, SalesSum = SUM(b.Sales)
  FROM dbo.[Table A] AS a
  INNER JOIN dbo.[Table B] AS b
  ON a.UserID = b.UserID
  GROUP BY a.UserID, a.Name;

Look ma, no subquery!

This will generate a different plan, likely, but certainly not a more expensive one. I don't think you're going to get as much benefit maintaining this sum using triggers as you think you will. Make sure you test your entire workload, not just the query that gets the sum, if you implement triggers.

That said, here is what a trigger would look like.

CREATE TRIGGER dbo.MaintainRedundantSums
ON dbo.[Table B]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE a 
    SET a.SalesSum = COALESCE(SUM(b.Sales), 0)
    FROM dbo.[Table A] AS a 
    LEFT OUTER JOIN dbo.[Table B] AS b
    ON a.UserID = b.UserID
    WHERE EXISTS (SELECT 1 FROM inserted WHERE UserID = a.UserID)
       OR EXISTS (SELECT 1 FROM deleted  WHERE UserID = a.UserID);
END
GO

Please test this trigger in a test environment; this is fairly off the cuff, partly because it's dinner time and partly because I genuinely don't want you to use a trigger for this. If you experience an actual performance problem when calculating the sums at runtime, let's talk about that, instead of premature optimization.

EDIT

Regarding your "slow" join, how does this perform in comparison?

;WITH d AS 
(
  SELECT OrderID, SalesAmount = SUM(Sales), SalesPriceSum = SUM(Sales*Price)
    FROM dbo.OrderDetails
    GROUP BY OrderID
)
SELECT
  u.UserID, 
  u.Name,  
  o.OrderID, 
  o.Comment,
  d.SalesAmount, 
  d.SalesPriceSum
FROM
  dbo.Users AS u
INNER JOIN 
  dbo.Orders AS o -- meaningful aliases please, not A, B, C etc.
  ON u.UserID = o.UserID
INNER JOIN
  d ON d.OrderID = o.OrderID
-- WHERE ...

-- no GROUP BY needed here
ORDER BY
  u.Name,
  o.Period;

You should look at the query plan and see if the cost is mostly in the sorting. I expect that you don't have an index to support ordering by u.Name first, for example. Also I would verify that you have indexes to support your WHERE clauses and JOINs, and I certainly hope that OrderDetails.OrderID is indexed appropriately. If you want help improving the performance of a query (or set of queries), post the queries and their actual (not estimated) execution plans. Jumping to the conclusion that a trigger must be the way to fix it is kind of like buying a new car when you have a flat tire. Fix the problem, don't try to outfit a solution for the whole system.

Answer 2

Typically this is handled by triggers. Triggers are a common database tool (supported by many different RDBMS platforms) that allow you to execute actions before or after CRUD operations.

SQL Team has a fairly good tutorial on using triggers.

Answer 3

One option would be to use insert/update/delete triggers on Table B. Read up on those here: http://msdn.microsoft.com/en-us/library/ms191524%28v=sql.105%29.aspx

Triggers can be difficult to maintain, however. If you can arrange it, give some thought to disallowing direct access to Table B and have all changes to the table take place through stored procedures. You can then have the stored procedure handle the changes that need to be made to Table A.

Answer 4

Wouldn't this work, without the need for a subquery?

Select A.Name, sum(B.Sales)
  from [Table A] as A
  join [Table B] as b on (A.userID = B.userID)
 group by A.Name

Using a trigger to keep a derived column updated is doable, but offers some potential maintenance issues. For instance, if table b is altered, it could invalidate the trigger, and then your queries are wrong.