實(shí)驗(yàn)11 觸發(fā)器

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

2、rtasif not exists(select * from student,inserted where student.sno=inserted.sno)beginprint '插入信息的學(xué)號(hào)不在學(xué)生表中！'if not exists(select * from course,inserted where o=o)print '插入信息的課程號(hào)不在課程表中！'rollback endelsebegin if not exists(select * from course,inserted where o=o)beginprint '插入信息的課程號(hào)

3、不在課程表中！'rollbackendend執(zhí)行：、insert into SCvalues('20110112','001','78')刪除外鍵約束：alter table SCdrop constraint FK_SC_Sno_182C9B23、insert into SCvalues('20110002','001','78')、insert into SCvalues('20110002','006','78')(2）為Course表創(chuàng)

4、建一個(gè)觸發(fā)器Course_del，當(dāng)刪除了Course表中的一條課程信息時(shí)，同時(shí)將表sc表中相應(yīng)的學(xué)生選課記錄刪除掉。create trigger course_delon courseafter deleteas if exists(select * from sc,deleted where o=o) begin delete from scwhere o in(select cno from deleted) enddelete from Coursewhere Cno='003'select * from SC(3）在Course表中添加一個(gè)平均成績(jī)avg_Grade字

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

6、 avg_grade=(select avg(grade) from sc where o=o group by cno)endupdate SCset Grade='90'where Sno='20050001' and Cno='001'（4）測(cè)試上述三個(gè)觸發(fā)器。測(cè)試過(guò)程在（1）、（2）、（3）中均給出2.創(chuàng)建instead of 觸發(fā)器(1）創(chuàng)建一視圖Student_view,包含學(xué)號(hào)、姓名、課程號(hào)、課程名、成績(jī)等屬性，在Student_view上創(chuàng)建一個(gè)觸發(fā)器Grade_moidfy，當(dāng)對(duì)Student_view中的學(xué)生的成績(jī)進(jìn)行修改時(shí)，

7、實(shí)際修改的是sc中的相應(yīng)記錄。create view Student_viewasselect s.Sno,Sname,c.Cno,Cname,Gradefrom Student s,Course c,SCwhere s.Sno=SC.Sno and c.Cno=SC.Cnoselect * from Student_viewcreate trigger Grade_moidfyon Student_viewinstead of updateasif UPDATE(Grade)beginupdate SC set Grade=(select Grade from inserted) where

8、Sno=(select Sno from inserted) andCno=(select Cno from inserted)endupdate Student_viewset Grade=40where Sno='20110001' and Cno='002'select * from Student_viewselect * from SC(2）在SC表中插入一個(gè)getcredit字段（記錄某學(xué)生，所選課程所獲學(xué)分的情況），創(chuàng)建一個(gè)觸發(fā)器ins_credit，當(dāng)更改（注：含插入時(shí)）SC表中的學(xué)生成績(jī)時(shí)，如果新成績(jī)大于等于60分，則該生可獲得這門課的學(xué)分，且

9、該學(xué)分須與Course表中的值一致；如果新成績(jī)小于60分，則該生未能獲得學(xué)分，修改值為0。 alter table SCadd getcredit smallintselect * from SCupdate SCset getcredit=(select credit from Coursewhere SC.Cno=Course.Cno)where Grade>=60update SCset getcredit=0where Grade<60create trigger ins_credit on SCinstead of update,insertasbegindeclare

10、s_no char(8),c_no char(3),new_grade smallint,cred smallintselect s_no=sno,c_no=cno,new_grade=grade from insertedselect cred=credit from course where cno=c_noif(new_grade>=60)begindelete from SC where sno=s_no and cno=c_noinsert into SC values(s_no,c_no,new_grade,cred)endelsebegindelete from SC wh

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

12、_modify中的定義內(nèi)容。sp_helptext Grade_moidfy(3) 使用select語(yǔ)句查看觸發(fā)器Grade_modify的定義內(nèi)容。select o.id,c.textfrom sysobjects o inner join syscomments con o.id=c.idwhere o.type='TR' and ='Grade_modify'（4）用系統(tǒng)存儲(chǔ)過(guò)程sp_depends查看觸發(fā)器Grade_modify的相關(guān)性。sp_depends Grade_modify（5）將sc_insert觸發(fā)器改為instead of

13、觸發(fā)器，實(shí)現(xiàn)的功能不變。drop trigger sc_insertcreate trigger sc_inserton scinstead of insertasif not exists(select * from student,inserted where student.sno=inserted.sno)beginprint '插入信息的學(xué)號(hào)不在學(xué)生表中！'if not exists(select * from course,inserted where o=o)print '插入信息的課程號(hào)不在課程表中！'rollback endelsebegin

14、if not exists(select * from course,inserted where o=o)beginprint '插入信息的課程號(hào)不在課程表中！'rollbackendendinsert into SCvalues('20110005','001','78','6')（6）將觸發(fā)器sc_insert刪除。drop trigger sc_insert4.使用SQL Server Management Studio管理存儲(chǔ)過(guò)程（1）在SQL Server Management Studio中重新創(chuàng)建剛刪除的觸發(fā)器sc_insert選中SC表，展開右擊“觸發(fā)器”新建觸發(fā)器出現(xiàn)如下界面：編寫余下的SQL語(yǔ)句：CREATE TRIGGER sc_insert ON SC INSTEAD OF INSERTAS BEGINif not exists(select * from student,inserted where student.sno=inserted.sno)beginprint '插入信息的學(xué)號(hào)不在學(xué)生表中！'if not exists(select * from course,inserted where o=o)print '插入信息的課