Creating a default query for a column in a table (SQL)???

Question

Hey

I have a column in one of my tables which is suppose to be the total sum for from the rows of a number of tables. Is there a way i can have a default query which runs on the total sum column so that every time a row is added to the other table an update is made in the total sum column.

Thanks

Answer 1

You might want to look at using a view instead of a table for this, something like the following might help.

Select table.*, sum(otherTable.column) 
from table 
inner join otherTable on table.something = otherTable.something

Answer 2

Sounds like you want to add a trigger.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Answer 3

You want to update the total sum column every time one of the columns in the other tables is changed? Then a trigger may serve your purposes.

   Create Trigger For Insert, Update, Delete
   On OtherTable
   As
      Update SumTable Set
          SumColumn =
               (Select Sum(Column)
                From OtherTable
                Where something = s.Something)
      From SumTable s
      Where Something In
           (Select Distinct something From inserted
              Union
            Select Distinct Something From deleted)

or, you can separate the code for a delete from the code for an insert or update by writing separate triggers, or by:

   Create Trigger For Insert, Update, Delete
   On OtherTable
   As
      If Exists(Select * From inserted) And Update(Column)
          Update SumTable Set
              SumColumn =
                  (Select Sum(Column)
                   From OtherTable
                   Where something = s.Something)
          From SumTable 
          Where Something In 
              (Select Distinct Something 
               From Inserted)
      Else If Exists(Select * From deleted)              
          Update SumTable Set
              SumColumn =
                  (Select Sum(Column)
                   From OtherTable
                   Where something = s.Something)
          From SumTable 
          Where Something In 
              (Select Distinct Something 
               From deleted)

Answer 4

As Charles said, a trigger works well in this situation. If the sum of rows from other tables changes frequently however, I'm not sure if a trigger would cause performance issues. There are two other approaches:

Views - A view is essentially a saved query, and you query on it just like a table. If the sum data is only needed for reporting-type stuff, you may be better off removing the sum column from your main table and using the view for reporting

Stored Procedure - If you prefer to keep the column in the main table, you could run a stored procedure on a regular basis that keeps the sum information up-to-date for all rows.

Answer 5

I would compare performance between the view idea and the trigger idea before deciding which to use. Do this against the full data set you expect the view to have, not just a small test set of data. Make sure to index the view if it is possible.