SQLServer触发器练习题Word文件下载.docx
《SQLServer触发器练习题Word文件下载.docx》由会员分享,可在线阅读,更多相关《SQLServer触发器练习题Word文件下载.docx(9页珍藏版)》请在冰豆网上搜索。
RAISERROR('
Youcannotinsertmorethanonestudentatatime.'
16,1)
ROLLBACKTRAN
return
--注意此处的return语句不能省略,因为在触发器脚本中的ROLLBACKTRAN语句之后还存在语句,那么将会执行这些语句,而为了其后的语句不执行,必须加入return语句
end
updateclass
setclass_num=class_num+1
whereclass_id=(selectclass_idfrominserted)print'
class表中数据更新成功'
go
--测试1
insertintostudent
select'
0601012'
'
丽'
女'
1986-07-11'
'
0601'
unionallselect'
0601013'
梅'
1988-02-07'
--测试2
insertintostudentvalues('
0602011'
文'
1986-09-21'
0602'
)--2)修改题1中创建的INSERT触发器stu_insert,要求在student表中插入记录时(允许插入多条记录),这个触发器都将更新class表中的class_nun列。
altertriggerstu_insert
setclass_num=class_num+(selectcount(class_id)frominsertedwhere
class.class_id=inserted.class_id)print'
7.27.2.1materialconstructiontechnicalmeasuresofinspectionandmanagementbuilttankselectionofmaterialsandaccessories,shouldhavethequalitycertificateorcertificateofqualityre-inspection.Constructionselectionoftanksteelplate,mustbecarriedoutbyVisualinspection,noseriousdamageanddistortion.Surfacecorrosionofsteelplatethicknessreductionamount,actualnegativedeviationofsteelplateandscratchingdepthandshouldcomplywithplatethicknesstolerancerequirements.Withoutstratification,porosity,surfacescarring,RIP,crease,inclusionandpressintheoxide.Electrodemusthaveaqualitycertificate,whichincludechemicalcompositionandmechanicalpropertiesofdepositedmetaltolerance.Electrodestorageandstackingtoexpertmanagement,establishmentoftheelectrode,electrodediameterelectrodeandmodelsshouldbeneatlystackedseparately.Establishastrictelectrodesystem,recordfillelectrode,electrodeshouldbeusedstrictlyforbakingandissuedasrequired.Allplatesshouldbeclassifiedbymaterialqualityandspecificationsofpilingup.Topreventthestringmaterialshouldbeestablishedaccordingtothepurposeanduseofbooks.Allpartsshouldbestrictlyaccordingtothedrawingcheckacceptance,andproductcertificates.7.2.2basisofacceptancebeforetheconstructionofstoragetankshouldbedesignedinaccordancewiththecorrespondingbasicdocumentationrequirementsandAPI620therelevantrequirementsforacceptance,andcheckthebasicconstructionunittoprovidechecksofrecords.FoundationCenterheighttoleranceis?
20mm.Basesurfaceshouldbeflatanddense,andthroughcracksintheprominentbulge,SAG-free.Tanktankbolt,theinnertankofanchorbolt,beltshouldbeinwaterbasedembeddedfixed.7.2.3operationscaninprefabricatedAssemblyandtestingprocessusedintheproductareasfollows:
thearcs
)--3)在student上创建DELETE触发器stu_delete,要求在student表中删除记录时,这个触发器都将更新class表中的class_nun列。
并测试触发器stu_delete。
createtriggerstu_delete
onstudentfordelete
setclass_num=class_num-(selectcount(class_id)fromdeletedwhere
class.class_id=deleted.class_id)go
deletefromstudentwherestu_id='
0601001'
--测试2
deletefromstudent
--4)为防止其他人修改成绩,在score上创建UPDATE触发器sc_update,要求不能更新score表中的score列。
测试触发器sc_update。
createtriggersc_update
onscoreforupdate
ifupdate(score)
print'
不允许修改score列'
rollbacktran
--尝试修改score列
updatescore
setscore=99
2、查看触发器相关信息:
使用系统存储过程sp_help,sp_helptext,sp_helptrigger查看触发器相关信息。
execsp_help
execsp_helpsc_update
execsp_helptextsc_update
execsp_helptriggerstudent
execsp_helptriggerstudent,'
insert'
--附录:
--创建数据库,准备数据
createdatabasestudent_score
GO
--在数据库中创建三个表的结构
usestudent_score
createtablestudent
(stu_idchar(8)primarykey,
stu_namechar(10),
stu_sexchar
(2),
stu_birthdaysmalldatetime,
class_idchar(6)
)
createtableclass
(class_idchar(6)primarykey,
class_namevarchar(30),
class_numint,
createtablecourse
(course_idchar(3)primarykey,
course_namevarchar(30),
createtablescore
(stu_idchar(8),
course_idchar(3),
scoreintcheck(score>
=0andscore<
=100)
primarykey(stu_id,course_id)
--往表中插入数据(student,course,score)
李玉'
1987-05-06'
0601002'
鲁敏'
1988-06-28'
0601003'
李小路'
1987-01-08'
0601004'
鲁斌'
男'
1988-04-21'
0601005'
王宁静'
1986-05-29'
0601006'
张明明'
1987-02-24'
0601007'
刘晓玲'
1988-12-21'
0601008'
周晓'
1986-04-27'
0601009'
易国梁'
1985-11-26'
0601010'
季风'
insertintoclassvalues('
0501'
计算机办公应用'
40)
0502'
网络构建'
43)
0503'
图形图像'
48)
可视化'
41)
数据库'
38)
0603'
网络管理'
45)
0604'
多媒体'
0701'
39)
0702'
WEB应用'
0703'
insertintocoursevalues('
001'
计算机应用基础'
002'
关系数据基础'
insertintocourse