試驗11觸發(fā)器講解

1、實驗 觸發(fā)器、實驗?zāi)康?）理解觸發(fā)器的用途、類型和工作原理2）掌握利用 T-SQL語句創(chuàng)建和維護(hù)觸發(fā)器的方法3）掌握利用企業(yè)管理器創(chuàng)建、維護(hù)觸發(fā)器的方法、實驗內(nèi)容1. 創(chuàng)建 after 觸發(fā)器triggersc_insert（1 ）創(chuàng)建一個在插入時觸發(fā)的觸發(fā)器 sc_insert, 當(dāng)向 sc表插入數(shù)據(jù)時，須確保插入的學(xué) 號已在 Student 表中存在，并且還須確保插入的課程號在 Course 表中存在；若不存在，則給 出相應(yīng)的提示信息， 并取消插入操作， 提示信息要求指明插入信息是學(xué)號不滿足條件還是課 程號不滿足條件（注： Student 表與 sc 表的外鍵約束要先取消） createo

2、n scafterinsertasif notexists(select * from studentwhere student . sno, inserted= inserted . sno )beginprint if 插入信息的學(xué)號不在學(xué)生表中！course . cnoexists( select * from= inserted. cno )print 插入信息的課程號不在課程表中！ rollbacknotcourse , insertedwhereendelsebeginifcourse . cnonot exists( select * from course , inserted

3、 = inserted. cno )wherebeginprint 插入信息的課程號不在課程表中！ rollbackendend執(zhí)行： 、 insertinto SCvalues ( 20110112 , 001 , 78 )刪除外鍵約束：alter table SCdrop constraintFK SC Sno 182C9B23 、 insert into SCvalues ( 20110002 , 001 , 78 ) 、 insert into SCvalues ( 20110002 , 006 , 78 )(2 )為Course 表創(chuàng)建一個觸發(fā)器 Course_del ，當(dāng)刪除了 C

4、ourse表中的一條課程信息時， 同時將表 sc表中相應(yīng)的學(xué)生選課記錄刪除掉。create trigger course_del on courseafter delete as if exists( select * from sc , deleted where sc . cno = deleted . cno ) begindelete from scwhere sc . cno in( select cno from deleted )enddelete from Coursewhere Cno =003select * from SC(3 )在 Course 表中添加一個平均成績 a

5、vg_Grade 字段(記錄每門課程的平均成績) ，創(chuàng)建 一個觸發(fā)器 Grade_modify ，當(dāng)SC表中的某學(xué)生的成績發(fā)生變化時， 則Course 表中的平均成績 也能及時相應(yīng)的發(fā)生改變。alter table Course add avg_Grade smallintupdate Courseset avg_Grade =( select AVG( Grade ) from SCwhere SC. Cno = Course . Cno)select* from Coursecreate trigger Grade_modifyon scafter updateasif update (

6、grade )beginupdate courseendset avg_grade=( select avg ( grade )from sc where course . cno =sc . cnogroup by cno )update SCset Grade =90where Sno = 20050001and Cno =0014）測試上述三個觸發(fā)器。測試過程在（ 1）、（ 2）、（ 3）中均給出2. 創(chuàng)建 instead of 觸發(fā)器(1 )創(chuàng)建一視圖 Student_view, 包含學(xué)號、姓名、課程號、課程名、成績等屬性，在 Student_view 上創(chuàng)建一個觸發(fā)器 Grade_m

7、oidfy ，當(dāng)對 Student_view 中的學(xué)生的成績進(jìn)行修改 時，實際修改的是 sc 中的相應(yīng)記錄。create view Student_viewasselect s. Sno , Sname , c. Cno, Cname , Gradefrom Student s, Course c, SC where s. Sno =SC. Sno and c. Cno =SC. Cno select * from Student_viewcreate trigger Grade_moidfyon Student_viewinstead of updateasif UPDATE( Grade

8、)beginupdate SCset Grade =( select Grade from inserted ) where Sno =( selectSno from inserted) andCno=( selectCno from inserted)endupdate Student_view set Grade =40where Sno = 20110001 and Cno =002selectfrom Student_viewselect* fromSC（2）在 SC表中插入一個 getcredit 字段（記錄某學(xué)生，所選課程所獲學(xué)分的情況） ，創(chuàng)建一個 觸發(fā)器 ins_credit

9、 ，當(dāng)更改（注：含插入時） SC表中的學(xué)生成績時，如果新成績大于等于60分，則該生可獲得這門課的學(xué)分，且該學(xué)分須與Course 表中的值一致；如果新成績小于60分，則該生未能獲得學(xué)分，修改值為 0。alter table SCadd getcredit smallintselectfrom SC=60where Gradeupdate SC set getcredit =0 where Grade =60 )begindeletefromSCwheresno = s_no and cno = c_noinsertintoSCvalues( s_no , c_no , new_grade , c

10、red )endelsebegindeletefromSCwheresno = s_no and cno = c_noinsertintoSCvalues( s_no , c_no , new_grade , 0)endendinsert into SC( sno , cno , grade ) values ( 20081800 , 002 , 85 )（3）測試上述兩個觸發(fā)器。 測試結(jié)果在（ 1）、（ 2）中均已給出3. 使用 T-SQL語句管理和維護(hù)(1 )用系統(tǒng)存儲過程 sp_helptrigger 查看觸發(fā)器 Grade_modify 的相關(guān)信息 sp_helptrigger Stu

11、dent_view(2 )使用系統(tǒng)存儲過程 sp_helptext 查看觸發(fā)器 Grade_modify 中的定義內(nèi)容。 sp_helptext Grade_moidfy(3) 使用 select 語句查看觸發(fā)器 Grade_modify 的定義內(nèi)容。 select o. id , c. textfrom sysobjects o inner join syscomments c on o. id =c. idwhere o. type =TR and o. name =Grade_modify觸發(fā)器，實現(xiàn)的功能不變。（ 4）用系統(tǒng)存儲過程 sp_depends 查看觸發(fā)器 Grade_mod

12、ify 的相關(guān)性。 sp_depends Grade_modify( 5)將 sc_insert 觸發(fā)器改為 instead of drop trigger sc_insertcreate trigger sc_insert on scinstead of insertasif not exists(select * from student, insertedwhere student . sno= inserted. sno )beginprint 插入信息的學(xué)號不在學(xué)生表中！if notexists( select * fromcourse ,insertedwherecourse .

13、cno = inserted. cno )print 插入信息的課程號不在課程表中！ rollbackendelsebeginif not exists( select * from course , inserted where course . cno = inserted . cno )beginprint 插入信息的課程號不在課程表中！ rollbackendendinsert into SCvalues ( 20110005 , 001 , 78 , 6 )( 6)將觸發(fā)器 sc_insert 刪除。 drop triggersc insert4. 使用 SQL Server Man

14、agement Studio 管理存儲過程（ 1）在 SQL Server Management Studio 中重新創(chuàng)建剛刪除的觸發(fā)器 sc_insert 選中 SC表，展開右擊“觸發(fā)器”新建觸發(fā)器出現(xiàn)如下界面：編寫余下的 SQL 語句：CREATE TRIGGER sc_insertON SCINSTEAD OF INSERTASBEGINif not exists( select * from student , inserted where student . sno = inserted. sno )beginprint 插入信息的學(xué)號不在學(xué)生表中！ if not exists( select * from course , inserted where course . cno = inserted . cno )print 插入信息的課程號不在課程表中！ rollbackendelsebeginif n