salesdetail is defined as follows:
create table salesdetail (stor_id char(4) not null references sales(stor_id), ord_num numeric(6,0) references sales(ord_num), title_id tid not null references titles(title_id), qty smallint not null, discount float not null)
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 the following 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 num_sold = isnull(num_sold, 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 num_sold = isnull(num_sold, 0) - (select sum(qty) from deleted where titles.title_id = deleted.title_id) where title_id in (select title_id from deleted)
Table B-5 lists the contents of salesdetail:
stor_id |
ord_num |
title_id |
qty |
discount |
---|---|---|---|---|
7896 |
100014 |
TC3218 |
75 |
40.000000 |
7896 |
100014 |
TC7777 |
75 |
40.000000 |
7131 |
100017 |
TC3218 |
50 |
40.000000 |
7131 |
100017 |
TC7777 |
80 |
40.000000 |
5023 |
100020 |
TC3218 |
85 |
40.000000 |
8042 |
100016 |
PS3333 |
90 |
45.000000 |
8042 |
100016 |
TC3218 |
40 |
45.000000 |
8042 |
100016 |
PS2106 |
30 |
45.000000 |
8042 |
100023 |
PS2106 |
50 |
45.000000 |
8042 |
100015 |
PS2106 |
31 |
45.000000 |
8042 |
100016 |
MC3021 |
69 |
45.000000 |
5023 |
100009 |
PC1035 |
1000 |
46.700000 |
5023 |
100007 |
BU2075 |
500 |
46.700000 |
5023 |
100007 |
BU1032 |
200 |
46.700000 |
5023 |
100007 |
BU7832 |
150 |
46.700000 |
5023 |
100007 |
PS7777 |
125 |
46.700000 |
5023 |
100018 |
TC7777 |
1000 |
46.700000 |
5023 |
100018 |
BU1032 |
1000 |
46.700000 |
5023 |
100018 |
PC1035 |
750 |
46.700000 |
7131 |
100004 |
BU1032 |
200 |
46.700000 |
7066 |
100012 |
BU7832 |
100 |
46.700000 |
7066 |
100021 |
PS7777 |
200 |
46.700000 |
7066 |
100021 |
PC1035 |
300 |
46.700000 |
7066 |
100021 |
TC7777 |
350 |
46.700000 |
5023 |
100025 |
PS2091 |
1000 |
46.700000 |
7067 |
100019 |
PS2091 |
200 |
46.700000 |
7067 |
100019 |
PS7777 |
250 |
46.700000 |
7067 |
100019 |
PS3333 |
345 |
46.700000 |
7067 |
100019 |
BU7832 |
360 |
46.700000 |
5023 |
100020 |
PS2091 |
845 |
46.700000 |
5023 |
100020 |
PS7777 |
581 |
46.700000 |
5023 |
100027 |
PS1372 |
375 |
46.700000 |
7067 |
100019 |
BU1111 |
175 |
46.700000 |
5023 |
100020 |
BU7832 |
885 |
46.700000 |
5023 |
100025 |
BU7832 |
900 |
46.700000 |
5023 |
100007 |
TC4203 |
550 |
46.700000 |
7131 |
100004 |
TC4203 |
350 |
46.700000 |
7896 |
100014 |
TC4203 |
275 |
46.700000 |
7066 |
100021 |
TC4203 |
500 |
46.700000 |
7067 |
100019 |
TC4203 |
512 |
46.700000 |
7131 |
100004 |
MC3021 |
400 |
46.700000 |
5023 |
100007 |
PC8888 |
105 |
46.700000 |
5023 |
100018 |
PC8888 |
300 |
46.700000 |
7066 |
100021 |
PC8888 |
350 |
46.700000 |
7067 |
100019 |
PC8888 |
335 |
46.700000 |
7131 |
100017 |
BU1111 |
500 |
46.700000 |
7896 |
100014 |
BU1111 |
340 |
46.700000 |
5023 |
100007 |
BU1111 |
370 |
46.700000 |
5023 |
100025 |
PS3333 |
750 |
46.700000 |
8042 |
100014 |
BU7832 |
300 |
51.700000 |
8042 |
100013 |
BU2075 |
150 |
51.700000 |
8042 |
100013 |
BU1032 |
300 |
51.700000 |
8042 |
100013 |
PC1035 |
400 |
51.700000 |
8042 |
100016 |
PS7777 |
180 |
51.700000 |
8042 |
100014 |
TC4203 |
250 |
51.700000 |
8042 |
100013 |
TC4203 |
226 |
51.700000 |
8042 |
100013 |
MC3021 |
400 |
51.700000 |
8042 |
100023 |
BU1111 |
390 |
51.700000 |
5023 |
100003 |
MC3021 |
5000 |
50.000000 |
5023 |
100018 |
PC8888 |
2000 |
50.000000 |
5023 |
100018 |
BU2075 |
2000 |
50.000000 |
5023 |
100010 |
PC1035 |
2000 |
50.000000 |
5023 |
100022 |
PC1035 |
2000 |
50.000000 |
5023 |
100022 |
PS7777 |
1500 |
50.000000 |
5023 |
100025 |
BU2075 |
3000 |
50.000000 |
5023 |
100025 |
TC7777 |
1500 |
50.000000 |
5023 |
100026 |
BU2075 |
3000 |
50.000000 |
5023 |
100026 |
BU2075 |
3000 |
50.000000 |
5023 |
100020 |
PS3333 |
2687 |
50.000000 |
5023 |
100020 |
TC7777 |
1090 |
50.000000 |
5023 |
100020 |
PC1035 |
2138 |
50.000000 |
5023 |
100027 |
MC2222 |
2032 |
50.000000 |
5023 |
100027 |
BU1111 |
1001 |
50.000000 |
5023 |
100022 |
BU1111 |
1100 |
50.000000 |
5023 |
100018 |
BU7832 |
1400 |
50.000000 |
5023 |
100009 |
TC4203 |
2700 |
50.000000 |
5023 |
100010 |
TC4203 |
2500 |
50.000000 |
5023 |
100018 |
TC4203 |
3500 |
50.000000 |
5023 |
100009 |
MC3021 |
4500 |
50.000000 |
5023 |
100007 |
MC3021 |
1600 |
50.000000 |
5023 |
100018 |
MC3021 |
2550 |
50.000000 |
5023 |
100022 |
MC3021 |
3000 |
50.000000 |
5023 |
100026 |
MC3021 |
3200 |
50.000000 |
5023 |
100009 |
BU2075 |
2200 |
50.000000 |
5023 |
100010 |
BU1032 |
1500 |
50.000000 |
5023 |
100027 |
PC8888 |
1005 |
50.000000 |
7896 |
100013 |
BU2075 |
42 |
50.500000 |
7896 |
100013 |
PC1035 |
25 |
50.500000 |
7131 |
100005 |
BU2075 |
35 |
50.500000 |
7067 |
100011 |
PC1035 |
34 |
50.500000 |
7067 |
100011 |
TC4203 |
53 |
50.500000 |
8042 |
100015 |
BU2075 |
30 |
55.500000 |
8042 |
100015 |
BU1032 |
94 |
55.500000 |
7066 |
100001 |
BU2075 |
200 |
57.200000 |
7896 |
100013 |
TC4203 |
350 |
57.200000 |
7066 |
100001 |
TC4203 |
230 |
57.200000 |
7066 |
100001 |
MC3021 |
200 |
57.200000 |
7131 |
100005 |
MC3021 |
137 |
57.200000 |
7067 |
100011 |
MC3021 |
270 |
57.200000 |
7067 |
100011 |
BU2075 |
230 |
57.200000 |
7131 |
100005 |
BU1032 |
345 |
57.200000 |
7067 |
100011 |
BU1032 |
136 |
57.200000 |
8042 |
100015 |
TC4203 |
300 |
62.200000 |
8042 |
100015 |
MC3021 |
270 |
62.200000 |
8042 |
100015 |
PC1035 |
133 |
62.200000 |
5023 |
100002 |
TC4203 |
2500 |
60.500000 |
5023 |
100002 |
BU2075 |
4000 |
60.500000 |
6380 |
100028 |
BU2075 |
200 |
57.200000 |
6380 |
100028 |
MC3021 |
250 |
57.200000 |
6380 |
100029 |
PS3333 |
200 |
46.700000 |
6380 |
100029 |
PS7777 |
500 |
46.700000 |
6380 |
100029 |
TC3218 |
125 |
46.700000 |
6380 |
100014 |
BU2075 |
135 |
46.700000 |
6380 |
100014 |
BU1032 |
320 |
46.700000 |
6380 |
100014 |
TC4203 |
300 |
46.700000 |
6380 |
100014 |
MC3021 |
400 |
46.700000 |