salesdetail Table

The salesdetail table shows the store ID, order ID, title number, quantity of sales, and discounts of sales.

salesdetail is defined as:

create table salesdetail
(stor_id char(4) not null,
ord_num numeric(6,0),
title_id tid not null,
qty smallint not null,
discount float not null)

Its primary keys are stor_id and ord_num:

sp_primarykey salesdetail, stor_id, ord_num

Its title_id, stor_id, and ord_num columns are foreign keys to titles and sales:

sp_foreignkey salesdetail, titles, title_id
sp_foreignkey salesdetail, sales, stor_id, ord_num

Its nonclustered index for the title_id column is defined as:

create nonclustered index titleidind
on salesdetail (title_id)

Its nonclustered index for the stor_id column is defined as:

create nonclustered index salesdetailind
on salesdetail (stor_id)

Its title_idrule rule is defined as:

create rule title_idrule
as
@title_id like "BU[0-9][0-9][0-9][0-9]" or
@title_id like "[MT]C[0-9][0-9][0-9][0-9]" or
@title_id like "P[SC][0-9][0-9][0-9][0-9]" or
@title_id like "[A-Z][A-Z]xxxx" or
@title_id like "[A-Z][A-Z]yyyy"

salesdetail uses this trigger:

create trigger totalsales_trig on salesdetail
    for insert, update, delete
as
/* Save processing:  return if there are no rows affected */
if @@rowcount = 0
    begin
       return
end
/* add all the new values */
/* use isnull:  a null value in the titles table means 
**              "no sales yet" not "sales unknown"
*/
update titles
    set total_sales = isnull(total_sales, 0) + (select sum(qty)
    from inserted
    where titles.title_id = inserted.title_id)
    where title_id in (select title_id from inserted)
/* remove all values being deleted or updated */
update titles
    set total_sales = isnull(total_sales, 0) - (select sum(qty)
    from deleted
    where titles.title_id = deleted.title_id)
    where title_id in (select title_id from deleted)