Advanced SQL Programming Fall 2002
In-class/take-home exercises for Nov. 11th
1. Trigger to allow adding new customers but not existing customers -
somewhat artificial, since index on custnum prevents this error before
the trigger can work; using name for demonstration purposes.
sp_helptext custcheck_trig
create trigger custcheck_trig
on customer
for insert
as
if (select count(*)
from customer c, inserted i
where c.lname = i.lname) > 1
begin
rollback
print "Customer already in database."
end
else
print "New customer added."
Tests (existing customer, new customer):
1> insert into customer
2> values ('111111111', 'Piper', 'Peter', '987 Pepper Lane', 'Pecksville',
3> 'OH', '12345', '555', '555-5555', '2')
4> insert into customer
5> values ('222222222', 'Peters', 'John', '123 Pumpkin Patch', 'Pumpkin
Hollow',
6> 'PA', '13579', '777', '777-7777', '1')
7> go
New customer added.
(1 row affected)
Customer already in database.
(0 rows affected)
1> select * from customer
2> where lname in ('Peters', 'Piper')
3> go
custnum lname fname
street city
state
zip areacode phone status
--------- -------------------- --------------------
---------------------------------------- --------------------
-----
---------- -------- ------- ------
776667778 Peters Pete
45 N. Maine Macedon NY
14502 800 5557777 2
111111111 Piper Peter
987 Pepper Lane Pecksville OH
12345 555 555-555 2
(2 rows affected)
1>
2. Create a trigger that deletes an order once all items shipped.
(Note: Using simpler tables for illustration: tblBudget and tblSpending.
Will delete appropriate row in tblSpending if budget amount changes, but
not if any remaining budget amount for same region is zero.)
Starting tables:
1> select * from tblBudget
2> go
regionID budgetAmt
-------- ------------------------
7 1,000.00
2 500.00
3 3,000.00
1 5,000.00
4 500.00
1 0.00
(6 rows affected)
1> select * from tblSpending
2> go
regionID spendingAmt
-------- ------------------------
1 100.00
1 100.00
2 50.00
4 300.00
(4 rows affected)
1>
create trigger dropSpend_trig
on tblBudget
for update
as
if update(budgetAmt)
begin
/* check for null (actually, zero) budgetAmt first - do not delete */
if exists (select *
from tblBudget, inserted
where tblBudget.regionID = inserted.regionID
and tblBudget.budgetAmt = 0)
begin
rollback transaction
print "Some budget line not specified - no deletion"
end
else
/* Not necessary to check @@rowcount if deleting based on primary key */
begin
delete tblSpending
from tblSpending, tblBudget, inserted
where tblBudget.regionID = inserted.regionID
and tblSpending.regionID = tblBudget.regionID
print "Changed row(s) in tblSpending deleted."
end
end
else
/* For any update on a different column */
print "Updated something other than budgetAmt - no change"
/* Test with update on different col.: No change message */
/* Test with update on budgetAmt and no zero value: */
/* corresponding ID row deleted from tblSpending. */
/* Test with update on budgetAmt with zero value in some row: */
/* update OK on other row, but no deletion. */
Tests:
1> -- Update a region ID in tblBudget
2> update tblBudget
3> set regionID = 6 where regionID = 7
4> go
Updated something other than budgetAmt - no change
(1 row affected)
1> -- Update budget amount for region 4
2> update tblBudget
3> set budgetAmt = 750
4> where regionID = 4
5> go
Changed row(s) in tblSpending deleted.
(1 row affected)
1> -- Check what happened in tblSpending
2> select * from tblSpending
3> go
regionID spendingAmt
-------- ------------------------
1 100.00
1 100.00
2 50.00
(3 rows affected)
1>
1> -- Update budget amount for one of the region 1 rows
2> update tblBudget
3> set budgetAmt = 2000
4> where regionID = 1
5> and budgetAmt > 0 -- could have specified actual amount instead
6> go
Some budget line not specified - no deletion
1> -- Check what happened in tblSpending
2> select * from tblSpending
3> go
regionID spendingAmt
-------- ------------------------
1 100.00
1 100.00
2 50.00
(3 rows affected)
1> -- Check final state of tblBudget
2> select * from tblBudget
3> go
regionID budgetAmt
-------- ------------------------
6 1,000.00
2 500.00
3 3,000.00
1 5,000.00
4 750.00
1 0.00
(6 rows affected)
-- Note: Update was rolled back in addition to no deletion. Could have
-- written the trigger without "rollback transaction" and just printed
-- the "no deletion" message.
3. Statement to create an invoice for new or changed order
(Used select all - the select statement should be pared down to what is
needed, and formatted appropriately.)
1> create trigger order_trig
2> on orderdetail
3> for insert, update
4> as
5> select custnum,
6> convert(char(4), o.ordnum) Ord#,
7> convert(char(12), orderdate, 101) OrderDate,
8> convert(char(18), prodname) Product,
-- Note d.qty, not plain qty, to distinguish between d.qty and i.qty
9> convert(char(4), d.qty) Qty,
10> convert(char(8), price) Price,
11> convert(char(10), d.qty*price) Total
12> from ordermaster o, orderdetail d, product p, inserted i
13> where o.ordnum = d.ordnum
14> and d.prodnum = p.prodnum
15> and i.ordnum = d.ordnum
16> go
1>
Tests: Add an item to an existing order; change qty for an existing item.
1> -- Insert a product for order # 99
2> insert into orderdetail
3> values (99, 2049, 10, null)
4> -- Change the quantity for product #2050
5> update orderdetail
6> set qty = 1000
7> where ordnum = 99
8> and prodnum = 2050
9> go
custnum Ord# OrderDate Product Qty Price Total
--------- ---- ------------ ------------------ ---- -------- ----------
776677778 99 01/02/1999 paper dolls 6 19.99 119.94
776677778 99 01/02/1999 tax time 2222 49.99 111077.78
776677778 99 01/02/1999 more paper dolls 10 19.99 199.90
(3 rows affected)
(1 row affected)
custnum Ord# OrderDate Product Qty Price Total
--------- ---- ------------ ------------------ ---- -------- ----------
776677778 99 01/02/1999 paper dolls 6 19.99 119.94
776677778 99 01/02/1999 tax time 1000 49.99 49990.00
776677778 99 01/02/1999 more paper dolls 10 19.99 199.90
(3 rows affected)
(1 row affected)
4. Total amount paid per order, including shipping and gift charges.
1> select convert(char(4), o.ordnum) Ord#,
2> convert(char(12), sum(qty*price)) Subtotal,
3> convert(char(12), convert(money, sum(qty*weight*5))) Shipping,
4> convert(char(12), sum(qty*giftadd)) GiftWrap,
5> convert(char(12),
6> convert(money,
7> sum(qty*price)+sum(qty*weight*5)+sum(qty*giftadd))) Total
8> from product p, orderdetail od, ordermaster o
9> where o.ordnum = od.ordnum
10> and p.prodnum = od.prodnum
11> group by o.ordnum
12> go
Ord# Subtotal Shipping GiftWrap Total
---- ------------ ------------ ------------ ------------
81 567.95 99.50 28.50 695.95
85 1649.75 307.50 72.50 2029.75
86 642.78 478.00 32.50 1153.28
87 1426.60 540.00 61.00 2027.60
89 99.98 25.00 5.00 129.98
90 89.99 12.50 4.00 106.49
91 879.88 27.50 44.00 951.38
92 166616.67 41662.50 8332.50 216611.67
93 269.94 75.00 13.50 358.44
94 174.99 157.50 9.00 341.49
95 127.98 98.00 6.50 232.48
99 50309.84 12700.00 2516.00 65525.84
(12 rows affected)