实验11 触发器.docx
《实验11 触发器.docx》由会员分享，可在线阅读，更多相关《实验11 触发器.docx（14页珍藏版）》请在冰点文库上搜索。
实验11触发器
实验十一 触发器
姓名：
学号：
专业：
班级：
同组人：
无
实验日期：
2012-5-1
【实验目的与要求】
1．熟练掌握变量的定义和赋值。
2．熟练掌握各种运算符。
3．熟练掌握流程控制语句，尤其是条件语句和循环语句。
4．熟悉游标的工作机制及游标的使用
【实验内容与步骤】
11.1.创建DML触发器
1.使用触发器模板创建触发器
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开“表”节点，右击要创建触发器的“表”，选择“新建触发器”命令，如图所示：
在右侧查询编辑器中出现触发器设计模板，可以在此基础上编辑触发器，单击“执行”按钮，即可创建该触发器。
2.使用T-SQL语句创建表级触发器
在查询分析器里使用T-SQL可直接创建存储过程
语法：
CREATETRIGGER触发器
ON表名
FOR[update,insert,delete]
AS
SQL语句
实验11-1：
编写一触发器，在向产品表CP中添加记录时，得到该类产品的总价格（总价格＝价格*库存量）,并输出。
说明：
可使用Print@变量名来输出变量值。
USECPXS
IFEXISTS（SELECTnameFROMsysobjects--如果已经存在触发器则删除
WHEREname='T_return_Total'ANDtype='TR'）
DROPTRIGGERT_return_Total
GO
CREATETRIGGERT_return_Total--创建触发器
ONCP--基于表borrow
AfterINSERT--监视插入操作，针对其触发
AS
Begin
--查询插入记录INSERTED中读者的类型
DECLARE@P_IDchar（6）,
@P_Namechar（30）,
@P_Pricefloat,
@P_Storageint,
@P_total_priceint
SET@P_ID=（SELECT产品编号FROMinserted）--inserted为临时表
SET@P_Name=（SELECT产品名称FROMinserted）
SET@P_Price=（SELECT价格FROMinserted）
SET@P_Storage=（SELECT库存量FROMinserted）
SET@P_total_price=@P_Price*@P_Storage
PRINT'产品为:
'+@P_Name+’总价格为:
’+convert（varchar（20）,@P_total_price）
End
应用测试：
USECPXS
INSERTINTOCP（产品编号,产品名称,价格,库存量）
VALUES（'100016','东风小汽车',85728,9）;
给出运行结果：
实验11-2：
对CPXS库中CP表的DELETE操作定义触发器。
当某产库存量为0时将该记录删除。
USECPXS
GO
IFEXISTS（SELECTnameFROMsysobjects--如果已经存在触发器则删除
WHEREname='CP_Del'ANDtype='TR'）
DROPTRIGGERCP_Del
GO
CREATETRIGGERCP_Del--创建触发器
ONCP
FORDELETE
AS
Begin
DECLARE@Storageint
SELECT@Storage=库存量
FROMdeleted
IF@Storage>0
BEGIN
PRINT'该产品记录不能删除！
还有'+convert（char
（2）,@Storage）+'件没销完。
'
ROLLBACK--回滚操作，撒销删除操作
END
ELSE
PRINT'该产品记录已被删除！
'
End
GO
测试：
USECPXS
GO
DELETECPWHERE产品名称='太阳能热水器'
请给出执行结果：
练习：
（1）完成后，再执行Select*fromcp;语句，查看产品名称='太阳能热水器'的记录是否真的删除。
请给出执行结果：
（2）执行如下语句，在CP表中插入一行库存量字段值为0的记录。
INSERTINTOCP（产品编号,产品名称,价格,库存量）
VALUES（'100017','小汽车',85788,0）;
再执行删除语句：
DELETECPWHERE产品名称='小汽车';
请给出执行结果：
而后再执行“Select*fromcp;”语句，查看该记录是否删除，比较与
（1）的差别。
11.2.创建DDL触发器
DDL触发器会为响应多种数据定义语言（DDL）语句而激发。
这些语句主要是以CREATE、ALTER和DROP开头的语句。
DDL触发器可用于管理任务，例如审核和控制数据库操作。
语法形式：
CREATETRIGGERtrigger_name
ON{ALLSERVER|DATABASE}[WITH[,...n]]
{FOR|AFTER}{event_type|event_group}[,...n]
AS{sql_statement[;][...n]|EXTERNALNAME[;]}
其中：
:
:
=[ENCRYPTION]EXECUTEASClause]
:
:
=assembly_name.class_name.method_name
实验11-3：
使用DDL触发器来防止数据库中的任一表被修改或删除。
CREATETRIGGERsafety
ONDATABASE
FORDROP_TABLE,ALTER_TABLE
AS
PRINT'YoumustdisableTrigger"safety"todroporaltertables!
'
ROLLBACK
练习：
试着对表进行删除或修改，看是否能修改成功。
11.3.触发器的其他操作
1.修改触发器
语法要点：
ALTERTRIGGER触发器名
2.删除触发器
语法要点：
DROPTRIGGER触发器名
实验11-4：
删除触发器：
请用T-SQL命令删除上一实验中创建的触发器safety。
请给出相应语句和执行结果：
3.查看触发器
可使用系统存储过程查看触发器相关信息，根据实验实际，将以下两处中的trigger_name和table_name换成具体的触发器名字和表名，放于查询分析器中执行，给出执行结果截图：
（1）查看触发器的具体内容：
sp_helptexttrigger_name
实验11-5：
查看触发器的定义：
请给出执行结果：
（2）查看表中的触发器：
sp_helptriggertable_name
实验11-6：
查看定义在表中的触发器：
请给出执行结果：
11.4.触发器练习
1. 练习：
编写一个触发器t_CP_bak，用以备份CP表的历史数据，若用户对表CP进行删除操作，则将被删除的数据转移到表CP_del，若用户对表CP进行更新操作，则将更新前的原始数据行转移到表CP_update中。
（注：
表CP_del和CP_update请先创建它，结构与CP相同，可用Select…Into…语句基于CP表创建。
）
请给程序源码：
createtriggert_CP_bak
oncp
afterdelete,update
as
ifexists（select1frominserted）andexists（select1fromdeleted）
begin
insertintocp_updateselect*fromdeleted
end
ifnotexists（select1frominserted）
begin
insertintocp_delselect*fromdeleted
end
（1）试着在CP表中删除几行数据，而后查询CP_del中数据。
给出查询结果：
（2）试着在CP表中更新几行数据，而后查询CP_update中数据。
给出查询结果：
2．练习：
创建一触发器，当向CPXSB表插入一记录时，检查该记录的产品编号在CP表中是否存在，检查该记录的客户编号在XSS表中是否存在，若有一项为否，则不允许插入。
请给出源码和测试结果：
（1）源代码
createtriggert_cpxsb_insert
oncpxsb
forinsert
as
begin
declare@cpbhchar（6）,
@xsbhchar（6）
SET@cpbh=（SELECT产品编号FROMinserted）
SET@xsbh=（SELECT客户编号FROMinserted）
if（@cpbhnotin（SELECT产品编号FROMcp））
begin
print'插入数据中产品编号与cp表中产品编号不一致'
rollbacktransaction
end
else
print'插入一行数据成功'
if（@xsbhnotin（SELECT客户编号FROMxss））
begin
print'插入数据中客户编号与xss表中产品编号不一致'
rollbacktransaction
end
else
print'插入一行数据成功'
end
（2）测试：
分别插入满足条件和不满足条件的记录，看数据是否能正常插入。
3.练习：
在CPXSB上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息“产品编号列和客户编号列不能修改”，并取消修改操作（使用回滚语句，见前面实验）。
请给出源码和测试结果：
（1）源代码
createtriggertrig_update
oncpxsb
forinsert
as
begin
ifupdate（产品编号）
begin
print'产品编号列和客户编号列不能修改'
rollbacktransaction
end
ifupdate（客户编号）
begin
print'产品编号列和客户编号列不能修改'
rollbacktransaction
end
end
（2）测试：
试着对CPXSB中产品编号列和客户编号数据进行修改，看是否能修成功。
升级会员 