数据库原理实验报告S11数据库触发器的创建.docx
- 文档编号:5185063
- 上传时间:2022-12-13
- 格式:DOCX
- 页数:17
- 大小:141.32KB
数据库原理实验报告S11数据库触发器的创建.docx
《数据库原理实验报告S11数据库触发器的创建.docx》由会员分享,可在线阅读,更多相关《数据库原理实验报告S11数据库触发器的创建.docx(17页珍藏版)》请在冰豆网上搜索。
数据库原理实验报告S11数据库触发器的创建
实验11数据库触发器的建立
实验日期和时间:
实验室:
软件工程室
班级:
12计科3
学号:
姓名:
张翔
实验环境:
1.硬件:
内存处理器
2.软件:
Windows7旗舰版MicrosoftSQLServer2005
实验原理:
1.理解触发器的概念、和一般存储过程的区别、触发器的优点和使用原则。
2.掌握触发器的创建、执行、查看、修改和删除方法。
一、触发器的概念:
触发器是一种特殊类型的存储过程。
一般的存储过程通过存储过程名称被直接调用,而触发器主要是通过当某个事件发生时自动被触发执行的。
触发器可以用于SQLServer约束、默认值和规则的完整性检查,还可以完成难以用普通约束实现的复杂功能。
当创建数据库对象或在数据表中插入记录、修改记录或者删除记录时,SQLServer就会自动执行触发器所定义的SQL语句,从而确保对数据的处理必须符合由这些SQL语句所定义的规则。
触发器和引起触发器执行的SQL语句被当作一次事务处理,如果这次事务未获得成功,SQLServer会自动返回该事务执行前的状态。
使用触发器有以下优点:
触发器是自动的执行的,。
触发器可以通过数据库中的相关表进行层叠更改,如可以在“学生表”中的学号列上创建一个删除触发器,当删除“学生表”的记录时,自动级联删除“成绩表”中的个匹配行。
触发器可以强制限制。
这些限制比用CHECK约束所定义的更复杂,与CHECK约束不同的是,触发器可以引用其他表中的列。
触发器可以分为DML触发器和DDL触发器两种
DDL触发器用于响应各种数据定义语言(DDL)事件。
这些事件主要对应于Transact-SQL中的CREATE、ALTER和DROP语句,以及执行类似DDL操作的某些系统存储过程。
它们用于执行管理任务,并强制影响数据库的业务规则。
DML触发器是在用户使用数据操作语言(DML)事件编辑数据时发生。
DML事件是针对表或视图的INSERT、UPDATE或DELETE语句。
DML触发器有助于在表或视图中修改数据时强制业务规则,扩展数据完整性。
DML触发器又分为AFTER触发器和INSTEADOF触发器两种:
AFTER触发器:
这种类型的触发器将在数据变动(INSERT、UPDATE和DELETE操作)完成以后才被触发。
可以对变动的数据进行检查,如果发现错误,将拒绝接受或回滚变动的数据。
AFTER触发器只能在表上定义。
在同一个数据表中可以创建多个AFTER触发器。
INSTEADOF触发器:
INSTEADOF触发器将在数据变动以前被触发,并取代变动数据的操作,而去执行触发器定义的操作。
INSTEADOF触发器可以在表或视图上定义。
每个INSERT、UPDATE和DELETE语句最多定义一个INSTEADOF触发器。
二、创建触发器
1.使用SQLServer管理控制台创建DML触发器
在SQLServer管理控制台中,展开指定的服务器和数据库,单击要创建触发器的数据表,右击其中的“触发器”文件夹。
从弹出的快捷菜单中选择“新建触发器”选项,会出现新建触发器模板窗口,在新建的触发器模板窗口中单击“查询”菜单中“指定模板参数的值”选项,会弹出“指定模板参数的值”对话框,可以在这里设置参数的具体值。
2.使用Transact-SQL语句创建DML触发器
使用Transact-SQL语言中的CREATETRIGGER命令也可以创建DML触发器:
CREATETRIGGER[schema_name.]trigger_nameON{table|view}
[WITH
AS{sql_statement[;][,...n]}
:
=[ENCRYPTION][EXECUTEASClause]
其中,各参数的说明如下:
schema_name:
DML触发器所属架构的名称。
trigger_name:
触发器的名称。
table|view:
对其执行DML触发器的表或视图。
WITHENCRYPTION:
对CREATETRIGGER语句的文本进行加密处理。
EXECUTEAS:
指定用于执行该触发器的安全上下文。
FOR|AFTER:
AFTER指定触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发。
不能对视图定义AFTER触发器。
INSTEADOF:
指定执行DML触发器而不是触发SQL语句。
{[DELETE][,][INSERT][,][UPDATE]}:
指定数据修改语句。
必须至少指定一个选项。
sql_statement:
触发条件和操作。
当创建一个触发器时必须指定以下几项内容:
触发器的名称;
在其上定义触发器的表;
触发器将何时激发;
执行触发操作的编程语句。
注意DML触发器时生成的两个幻表:
DML触发器可以使用两个特殊的临时表,它们分别是inserted表和deleted表。
这两个表都存在于内存中。
它们在结构上类似于定义了触发器的表。
在inserted表中存储着被INSERT和UPDATE语句影响的新的数据行。
在执行INSERT或UPDATE语句时,新的数据行被添加到基本表中,同时这些数据行的备份被复制到inserted临时表中。
在deleted表中存储着被DELETE和UPDATE语句影响的旧数据行。
在执行DELETE或UPDATE语句时,指定的数据行从基本表中删除,然后被转移到了deleted表中。
在基本表和deleted表中一般不会存在相同的数据行。
一个UPDATE操作实际上是由一个DELETE操作和一个INSERT操作组成的。
在执行UPDATE操作时,旧的数据行从基本表中转移到deleted表中,然后将新的数据行同时插入基本表和inserted表中。
实验主要任务:
导入学生成绩数据库,并建立相关表的关系,对数据库实施如下操作:
一、利用SQLServer管理控制台创建DML触发器中的INSERT触发器的后(AFTER)触发器
1.利用SQL-SERVER管理控制台:
创建一个INSERT触发器“TR_Course_Insert”,当在课程表中插入一条新记录时,触发该触发器,并给出“你插入了一条新的课程记录!
”的提示信息。
2.创建了TR_Course_Insert触发器后,查看向课程表中插入数据时,此触发器所完成的功能。
二、使用Transact-SQL语句创建DML的后(FOR、AFTER)触发器
(一)实现检查约束
3.创建一个AFTER触发器,要求实现以下功能:
在成绩表上创建一个插入、更新类型的触发器TR_ScoreCheck,当在成绩字段中插入或修改考试分数后,触发该触发器,检查分数是否在0-100之间。
createtriggerTR_ScoreCheck
on成绩
forinsert,update
as
if(select成绩frominserted)between0and100
begin
print'你成功的对成绩表插入(更新)了一条记录'
end
else
begin
print'(插入)更新的成绩数据错误'
rollbacktransaction
end
在创建了TR_ScoreCheck触发器之后,在查询窗口中输入以下SQL语句验证:
(独立完成:
4.创建一个AFTER触发器,要求实现以下功能:
在2003学生名单表上创建一个插入、更新类型的触发器TR_Sex_Check,当在性别字段中插入或修改性别后,触发该触发器,检查性别是否是‘男’或‘女’,并验证)
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
ALTERtrigger[TR_Sex_Check]
on[dbo].[学生名单]
forinsert,update
as
if(select性别frominserted)in('男','女')
begin
print'插入(更新)数据'
end
else
begin
print'插入或更新的性别错误'
rollbacktransaction
end
(二)实现层叠修改
5.创建一个AFTER触发器,要求如下:
在学生表上创建一个删除类型的触发器TR_Stu_Delete,当在学生表中删除某一条记录后,触发该触发器,在成绩表中删除与此学号对应的记录。
createtriggerTR_Stu_Delete
on学生
fordelete
as
begin
delete
from成绩
where学号=(select学号fromdeleted)
end
在创建了TR_Stu_Delete触发器之后,在查询窗口中输入以下SQL语句验证:
(独立完成:
6.创建一个AFTER触发器,要求实现以下功能在2003班级表上创建一个删除类型的触发器TR_Class_Delete,当在2003班级表中删除某一条记录后,触发该触发器,在2003学生名单中删除与此学号对应的记录。
并验证)
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
ALTERtrigger[TR_Class_Delete]
on[dbo].[班级]
fordelete
as
begin
delete
from学生名单
where班级=(select班级fromdeleted)
end
三、使用Transact-SQL语句创建DML的前(INSTEADOF)触发器
7.创建一个INSTEADOF触发器,要求实现以下功能:
在课程表上创建一个删除类型的触发器TR_NotAllowDelete,当在课程表中删除记录时,触发该触发器,显示不允许删除表中数据的提示信息。
在创建了TR_Stu_Delete触发器之后,在查询窗口中输入以下SQL语句验证:
四、查看和修改触发器
(一)利用SQLServer管理控制平台查看
8.使用SQLServer管理控制台查看TR_Course_Insert触发器并查看与该触发器有依赖关系的其他数据库对象
(二)使用系统存储过程查看触发器
可以使用系统存储过程sp_help、sp_helptext和sp_depends分别查看触发器的不同信息。
它们的具体用途和语法形式如下:
sp_help:
用于查看触发器的一般信息,如触发器的名称、属性、类型和创建时间。
sp_help‘触发器名称’
sp_helptext:
用于查看触发器的正文信息。
sp_depends:
用于查看触发器涉及到的所有表。
9.使用系统存储过程查看TR_Course_Insert触发器的一般信息
10.使用系统存储过程查看TR_Stu_Delete触发器涉及到的所有表。
11.使用系统存储过程查看TR_Stu_Delete触发器的正文信息
五、删除触发器
12.用系统命令DROPTRIGGER触发器名删除TR_Course_Inser
利用商品销售练习数据库阅读下面的插入存储器、触发器实例,并把代码敲入自己的界面上熟悉:
数据库【商品销售练习】关系图
用visio2010画的关系图
1.)创建一个存储过程,它接收一个商品编号,显示该商品的的所有销售记录及销售总数量、金额总计值、现有库存等相关信息。
--打开数据库商品销售练习
USE商品销售练习
GO
--先查询商品编号为'005'学生信息
select*from商品销售where商品编号='005'
--执行刚刚创建的存储过程Pro_各科成绩单
EXECPro_商品编号'005'
2.)创建一个触发器,能够在"商品销售"表中插入一条记录时(只输入"商品编号"和"商品数量")通过触发器的作用,能自动填写该记录"商品编号"对应的"商品名称"、"商品规格"、"单价"、"销售日期",能自动计算"金额"(=单价*数量)填入。
并实现库存不足报警。
在"商品销售"表中插入记录时,能根据"商品库存"表中的对应商品记录的"库存数量"进行检查,凡销售"数量"已超出库存数量的商品记录不能插入,并显示现有库存量并提示“库存不足”/“库存空”,凡是库存量低于最低库存量的也提示"现有库存量低于最低库量"。
创建触发器代码如下:
插入代码:
--插入一条记录
USE商品销售练习
GO
select*from商品库存where商品编号='005'
GO
select*from商品销售
GO
INSERTINTO商品销售(商品编号,数量)VALUES('005',18)
GO
select*from商品库存where商品编号='005'
GO
select*from商品销售
GO
将以上任务的实验完成情况、实验结果、实验原理、总结分栏一一填写到下表中,格式参考任务1或者自定。
回答问题:
1.触发器与一般的存储过程的主要区别是什么
触发器不同于存储过程,触发器主要是通过事件执行触发而被执行的,而存储过程可以通过存储过程名称名字而直接调用。
当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时,SQLSERVER就会自动执行触发器所定义的SQL语句,从而确保对数据的处理必须符合这些SQL语句所定义的规则
2.简述触发器的分类及各自的特点。
DML触发器,DDL触发器
3.使用触发器有哪些优点
自动执行级联更新强化约束跟踪变化强制业务逻辑
小结:
(不少于100字)
你在完成查询任务的过程中遇到的问题及解决的方法有:
要求:
1.报告格式和内容要求:
2.a.内容和格式整齐。
大标题采用黑体四号字加粗,小标题采用小四号字加粗。
正文采用五号宋体,单倍行距。
3.b.贴图时请剪裁到适当大小,要保证打印时可以看清,但也不要太大以免“越界”。
4.c.不要在报告中写与实验无关的话,内容要有条理、完整、并能突出重点,要将遇到的主要问题说明。
5.文件格式要求:
6.a.将实验成果放入一个文件夹中,文件夹的内容包括:
本实验报告、分离后的数据库、如有查询代码(.sql文件)和其它文件也一并放入。
7.b.文件夹以“学号姓名班级_S11”为文件名。
注意:
你的学号放在姓名前。
8.c.注意:
所有文件保存后关闭,然后打包成RAR文件,以免提交的内容丢失或打不开。
9.提交方式和时间:
一周内完成。
可以将文件包发到我。
10.主动查阅资料,坚持自己亲手完成实验,弄清每个步骤和相关原理。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 实验 报告 S11 触发器 创建
![提示](https://static.bdocx.com/images/bang_tan.gif)