实验8 数据完整性实验1.实验目的本实验的目的是通过实验使学生加深对数据完整性的理解，学会创建和使用触发器。
2.实验时数4学时[相关知识]SQL Server实现数据完整性的具体方法有4种：约束、缺省、规则和触发器。
其中约束和缺省在实验一中已接触过。
本实验重点学会创建和使用触发器。
触发器是实施复杂完整性的特殊类型的存储类型。
触发器不需要专门语句调用，当对它所保护数据进行修改时自动激活，以防止对数据进行不正确，未授权或不一致的修改。
创建触发器的语法为：CREATE TRIGGER < 触发器> ON <表名>[WITH ENCRYPTION]FOR {[DELETE][，][INSERT][，][UPDATE]}[WITH APPEND][NOT FOR REPLICATION]AS <SQL 语句组>其中：1）WITH ENCRYPTION 为加密选项。
2）DELETE 选项为创建DELETE 触发器。
DELETE触发器的作用是当对表执行DELETE操作时触发器被激活，并从指定表中删除元组，同时将删除的元组放入一个特殊的逻辑表（delete表）中。
触发器的动作可以检查delete表中的数据，以确定下一步该如何处理。
3）INSERT选项为创建INSERT触发器。
INSERT触发器在对指定表中执行插入数据操作时激活，激活后将插入表中的数据拷贝并送入一个特殊的逻辑表（inserted 表）中，触发器会根据INSERT表中的值决定如何处理。
4）UPDATE选项为创建UPDATE触发器。
UPDATE触发器仅在对指定表中进行更新数据操作时激活。
UPDATE触发器激活后把将要被更新的原数据移入delete表中再将要被更新后的新数据的备份送入insert表中，UPDATE触发器对delete和inserted表进行检查，并决定如何处理。
5）NOT FOR REPLICATION 选项说明当一个复制过程在修改一个触发器表时，与该表相关联的触发器不能被执行。
一个触发器只适用于一个表，每个表最多只能有三个触发器，它们分别是INSERT、UPDATE和DELETE触发器。
触发器仅在实施数据完整性和处理业务规则时使用。
3.实验内容本实验的内容为：使用SQL设计触发器，通过SQL Server企业管理器定义它们。
1.具体完成下面例题：利用触发器来保证学生选课库中选课表的参照完整性，以维护其外码与参照表中的主码一致。
CREATE TRIGGER SC_inserted ON cjdjFOR INSERTAS IF(SELECT COUNT(*)FROM student,inserted,kcWHERE student.xh = inserted.xh AND inserted.kch = kc.kch)=0 ROLLBACK TRANSACTION2.把课件第5章中卷烟数据库及其触发器的例子在Sql server中完成。
创建一个卷烟数据库 :CREATE TABLE卷烟库存表(卷烟品牌varchar(40)PRIMARY KEY NOT NULL,库存数量int NULL,库存单价money NULL,库存金额money NULL)--业务规则：库存金额= 库存数量* 库存单价—CREATE TABLE卷烟销售表(卷烟品牌VARCHAR(40)PRIMARY KEY NOT NULL,购货商VARCHAR(40)NULL,销售数量INT NULL,销售单价MONEY NULL,销售金额MONEY NULL)--业务规则：销售金额= 销售数量* 销售单价—CREATE TRIGGER T_INSERT_卷烟库存表ON卷烟库存表FOR INSERTAS--提交事务处理BEGIN TRANSACTION--强制执行下列语句，保证业务规则UPDATE卷烟库存表SET库存金额=库存数量*库存单价WHERE卷烟品牌IN(SELECT卷烟品牌from INSERTED)COMMIT TRANSACTIONGOINSERT INTO卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values('红塔山新势力',100,12.0000,1200.0000)INSERT INTO卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values('红塔山人为峰',100,22.0000,null)INSERT INTO卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values('云南映像',100,60,500)INSERT INTO卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values('玉溪',0,30,0)CREATE TRIGGER T_INSERT_卷烟销售表ON卷烟销售表FOR INSERTASBEGIN TRANSACTION--检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零IF NOT EXISTS(SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN(SELECT卷烟品牌FROM INSERTED)) BEGIN--返回错误提示RAISERROR('错误！该卷烟不存在库存，不能销售。
',16,1)--回滚事务ROLLBACKRETURNENDIF EXISTS(SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN(SELECT卷烟品牌FROM INSERTED)AND 库存数量<= 0)BEGIN--返回错误提示RAISERROR('错误！该卷烟库存小于等于，不能销售。
',16,1) --回滚事务ROLLBACKRETURNEND--对合法的数据进行处理--强制执行下列语句，保证业务规则UPDATE卷烟销售表SET销售金额=销售数量*销售单价WHERE卷烟品牌IN(SELECT卷烟品牌FROM INSERTED)DECLARE @卷烟品牌VARCHAR(40)SET @卷烟品牌=(SELECT卷烟品牌FROM INSERTED)DECLARE @销售数量MONEYSET @销售数量=(SELECT销售数量FROM INSERTED)UPDATE卷烟库存表SET库存数量=库存数量- @销售数量,库存金额=(库存数量- @销售数量)*库存单价WHERE卷烟品牌= @卷烟品牌COMMIT TRANSACTIONGOINSERT INTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) values('红塔山新势力','某购货商',10,12,1200)GOINSERT INTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) values('红塔山人为峰','某购货商',10,22,2000)GOINSERT INTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) values('红塔山新势力','某购货商',10,12,1200)GOINSERT INTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) values('红塔山人为峰','某购货商',10,22,2000)GOINSERT INTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) values('红河V8','某购货商',10,60,600)GOINSERT INTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)values('玉溪','某购货商',10,30,300)GOSELECT*FROM卷烟库存表SELECT*FROM卷烟销售表GO4.实验步骤1、在企业管理器中创建触发器1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库），打开表文件夹，在表窗口中用鼠标右击触发器所属的表（本例为选课表）。
2）在弹出菜单上选择“所有任务”“管理触发器”项，则弹出如图所示的触发器属性对话框。
图触发器属性对话框如果要新建触发器，在文本[T]的文本框中输入创建触发器的SQL语句（见实验内容），单击“检查语法”按钮进行语法的检查，检查无误后，单击确定按钮。
3）如果要修改触发器，则在名字弹出项中选择要修改的触发器名（本例为SC_inserted）,然后在文本[T]的文本框中对已有的内容进行修改，检查无误后单击“确定”按钮。
4）如果要删除触发器，则在名字弹出项中选择要删除的触发器名（本例为SC_inserted），并检查文本[T]的文本框中的内容，看是否选得正确，最后单击“删除”按钮。
2、验证触发器1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库）。
此时，可在选课表中输入一条记录（学号：’96001’，课程号：’C8’，成绩：88）。
显然，这条记录是不符合参照完整性的。
通过企业管理器输入时，会出现错误提示，违反了数据库的约束（如果约束C5和C6同时存在的话）。
2）如果约束C5和C6存在，则删除之（参见实验一）3）打开SQL查询分析器，输入语句“INSERT INTO 选课V ALUES（‘96001’，‘C8’，88）”，查看执行结果。
删除触发器（本例为SC_inserted），重复步骤3。
5.收获体会：。