1、实验11 触发器实验十一触发器姓名：学号：专业：班级：同组人：无实验日期：2012-5-1【实验目的与要求】1 熟练掌握变量的定义和赋值。 2 熟练掌握各种运算符。3 熟练掌握流程控制语句，尤其是条件语句和循环语句。4 熟悉游标的工作机制及游标的使用【实验内容与步骤】11.1. 创建DML触发器1. 使用触发器模板创建触发器在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开“表”节点，右击要创建触发器的“表”，选择“新建触发器”命令，如图所示：在右侧查询编辑器中出现触发器设计模板，可以在此基础上编辑触发器，单击“执行”按钮，即可创建该触发器。2. 使用T-SQ
2、L语句创建表级触发器在查询分析器里使用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)DROP TRIGGER T_return_TotalGOCREAT
3、E 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 inserted)SET P_Price =(SELECT 价格
4、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);给出运行结果：实验11-2：对CPXS库中CP表的 DELETE操作定义触发器。当某产库存量为0时将该记录删除。USE CPXSGOIF EX
5、ISTS(SELECT name FROM sysobjects -如果已经存在触发器则删除 WHERE name=CP_Del AND type=TR)DROP TRIGGER CP_DelGOCREATE TRIGGER CP_Del -创建触发器ON CPFOR DELETEASBeginDECLARE Storage intSELECT Storage =库存量FROM deletedIF Storage0 BEGIN PRINT 该产品记录不能删除！还有+convert(char(2),Storage)+件没销完。 ROLLBACK -回滚操作，撒销删除操作 ENDELSE PRIN
6、T 该产品记录已被删除！EndGO测试：USE CPXSGODELETE CP WHERE 产品名称=太阳能热水器请给出执行结果：练习：（1）完成后，再执行Select * from cp;语句，查看产品名称=太阳能热水器的记录是否真的删除。请给出执行结果：（2）执行如下语句，在CP表中插入一行库存量字段值为0的记录。INSERT INTO CP(产品编号,产品名称,价格,库存量) VALUES(100017,小汽车,85788,0);再执行删除语句：DELETE CP WHERE 产品名称=小汽车;请给出执行结果：而后再执行“Select * from cp;”语句，查看该记录是否删除，比较
7、与（1）的差别。11.2. 创建DDL触发器DDL 触发器会为响应多种数据定义语言 (DDL) 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。语法形式：CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ,.n FOR|AFTER event_type|event_group,.nAS sql_statement; .n|EXTERNAL NAME ; 其中：:=ENCRYPTION EXECUTE AS Clause := assembly_n
8、ame.class_name.method_name 实验11-3：使用DDL触发器来防止数据库中的任一表被修改或删除。CREATE TRIGGER safetyON DATABASE FOR DROP_TABLE, ALTER_TABLEASPRINT You must disable Trigger safety to drop or alter tables!ROLLBACK 练习：试着对表进行删除或修改，看是否能修改成功。11.3. 触发器的其他操作1.修改触发器语法要点：ALTER TRIGGER 触发器名2.删除触发器语法要点：DROP TRIGGER 触发器名实验11-4：删除触
9、发器：请用T-SQL命令删除上一实验中创建的触发器safety。请给出相应语句和执行结果：3.查看触发器可使用系统存储过程查看触发器相关信息，根据实验实际，将以下两处中的trigger_name和table_name换成具体的触发器名字和表名，放于查询分析器中执行，给出执行结果截图：（1）查看触发器的具体内容：sp_helptext trigger_name实验11-5：查看触发器的定义：请给出执行结果：（2）查看表中的触发器：sp_helptrigger table_name实验11-6：查看定义在表中的触发器：请给出执行结果：11.4. 触发器练习1.练习：编写一个触发器t_CP_bak，
10、用以备份CP表的历史数据，若用户对表CP进行删除操作，则将被删除的数据转移到表CP_del，若用户对表CP进行更新操作，则将更新前的原始数据行转移到表CP_update中。(注：表CP_del和CP_update请先创建它，结构与CP相同，可用Select Into语句基于CP表创建。)请给程序源码：create trigger t_CP_bakon cpafter delete,updateasif exists(select 1 from inserted) and exists(select 1 from deleted)begininsert into cp_update select
11、 * from deletedendif not exists(select 1 from inserted)begininsert into cp_del select * from deletedend（1）试着在CP表中删除几行数据，而后查询CP_del中数据。给出查询结果：（2）试着在CP表中更新几行数据，而后查询CP_update中数据。给出查询结果：2练习：创建一触发器，当向CPXSB表插入一记录时，检查该记录的产品编号在CP表中是否存在，检查该记录的客户编号在XSS表中是否存在，若有一项为否，则不允许插入。请给出源码和测试结果：（1）源代码create trigger t_cpx
12、sb_inserton cpxsbfor insertas begindeclare cpbh char(6), xsbh char(6)SET cpbh =(SELECT 产品编号 FROM inserted) SET xsbh =(SELECT 客户编号 FROM inserted)if(cpbh not in (SELECT 产品编号 FROM cp) begin print插入数据中产品编号与cp表中产品编号不一致 rollback transaction endelse print插入一行数据成功if(xsbh not in(SELECT 客户编号 FROM xss)begin pr
13、int插入数据中客户编号与xss表中产品编号不一致 rollback transaction endelse print插入一行数据成功end（2）测试：分别插入满足条件和不满足条件的记录，看数据是否能正常插入。3. 练习： 在CPXSB上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息“产品编号列和客户编号列不能修改”，并取消修改操作(使用回滚语句，见前面实验)。请给出源码和测试结果：（1）源代码create trigger trig_updateon cpxsbfor insertas begin if update(产品编号) begin print产品编号列和客户编号列不能修改 rollback transaction endif update(客户编号) begin print产品编号列和客户编号列不能修改 rollback transaction endend（2）测试：试着对CPXSB中产品编号列和客户编号数据进行修改，看是否能修成功。
