Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Why do you want to duplicate this information, and have to maintain it every time any data in B changes, when you can derive it easily from table B when you need it? You're paying a huge maintenance cost to avoid calculating a sum once in a while. –  Aaron Bertrand Mar 8 '13 at 22:26
    
Once in a while? This is vital information for the application and are selected A LOT. There are 5-6 different kind of sub-calculations like this, for each select statement. Is that not a performance-issue? –  Behrens Mar 8 '13 at 22:37
    
With the right indexing in place, no, it shouldn't be. If you are writing data A LOT, aren't you concerned about that performance issue? Do you actually have a performance issue with this query, or are you prematurely optimizing? –  Aaron Bertrand Mar 8 '13 at 22:38
    
I see your point there, but the data are being written/updated 1 time for every 100 times the data are being read. I'm not an expert on query performance, therefore this question. :) –  Behrens Mar 8 '13 at 22:47
1  
For starters, you're on SQL Server 2008. Why are you using ntext data type, which was deprecated OVER 8 YEARS AGO? Please use nvarchar(max). Next, I'm not sure I understand why you want to group by this column. What could it possibly contain that it would be duplicated on more than one row, never mind in combination with all those other grouped columns? –  Aaron Bertrand Mar 9 '13 at 1:28

4 Answers 4

up vote 4 down vote accepted

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.

share|improve this answer
    
The Table A row are always inserted before Table B rows, by the application. Does this answer the question regarding the Name? If all rows in Table B are deleted, this must not delete the Table A row, but just set the column to either 0 or NULL. –  Behrens Mar 8 '13 at 22:57
    
And about the inner join calculation, then there COULD be times where I would have to do 5 or 6 inner joins instead of the current 5 or 6 subcalculations. I think (not knowing) this could be a performance issue? –  Behrens Mar 8 '13 at 22:59
    
For your first comment, then it sounds like the trigger is much simpler (though I don't know how much I trust an application to "always" do something). For your second comment, if you have 5 or 6 joins / subqueries, show them. Sounds like suboptimal design to me. We can help with those things, too, you know. –  Aaron Bertrand Mar 8 '13 at 23:01
    
+1 just for "...instead of premature optimization." –  Tony Mar 8 '13 at 23:05
    
@AaronBertrand, I just edited the question with an example on your approach with the inner join. Reading the edit, do You still think this approach is better than the trigger approach? –  Behrens Mar 9 '13 at 0:30

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.