实验的七触发器.docx
《实验的七触发器.docx》由会员分享，可在线阅读，更多相关《实验的七触发器.docx（16页珍藏版）》请在冰豆网上搜索。
实验的七触发器
实验七、触发器
一、实验目的
（1）理解触发器的用途、类型和工作原理。
（2）掌握利用T-SQL语句创建和维护触发器的方法。
（3）掌握利用SQLServerManagementStudio创建、维护触发器的方法。
二、实验内容、
1、创建AFTER触发器
（1）创建一个在插入时触发的触发器sc_insert当向SC表插入数据时，须确保插入的学号已在Student表中存在，并且还须确保插入课程号在Course表中存在；若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件。
（注：
Student表与SC表的外键约束要先取消。
）
ifexz.3匸sselectnamefromsysol?
jectswherename^1scinsertRtype=1TRr:
draptriggersc_±nsert
宀?
°、
头用标准文案^…•三：
m•二
onsc
forinsert
as
declare^sn.ovarchar110^cnovarcriar10
select^snc-sno,@cno^cno
fromINSERTED
ifnotexists（selectsno
fromstudent
where§sno=sno
kegin
printL学号不存在！
J
rollbacktran
end]
一一ELSEPRINT
elseifno匸2xz_st3selecteno
fromcourse
where@cno=cno
begin
rollbacktran
print，课程号不存在!
1j
end
go
<|il
匕消息
命令已成功完咸.
（2）为Course表创建一个触发器Course_de,当删除了Course表
中的一条课程信息时，同时将表SC中相应的学生选课记录也删除。
ifexistsselectTiamefromgysotojectswherename='courje^delrandtype='TR1droptriggercoursedel
9口
createtriggercouraedel
onacur^e
forDELETE
as
begintransaction
declare3enochar8}
select@cne=cno
frarnDELETED
DELETE
FROMsc
wherecnQ=Scno
committran
<
75消息
命令已咸功完成.
（3）在Course表中添加一个平均成绩avg_modify,当SC表中某学
生的成绩发生变化时，则Course表中的平均成绩也能及时相应发生改变。
I
altertablecourse
addavg_grade
/*alcer匸ablecourse
dropcoluwiavg_grade*/
ifexistsselectnamefromsysobjectswhere=|grade_raodify1and匸yp^；=・TR?
droptriggergrade_nio[lify
gQ:
i*
createtriggergrade^mfidify
□nsc
forupdate
as
decla工皂®avgint.r@cnocharS
select@>cn口=cdq『@avg=avg（gradei
fromINSERTED
groupbyeno
UPDATEcourse
setavg_grade=电avg
wherecno=@cno
（4）测试上述三个触发器
insert
intosc
values（'20110009',*001',90|
<
S消息
聲号
ife息3509,级别1®状态S第丄行事务在触发器中结束「批处理已中止。
insert
intosc
values/,2O110Q02,,rQ0^1,90select_
fromsc;
口结果血消息
SncCnoGrade
j・L,E■-・F,f"，・]
1j2011DD0100139
2“面了伍亦"D027S
32D11D001D03出
42011D001DMNULL
520110002001NULL
£2011000200260
720110002003NULL
620110002004W
deletefromCoursewherecna=1002*;
<
□rmi±A£l1llAA..--CLDO.UOUTE-t!
JfJLVXAjLa1A>HLEO—4LD
Sno
Cno
Grade
卜
003
39
20110001
004
NULL
20110002
003
AUZ
20110002
004
90
20110003
003
37
20110003
004
75
MJ.
NULL
MULL
Cname
Total_peror
Credit
r-・二幵士丑口住
*jRh
3
*1丁筑^语言程序设计
■j：
sua'JtiaSC
斗
英语
64
斗
数据库
羽
3
NULL
NULL
MJLL
Jpda匸亡3C
sergrade:
S0wherecno=*"■33';
updaresc
setgradegrade^lwkerecno=11j4*;
1行受彫响）
（3行受彫响】
警告：
聚合或其他SZT操作巒了空值.
（1行受彫响）
3行受影■响）
1[_
6。
Cname
Totaljjerior
Credit
avg_grade
鹿
Jbm语言程序…
3
3Q
004
Viiual_Basic
羽
4
90
00S
英语
S4
斗
At£2
006
数据库
43
3
AUZ
NULL
NLIL
NHL
AUZ
MJLL
2、创建INSERTEDOF触发器
（1）创建一视图student_view包含学号、姓名、课程号、课程名、
成绩等属性，在student_view上创建一个触发器grade_modify,当对student_view中的学生的成绩进行修改时，实际修改的是SC中的相应记录。
if（selectnwnefiomsyaobjectswhereuame=rst-uden^_viewrandtype=,vhi
DROPVIEW3cuden.c_vl4w
go
crea匸亡viewstudeni;view
selectstudent.snc,SDamegrade
fromsc,course凸七口dent
wheresc.ano■5no.ardsa^cno:
o;
ifexist3iselectnamefrernsysobjectswhereEajne=,grade_n3difyTandtype=,TRF:
droptriggerflxade_raodify
go
createtriggercrade_mQclify
□natudentview
INSTEADOF_UPDATE
AS
DECLAJ1E@graaeinu
selea^@gradegrade
fromINSERTED
update31udent_view
set?
rade3?
-ader|
（2）在Student表中插入一个getcredit字段（记录学生所获学分的情况），创建一个触发器ins_credit当更改（注：
含插入时）SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课
的学分，如果新成绩小于60分，则该生未能获得这门课的学分。
（3）测试上述两个触发器
update5t«dent_viewsetgrade-2?
:
Sno
Cna
Grade
►
003
3S
2C110001
004
33
20110002
003
sa
20110002
004
as
2C110003
003
as
20110003
004
as
米
NULL
Am
NULL
3、使用T-SQL语句管理和维护触发器
（1）用系统存储过程sp_helptrigger查看触发器grade_modify的相
关信息。
EXECspheLptriggerr匸view1;
s€
[G
1菇果&消息
trigger_nam&
trigger_07kTFerisupdateisdsletsisinserlisafterisinsteadoftngger_3chenna
1
igrade_nnciclify
dbo10001dbo
（2）使用sp_helptext查看触发器grade_modify中的定义内容。
execlptexty
型=ZZZ=ZZ
二结果
J消息
Text
1
:
createtriggergrade_modify:
2
onstjderrt_view
3
INSTEADOFUPDATE
4
AS
5
DECLARE@gradeint
E
select@grade=grade
fromINSERTED
&
updatestudent^view
9
setg^ade=@grade；
（3）使用select语句查看触发器grade_modify的定义内容。
SELECT亠
ETRCHrByscoraraeDts
M^EKEtype-'TR-azi«dnaw"gratiE_iiod1fyp;
Mu1J
□
1占朋
det
t-fiXttflM
language
fincWBd
ccr*ipfssi»d
LeM
A
0
1
0
ft^B{）05B0050™FQ073ffl）RQ053{MKF畑WK5C05D0020…
2
0
a
0
iJPiMia昶]呛lMlQ-9|IMR9|[Q-9!
l{J-9r）
5
D
1
0
血泊瞒XK桥畑0M006300詹畑DO&KOSDOCEI...
2
0
打
0
ilPmsio詔时帕lM]D-9|lQ-?
lD-9||0-9ia-9n
S
fl
i
0
Ch2m5SMM细ElMWiTMKTKieKKffia&KHW如…
2
0
a
0
i[Entrance_daLep|Brthll
7
百
i
D
M3D07200650（胞100”00650021»&140皿5004風）041005開...
2
0
a
0
errfeDEFMJLTGradeasU
D
i
D
[k43®52im5fflM畑540045002000；«106戏便50（]771»200...
2
D
a
匚RE^TEvwnsiLArs^2esseleclcno,cnHmeBcredtE..
打
1
D
Ds43®52£Oi5DG41CO540045002[W56QM3CG150M7KI2CK]...
2
0
0
匚REATEVIEWstuv»«1assdedSno.SnamtSec,.
疋
0
1
□
[RS3M721M™£1[MZfl0KaKL2WD7fflM9aK5OT77DO2M]…
2
0
□
5^tswsfMstuvewl35sd«1SCSnaD».Gradef
11
fl
n
D
EkSXKIT^Oli瞬1Q?
74IX^5Cia2W7«m9^S0（l77^CW
2
Q
fl-
0
cnjt12
1
1
0
2
0
护
Q
CREATEJWK*加stu_pr¥9A*"1*4*亠《!
J1
U
1
1
0
卯QQ650a™QTO0720afM3Q065D
2
Q
Q
CWt«£mK4du^5hJ_p*UCl刍:
；:
；V.
（4）用系统存储过程sp_depends查看触发器grade_modify的相关性（即该触发器涉及哪些基本表）。
USESt^dent_info---指定数据库
EXECspdepends0objname=radencdLfy1;
…——一…—.……
二结果
A消息
name
type
updated
selected
column
1!
dbo.5tudent_view
■niamMiBinrainia■■■>■■■■■■■■■■■«■■■■■>■
lview
yes
no
grade
（5）将sc_insert触发器改为insteadof触发器，实现的功能不变
if-^xs3t3■selectnamefroirsyscbjectswnererane=1sc_±n3ert'andtyp已='工貝.‘droptriggersc_inserr
go
ci&atrigger
onsg
INSTEADOFINSERT
£3
declare@sncvarchar（10F@cnovarchar（ID）
select@snoano,3cnoeno
fremINSERTED
ifnetexis匸巴（sei亡utano
frcTUstudent
zFnere@siiQ=snoi
begin
Print■学号不存在|・}
rollbacktran
end|
（6）将触发器sc_insert删除
DROPTRIGGER3c_in3ert;|
4、使用SQLServerManagementStudio管理触发器
（1）在SQLServerManagementStudio中重新创建刚删除的触发器sc_insert
CREATETRIGGERsc_insert
ONsc
INSTEADOFINSERT
AS
3EGIN
declare®snovarchai（10）f^cnovarchar（10
select&sno=sna.@cno=cno
frcinINSERTED
ifrotexis匸m（selectsno
framstudenr
where@sno=3no
begin
print「学号不存在
rollbacktran
end
END
GO
_J消息
命令已咸功完成.
（2）查看触发器sc_insert的内容
75E[5tudent_info]
GO
11/19/201319:
02;37*****'
/******对跟：
Trigger[dbo]»[sc_ln3ert]脚本日期:
SETAN名工_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
—Author:
——Createdate:
-―Descript1on;
ALTERTRIGGER[dbo}・（5C_inser匸]
OM|[dlDO）.[SC]
INSTEADOFINSERT
AS
BEGIN
declare@3novarchaz10if@cnovarchar10：
|select@5bo=sbq^@eno=gho
fromINSERTED
xfnotexists£sno
fromstudent
where@sno=3noi
begin
print（'学号不存在I■）
rollbacktrail
end
END
（3）删除触发器sc_inser。
要W除的对象（Q）
对線类型
姑有者状态
1…1B
升级会员 