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)