Sql-server – Coalesce Function Returning Duplicates

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.

If I understand correctly, I think this should work...

We're checking that TableA.id+100 is in TableC.eid and then counting the number of times TableA.id+100 appears as TableB.char.

It's always good to create a SQLFiddle though so that others can easily pick up your schema and code to debug.

SELECT
  a.id,
  a.name,
  a.comment,
  COUNT(b.char) AS count
FROM TableA a
INNER JOIN TableB b 
  ON a.id+100 = b.char
INNER JOIN TableC 
  ON a.id+100 = c.eid
GROUP BY a.id, a.name, a.comment;

As Aaron Bertrand pointed out below, using EXISTS would help to eliminate double counting on where duplicate eids appear in TableC.

SELECT
      a.id,
      a.name,
      a.comment,
      COUNT(b.char) AS count
FROM TableA a
INNER JOIN TableB b 
  ON a.id+100 = b.char
WHERE EXISTS (SELECT * FROM TableC where eid = a.id+100)
GROUP BY a.id, a.name, a.comment;