1、存儲(chǔ)過程與觸發(fā)器課堂練習(xí)及答案1.創(chuàng)建一個(gè)存儲(chǔ)過程,顯示所有價(jià)格在15美元以下的書的書名,類型,價(jià)格。CREATE PROCEDURE show_titleASSELECT title,type,priceFROM titlesWHERE price < 15GOEXEC show_title2.把價(jià)格作為參數(shù),創(chuàng)建一個(gè)能顯示在某兩個(gè)指定價(jià)格之間的書的書名,類型,價(jià)格。CREATE PROCEDURE show_title2price1 money,price2 moneyASSELECT title,type,priceFROM titlesWHERE price between pr

2、ice1 and price2GOshow_title2 12,203.使用OUTPUT參數(shù),創(chuàng)建一個(gè)計(jì)算圓柱體體積的存儲(chǔ)過程。并執(zhí)行它。CREATE PROCEDURE comp_arear smallint,h smallint,result decimal(10,2 OUTPUTASSET result = PI(*SQUARE(r* hGODECLARE answer decimal(10,2EXECUTE comp_area 2,3, answer OUTPUTSELECT 'The result is: ', answer4.A 建立price_change表,準(zhǔn)備

3、用來存放書的價(jià)格變化信息,有以下幾列:title_id, type,old_price, new_price, change_date, operator。B 建立一個(gè)更新觸發(fā)器,一旦titles表發(fā)生更新,立即把相關(guān)信息存放到price_change表中。create table price_change(title_id varchar(20,type varchar(20,old_price money,new_price money,change_date datetime,operator varchar(20gocreate trigger tri_priceon titlesfo

4、r updateasinsert into price_changeselect o.title_id,o.type,o.price,n.price,getdate(,user_name(from deleted o JOIN inserted nON o.title_id = n.title_idgoupdate titles set price = price*1.15.修改練習(xí)4,使得只有當(dāng)price列被更新時(shí),才會(huì)觸發(fā)觸發(fā)器。create trigger tri_priceon titlesfor updateasif update(pricebegininsert into pric

5、e_changeselect o.title_id,o.type,o.price,n.price,getdate(,user_name(from deleted o JOIN inserted nON o.title_id = n.title_idendgo6.創(chuàng)建一個(gè)存放書的編號(hào)、書名、類型、價(jià)格、對(duì)應(yīng)作者的編號(hào)、姓名、電話、住址的視圖。use pubsgocreate view v_titledetailasselect t.title_id, title, type, price, a.au_id, au_lname, au_fname, phone, addressfrom title

6、s t,titleauthor ta,authors awhere t.title_id = ta.title_id and ta.au_id = a.au_id7.為這個(gè)視圖創(chuàng)建一個(gè)Instead of更新觸發(fā)器,把對(duì)視圖的更新放到觸發(fā)器里面來做。(假設(shè),我們只允許更新這個(gè)視圖的某幾個(gè)列:price, phone, addresscreate trigger tri_titledetailon v_titledetailinstead of updateasdeclare price money,phone varchar(20,address varchar(40,title_id var

7、char(20,au_id varchar(20select title_id = title_id, price = price, au_id = au_id, phone = phone ,address = address from insertedupdate titles set price = price where title_id = title_idupdate authors set phone = phone, address = address where au_id = au_idgo8.添加測(cè)試數(shù)據(jù)。更新v_titledetail,把書編號(hào)為L(zhǎng)I1234的書的價(jià)格改為2