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?
Best Answer
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 withUserID
), 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):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.
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?
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 yourWHERE
clauses andJOIN
s, and I certainly hope thatOrderDetails.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.