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.
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?
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?