試驗五-觸發(fā)器和存儲過程

1、實驗五：觸發(fā)器和存儲過程一.實驗目的：理解觸發(fā)器和存儲過程的含義，掌握用 SQL語句實 現(xiàn)觸發(fā)器和存儲過程的編寫，并初步掌握什么情況下使用事務。二.實驗內(nèi)容：有一個小型的圖書管理數(shù)據(jù)庫，包含的表為：bookstore （bookid , bookname , bookauthor , purchasedate , state ）；-圖書庫存表borrowcard （cardid , ownername ）;-借書證表borrowlog （cardid , bookid , borrowdate , returndate ）;- 借書記錄表寫一個存儲過程，實現(xiàn)借書操作，要求有事務處理。（1）讀者

2、借書， 要先設置書籍不在庫標志state （借出），然后增加借書記錄，在同一事務中完成。（2）要求在事務執(zhí)行過程中引入錯誤觸發(fā)事件，以此體 會事務的錯誤保護機制和事務編程的作用。（3）要求用觸發(fā)器實現(xiàn)表的完整性控制。三、操作與運行.創(chuàng)建圖書數(shù)據(jù)庫：create table bookstore（bookid int not null primary key,bookname char(20),bookauthor char(20),purchasedate datetime,state char(4)create table borrowcard(cardid int not null prim

3、ary key,ownername char(20)create table borrowlog(cardid int not null,bookid int not null,borrowdate datetime,returndate datetime,primary key(cardid,bookid),一foreign key(cardid)references borrowcard(cardid),一foreign key(bookid)references bookstore(bookid)通過以上語句，可以看到數(shù)據(jù)庫中的表建立成功。.創(chuàng)建存儲過程：create proc book

4、_borrowmycardid_in int,mybookid_in int,str_out char(30) outputasbeginif not exists(select * from borrowcard where cardid=mycardid_in) beginset str_out=該讀者不存在returnendif(select state from bookstorewherebookid=mybookid_in尸 借出beginset str_out= 該書以借出endbegin traninsert into borrowlog values(mycardid_in,

5、mybookid_in, getdate(),null)if error0beginrollback transet str_out=執(zhí)行過程中遇到錯誤！returnendupdate bookstore set state=借出where bookid=myboo kid_inif error0beginrollback transet str_out=執(zhí)行過程中遇到錯誤!,returnendif error=0begincommit transet str_out=借書成功!,return 1elsebeginrollback transet str_out=執(zhí)行過程中遇到錯誤!,retu

6、rnendEnd查看執(zhí)行結果:執(zhí)行存儲過程：當違反參照完整性時:declare str_outchar(30)exec book borrow1114060119,106,str_outoutputprint執(zhí)行情況+ str_out當圖書已借出時執(zhí)行結果會是:declare str_out char(30)outputexec book_borrow 1114060114,102,str_outprint 執(zhí)行情況+ str_out當正常執(zhí)行時（即不違反完整性時）：原先的借書記錄有:正常借書時：declare str_out char(30) exec book_borrow 1114060

7、116,105,str_out outputprint執(zhí)行情況（斫題晌的行數(shù)為1行）（斫器響的行數(shù)為1行）（所題響的行粒為1行）執(zhí)行情況借書成功!+ str_out查看借書記錄:ST1E阻串鼻I1MHBLM LDP UHlRIlIi Lbt lit ificani |-EP feuitiimu- LmfllECniDIL NHiqViMMl-DI-r 9V3GBI D&.W m：V-Q3-C5!2川 U5- IE： |j：a： n. M7T ltl_L查看圖書在庫狀態(tài):由此可知借書成功。.創(chuàng)建觸發(fā)器:create trigger delete_borrowlogonbookstoreforde

8、leteasbeginfrodelete from borrowlog where bookid in(select bookid m deleted)End執(zhí)行:deletefrom bookstorewhere bookid=102結果:（所影響的行數(shù)為1行）查看結束記錄情況:In“oan理國mx卜。1如|由力HL1i Halidalj99. W? MU可知刪除觸發(fā)器創(chuàng)建成功。create trigger update_borrowlogon bookstorefor updateas begindeclare old_bookid int,new_bookid intselect old

9、_bookid=bookid from deletedselect new_bookid=bookid from insertedbookiupdate borrowlog set bookid=new_bookid whered=old_bookidEnd執(zhí)行：update bookstoreset bookid=119where bookid=105結果：11- 1，口，口a X mk i i_，w4三上，I| f 0 來檢查。創(chuàng)建觸發(fā)器時剛開始不能創(chuàng)建成，根據(jù)提示知道原來在建表時已經(jīng) 設置了外鍵，所以棄掉就可以了。五.思考題：如何通過系統(tǒng)的設置實現(xiàn)類似的功能， 而不需觸發(fā)器？ 答：可以使 用企業(yè)管理其中可視化的建表方法，也可以使用 sql語句來在表中增加 外鍵約束就可以了