实验17实现数据完整性

1、实验17实现数据完整性（创建触发器）目标l 使用SQL Server Management Studio创建触发器l 使用Transact-SQL创建触发器触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效，唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。触发器可以查询其他表，并可以包含复杂的 Transact-SQL 语句。 触发器是在特定表上进行定义的，该表也称为触发器表。当有操作针对触发器表时，例如在表中插入、删除、更新数据时，如果该表有相应操作类型的触发器，那么触发器就自动触发执行。触发器语句中可以使用两种特殊的表：deleted 表和 i

2、nserted 表。Microsoft SQL Server 自动创建和管理这些表，可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件，但不能直接对表中的数据进行更改。deleted 表用于存储 DELETE的复本和UPDATE语句修改前所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。inserted 表用于存储 INSERT的副本和 UPDATE语句所影响行的修改后的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。inserted 表中的行是触发器表中新行的

3、副本。此实验要求：（1）了解数据库中各表的结构及其之间的关系，以创建触发器。（2）已完成实验10，成功创建了数据库stu中的各个表。（3）了解创建和删除触发器的方法。使用 SQL Server Management Studio 创建触发器（1）在对象资源管理器中，依次展开【数据库】、【stu】、【表】，展开要建立触发器的表，如【student】表。（2）选择【触发器】右击，然后单击【新建触发器】，在打开的模板代码窗口中，修改或输入触发器脚本，如图17-1。图17-1 创建触发器（3）触发器脚本编写完成后，单击【执行】按钮完成创建触发器。使用 SQL Server Management Stu

4、dio 修改、删除触发器（1）在对象资源管理器中，依次展开【数据库】、【stu】、【表】，展开要修改或删除的触发器所在的表，如【student】表。（2）展开【触发器】，然后选择要修改或删除的触发器右击，选择【编写触发器脚本为】或【修改】可以查看并修改触发器；选择【删除】能删除不需要的触发器；选择【禁用】或【启用】能控制触发器是否生效。使用 Transact-SQL 创建触发器（1）在 SQL Server Management Studio 中，单击工具栏上的【新建查询】按钮。（2）在新的空白查询窗口中，输入以下 Transact-SQL 代码，创建一个触发器，当对表student做inse

5、rt和update操作时，会自动触发TR_S_Age触发器，保证学生年龄应该大于等于10，并小于等于50，否则取消该次插入或修改操作。USE stuGOCREATE TRIGGER TR_S_Age ON students.student FOR insert,update AS DECLARE iAge INTSELECT iAge=sage FROM inserted IF iAge<10 OR iAge>50BEGINPRINT '学生年龄应该大于等于10，小于等于50'ROLLBACK TRANSACTIONEND（3）单击工具栏上的【执行】按钮。（4）该命

6、令成功完成之后，选择对象资源管理器中的【触发器】文件夹右击，然后单击【刷新】以确认TR_S_Age触发器已创建好。（5）单击工具栏上的【新建查询】按钮。（6）在新的空白查询窗口中，输入以下 Transact-SQL 代码。USE stuINSERT INTO students.student VALUES('008','王红','女',20,'cs')（7）在工具栏上，单击【执行】按钮。（8）确认可以插入数据。（9）在新的空白查询窗口中，输入以下 Transact-SQL 代码。USE stuINSERT INTO student

7、s.student VALUES('009','王英','女',8,'cs')（10）在工具栏上，单击【执行】按钮。（11）确认引发触发器TR_S_Age，取消该记录的插入。（12）在 SQL Server Management Studio 中，单击工具栏上的【新建查询】按钮，再创建一个新触发器。（13）在新的空白查询窗口中，输入以下 Transact-SQL 代码，创建一个触发器，当删除student表中数据时，级联删除sc表中与student表中当前删除记录学号相同的数据。USE stuGOCREATE TRIGGER TR

8、_S_SC ON students.student INSTEAD OF delete ASBEGIN DELETE FROM students.sc WHERE sno IN(SELECT sno FROM deleted)DELETE FROM students.student WHERE sno IN(SELECT sno FROM deleted)END（14）单击工具栏上的【执行】按钮。（15）该命令成功完成之后，选择对象资源管理器中的【触发器】文件夹右击，然后单击【刷新】以确认TR_S_SC触发器已创建好。（16）单击工具栏上的【新建查询】按钮。（17）在新的空白查询窗口中，输入以

9、下 Transact-SQL 代码。DELETE FROM students.student WHERE sno='01'（18）在工具栏上，单击【执行】按钮。（19）确认引发触发器TR_S_SC，级联删除了sc表中的数据。使用 Transact-SQL 修改触发器（1）在 SQL Server Management Studio 中，单击工具栏上的【新建查询】按钮。（2）在新的空白查询窗口中，输入以下 Transact-SQL 代码，修改TR_S_Age触发器，当对表student做insert和update操作时，会自动触发TR_S_Age触发器，保证学生年龄应该大于等于1

10、5，并小于等于40，否则取消该次插入或修改操作。USE stuGOALTER TRIGGER students.TR_S_Age ON students.student FOR insert,update AS DECLARE iAge INTSELECT iAge=sage FROM inserted IF iAge<15 OR iAge>40BEGINPRINT '学生年龄应该大于等于15，小于等于40'ROLLBACK TRANSACTIONEND（3）单击工具栏上的【执行】按钮。（4）该命令成功完成之后，选择对象资源管理器中的【触发器】文件夹右击，然后单击【

11、刷新】以确认TR_S_Age触发器已被修改。使用 Transact-SQL 删除触发器（1）在 SQL Server Management Studio 中，单击工具栏上的【新建查询】按钮。（2）在新的空白查询窗口中，输入以下 Transact-SQL 代码，删除TR_S_Age触发器。 （3）单击工具栏上的【执行】按钮。该命令成功完成之后，选择对象资源管理器中的【触发器】文件夹右击，然后单击【刷新】以确认TR_S_Age触发器已被删除。（1）创建表worker（number,name,sex,sage,department），并自定义2个约束U1和U2，其中U1规定name字段唯一，U2规定sage字段的上限是28。（2）在worker表中插入一条合法记录。（3）演示插入违反U2约束的例子，U2规定元组的sage属性的值必须<=28。（4）去除U2约束。（5）重新插入（3）中想要插入的数据，验证是否插入成功。（6）为worker表