1、第 1 页 共 13 页实验十一 触发器姓名： 计算机科学与技术 学号：专业： 班级：同组人： 无 实验日期：【实验目的与要求】1 熟练掌握变量的定义和赋值。 2 熟练掌握各种运算符。3 熟练掌握流程控制语句，尤其是条件语句和循环语句。4 熟悉游标的工作机制及游标的使用【实验内容与步骤】11.1. 创建 DML 触发器1. 使用触发器模板创建触发器在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开“表”节点，右击要创建触发器的“表” ，选择“新建触发器”命令，如图所示：在右侧查询编辑器中出现触发器设计模板，可以在此基础上编辑触发器，单击“执行”按钮，即可创建该
2、触发器。第 2 页 共 13 页2. 使用 T-SQL 语句创建表级触发器在查询分析器里使用 T-SQL 可直接创建存储过程语法：CREATE TRIGGER 触发器ON 表名FORupdate,insert,delete ASSQL 语句实验 11-1：编写一触发器，在向产品表 CP 中添加记录时，得到该类产品的总价格（总价格价格*库存量）,并输出。说明：可使用 Print 变量名 来输出变量值。USE CPXSIF EXISTS (SELECT name FROM sysobjects -如果已经存在触发器则删除WHERE name =T_return_Total AND type=TR)
3、DROP TRIGGER T_return_TotalGOCREATE TRIGGER T_return_Total -创建触发器ON CP -基于表borrowAfter INSERT -监视插入操作，针对其触发ASBegin-查询插入记录INSERTED中读者的类型DECLARE P_ID char(6),P_Name char(30),P_Price float, P_Storage int, P_total_price intSET P_ID =(SELECT 产品编号 FROM inserted) - inserted为临时表SET P_Name =(SELECT 产品名称 FROM
4、 inserted)SET P_Price =(SELECT 价格 FROM inserted)SET P_Storage=(SELECT 库存量 FROM inserted)SET P_total_price=P_Price*P_StoragePRINT 产品为: + P_Name+总价格为: + convert(varchar(20),P_total_price)End应用测试：USE CPXSINSERT INTO CP(产品编号,产品名称,价格,库存量) VALUES(100016,东风小汽车 ,85728,9);给出运行结果：第 3 页 共 13 页实验 11-2：对 CPXS 库中
5、 CP 表的 DELETE 操作定义触发器。当某产库存量为 0 时将该记录删除。USE CPXSGOIF EXISTS(SELECT name FROM sysobjects -如果已经存在触发器则删除WHERE name=CP_Del AND type=TR)DROP TRIGGER CP_Del第 4 页 共 13 页GOCREATE TRIGGER CP_Del -创建触发器ON CPFOR DELETEASBeginDECLARE Storage intSELECT Storage =库存量FROM deletedIF Storage0BEGINPRINT 该产品记录不能删除！还有 +
6、convert(char(2),Storage)+件没销完。ROLLBACK -回滚操作，撒销删除操作ENDELSEPRINT 该产品记录已被删除！EndGO测试：USE CPXSGODELETE CP WHERE 产品名称=太阳能热水器请给出执行结果：第 5 页 共 13 页练习：（1）完成后，再执行 Select * from cp;语句，查看产品名称=太阳能热水器的记录是否真的删除。请给出执行结果：（2）执行如下语句，在 CP 表中插入一行库存量字段值为 0 的记录。INSERT INTO CP(产品编号,产品名称,价格,库存量) VALUES(100017,小汽车,85788,0);再
7、执行删除语句：DELETE CP WHERE 产品名称=小汽车;第 6 页 共 13 页请给出执行结果：而后再执行“Select * from cp;”语句，查看该记录是否删除，比较与（1）的差别。第 7 页 共 13 页11.2. 创建 DDL 触发器DDL 触发器会为响应多种数据定义语言 (DDL) 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。语法形式：CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ,.n FOR|AFTER even
8、t_type|event_group,.nAS sql_statement; .n|EXTERNAL NAME ; 其中：:=ENCRYPTION EXECUTE AS Clause:= assembly_name.class_name.method_name 实验 11-3：使用 DDL 触发器来防止数据库中的任一表被修改或删除。CREATE TRIGGER safetyON DATABASE CPXSFOR DROP_TABLE, ALTER_TABLEASPRINT You must disable Trigger “safety“ to drop or alter tables!ROL
9、LBACK 练习：试着对表进行删除或修改，看是否能修改成功。第 8 页 共 13 页11.3. 触发器的其他操作1.修改触发器语法要点：ALTER TRIGGER 触发器名2.删除触发器语法要点：DROP TRIGGER 触发器名实验 11-4：删除触发器：请用 T-SQL 命令删除上一实验中创建的触发器 safety。请给出相应语句和执行结果：DROP TRIGGER safety ON DataBase3.查看触发器可使用系统存储过程查看触发器相关信息，根据实验实际，将以下两处中的第 9 页 共 13 页trigger_name 和 table_name 换成具体的触发器名字和表名，放于查
10、询分析器中执行，给出执行结果截图：（1）查看触发器的具体内容：sp_helptext trigger_name实验 11-5：查看触发器的定义：请给出执行结果：（2）查看表中的触发器：sp_helptrigger table_name实验 11-6：查看定义在表中的触发器：请给出执行结果：第 10 页 共 13 页11.4. 触发器练习1. 练习：编写一个触发器 t_CP_bak，用以备份 CP 表的历史数据，若用户对表 CP 进行删除操作，则将被删除的数据转移到表 CP_del，若用户对表 CP 进行更新操作，则将更新前的原始数据行转移到表 CP_update 中。( 注：表 CP_del
11、和 CP_update 请先创建它，结构与 CP 相同，可用 Select Into语句基于 CP 表创建。)请给程序源码：CREAT TRIGGER CP_Dell ON CPFOR DELETEASBegininsert into CP_Del select * from deletedPRINT 删除的产品已经备份 EndCREAT TRIGGER CP_updateeON CPFOR UPDATEASBegininsert into CP_update select * from deletedPRINT 删除的产品已经备份 End（1）试着在 CP 表中删除几行数据，而后查询 CP_
12、del 中数据。给出查询结果：第 11 页 共 13 页（2）试着在 CP 表中更新几行数据，而后查询 CP_update 中数据。给出查询结果：2练习：创建一触发器，当向 CPXSB 表插入一记录时，检查该记录的产品编号在 CP表中是否存在，检查该记录的客户编号在 XSS 表中是否存在，若有一项为否，则不允许插入。请给出源码和测试结果：（1）源代码create trigger cpxsb_insertON CPXSBafter INSERTAS BEGINdeclare A char(8)declare B char(8)set A=(select inserted.产品编号 from in
13、serted)set B=(select inserted.客户编号 from inserted)IF (A not in (select 产品编号 from cp)or (B not in(select 客户编号 from xss)BEGINPRINT 插入的数据不在 cp 表中ROLLBACKENDEND（2）测试：分别插入满足条件和不满足条件的记录，看数据是否能正常插入。插入错误数据，第 12 页 共 13 页插入正确数据成功3. 练习： 在 CPXSB 上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息“产品编号列和客户编号列不能修改” ，并取消修改操作(使用回滚语句，见前面实验)。请给出源码和测试结果：（1）源代码create trigger cpxsb_updateON CPXSBafter updateAS BEGINif update(产品编号)beginprint产品编号不能更改rollbackendif update(客户编号)beginprint客户编号不能更改rollbackendEND第 13 页 共 13 页（2）测试：试着对 CPXSB 中产品编号列和客户编号数据进行修改，看是否能修成功。
