Maintain Derived Columns

The next example further explores the relationship between the sales and titles tables. We’ll look at how the ytd_sales column in the titles table would be maintained using triggers.

ytd_sales is a derived column with a running tally of sales for a title. Every time a title is sold or a sales record modified, the value of ytd_sales must be modified. Without triggers, you would need to add the code to your own application to update the value of ytd_sales every time you made a modification to a sales record. Another approach would be to schedule a regular batch operation to walk the sales table and update the titles table like this:

select    title_id,    sum(qty)   “q”
   into   #temp
   from    my_sales  s
 where   ord_date  >=  “1/1/97”  and  ord_date < “1/1/98”
 group    by   title_id
update     my_titles
     set  ytd_my_sales  =  q
  from    #temp tp
     where     my_titles.title_id   =   tp.title_id
drop    table  #temp

Is it better to maintain a derived column every time its underlying values change? Or should you just schedule batch updates? That depends. As the my_sales table gets very large (and the ytd_sales column more useful!), this batch operation will take longer and longer. Also, the ytd_sales column will be unreliable except immediately after the update process runs.

To get more reliable information all day long, and to avoid the long-running batch operations, you can write a trigger to update ytd_sales after every change to my_sales. The trigger is on the my_sales table for insert, update, and delete.

create     trigger    tr_my_sales_idu_1
on     my_sales
for     insert,   update,   delete
as
if  @@rowcount  =  0  return
--  Step   1:  get  a  temporary  list  of  net  inserts  and deletes
select    title_id,     isnull(sum(qty),0)   “q”
   into  #temp
     from   inserted
  where    ord_date  >=  “1/1/97”  and  ord_date < “1/1/98”
  group   by   title_id
union
select   title_id,   -   isnull(sum(qty),0)
    from    deleted
  where  ord_date  >=  “1/1/97”  and  ord_date < “1/1/98”
  group  by   title_id
if @@error  <>  0
begin
      raiserror  (“Error  in  step  1  of  trigger:  changes rolled  back”,
      16,  1)
       rollback    transaction
   return
end
--  Step  2:  make  a  single  list  of  net  changes
select  title_id,  sum(q) qq
   into  #temp2
  from   #temp
  group    by   title_id
if @@error <> 0
begin
      raiserror  (“Error  in  step  2  of  trigger:  changes rolled  back”,
      16,  1)
       rollback   transaction
   return
end
--  Step  3:  update  the  my_titles  table
update     my_titles
       set   ytd_sales  =  isnull(ytd_sales,0)  +  qq
  from  #temp2  tp
   where    my_titles.title_id  =  tp.title_id
if @@error  <>  0
begin
      raiserror  (“Error  in  step  3  of  trigger:  changes rolled  back”,
      16,  1)
       rollback   transaction
   return
end
return

This trigger gets a lot more complex because it needs to handle INSERT, UPDATE, and DELETE. There are lots of nullability issues (remember that anything plus null makes null!), and the inserted and deleted tables will only both be populated after an UPDATE. Finally, a single operation can affect many rows in my_titles, so the code needs to handle the case of multiple affected my_sales and multiple affected my_titles.

First we need to build a list of net changes to each title as recorded in the inserted and deleted tables. This step is necessary because MS SQL 6.5 does not permit aggregate functions in a subquery.

-- Step  1:  get  a  temporary  list  of  net  inserts  and  deletes
select  title_id,  isnull(sum(qty),0)   “q”
  into  #temp
   from   inserted
 where  ord_date  >=  “1/1/97”  and  ord_date  < “1/1/98”
 group    by  title_id
union
select  title_id,  -  isnull(sum(qty),0)    --   negative  -  these are
subtractions
  from   deleted
 where  ord_date  >=  “1/1/97”  and  ord_date  < “1/1/98”
 group   by   title_id

The result of this operation is a single, two-column temporary table, with two rows per title_id, one with total additions (from inserted) and one with total subtractions (from deleted).

This query applies the “year-to-date” limitation. Changes to last year’s my_sales should not impact the value of ytd_sales.

The second step creates a new two-column temporary table with exactly one row per title_id affected. This step is necessary to avoid problems joining together three tables where at least one relationship might be optional (MS SQL 6.5 does not permit multipart outer joins or double outer joins).

-- Step  2:  make  a  single  list  of  net  changes
select   title_id,   sum(q)   qq
   into    #temp2
   from    #temp
   group    by   title_id

The final step updates the my_titles table from the second temporary table.

-- Step  3:  update  the   my_titles   table
update      my_titles
       set   ytd_sales  =  isnull(ytd_sales,0)  +  qq
  from  #temp2  tp
   where     my_titles.title_id   =   tp.title_id

(This UPDATE statement based on a join, like the DELETE statement in the last example, only works in T-SQL, not in ANSI SQL.)

Again, there are other approaches. You might consider using a cursor inside the trigger to avoid the use of temporary tables, although you should look closely at the performance issues, especially if the set of affected rows in the inserted table is quite large. Cursor performance on a large set of rows is pretty awful, so stick with set operations inside the trigger whenever you can.