《SQL Server数据库应用技术实例教程》课件任务四.ppt

1、任务四任务四 保证数据的准确性、一致性和完备性保证数据的准确性、一致性和完备性 在在SQL Server 2005SQL Server 2005中实施数据完整性中实施数据完整性任务：任务：用约束、规则、默认值、触发器等方法强制实施数据完整性，保证输入到数据库中的数据的准确性、一致性和完备性。4.1 了解数据完整性4.2 应用约束4.3 创建和管理关系图4.4 应用默认值4.5 应用规则4.6 应用触发器 4.7 知识进阶 项目实训 了解数据完整性的概念 SQL Server 2005中的数据完整性机制 创建、应用约束实施数据完整性，管理约束 使用关系图工具创建数据库关系图实施数据完整性 创建、

2、应用默认值实施数据完整性，管理默认值 创建、应用规则实施数据完整性，管理规则 创建、应用触发器实施数据完整性，管理触发器学习目标学习目标在SQL Server 2005中实施数据完整性表4.1 4.1 了解数据完整性了解数据完整性任务：建立数据完整性的概念，了解SQL Server 2005中的数据完整性机制。在SQL Server 2005中实施数据完整性表4.1.1 什么是数据完整性4.1.2 “高职院校教学管理系统”的数据完整性 需求分析4.1.1 什么是数据完整性1什么是数据完整性什么是数据完整性数据完整性指存储在数据库中的数据在逻辑上是一致的、准确的、完备的，这是现代数据库的一个典型

3、特征。2数据完整性的分类数据完整性的分类 域完整性。即列完整性，产生在表中的一列或多列上，指定某个列的输入是否合法，以及确定该列是否允许空值。实体完整性。即行完整性，指定表中所有行的唯一性。它要求表中所有的行有一个唯一的标识符，即为主键。参照完整性。即引用完整性，产生在表和表之间，要求在主键（被参照表）和外键之间的参照关系中得到维护，即参照表中的外键值必须始终参照被参照表中的主键值。用户自定义完整性。用户自定义的一组规则，不属于以上三种完整性的任一种。在SQL Server 2005中实施数据完整性表4.1.2 “高职院校教学管理系统”的数据完整性 需求分析1 1“高职院校教学管理系统高职院校

4、教学管理系统”中的域完整性中的域完整性（1）表student_info中的列zipcode和teacher_info中的列tzipcode拥有自定义数据类型code。（2）表student_grade中的列grade和credit使用检查约束指定取值范围分别为0=grade=100和0=credit=6。（3）表student_info和表teacher_info中的列gender指定为bit型，取值只能是1（男）或0（女）。（4）多个列拥有DEFAULT约束，如列profession的默认值为“副教授”，gender的默认值为1。2 2“高职院校教学管理系统高职院校教学管理系统”中的实体完整

5、性中的实体完整性（1）共包含九个表，每个表具有唯一一个主键（2）表teacher_info的列identity_card指定UNIQUE约束，避免身份证号码重复。在SQL Server 2005中实施数据完整性表3 3“高职院校教学管理系统高职院校教学管理系统”中的参照完整性中的参照完整性student_grade(stud_id+course_id)teacher_class_course(teac_id+class_id+course_id)student_info(stud_id)teacher_info(teac_id)department_info(dep_id)class_info

6、(class_id)book_info(book_id)course_info(course_id)classroom_info(croom_id)“高职院校教学管理系统”中的参照完整性4.1.2 “高职院校教学管理系统”的数据完整性 需求分析在SQL Server 2005中实施数据完整性表4.2 应用约束任务：任务：了解SQL Server 2005中的约束类型及作用。创建、应用NOT NULL、PRIMARY KEY、UNIQUE、CHECK、DEFAULT、FOREIGN KEY约束强制数据完整性，对约束进行简单管理。在SQL Server 2005中实施数据完整性表4.2.1 约束的

7、类型4.2.2 使用SSMS创建约束4.2.3 使用T-SQL创建约束 约束（约束（CONSTRAINT）是强制数据完整性非常有效的方法，属于声明型数据完整性。对数据库来说，约束分为列约束和表约束。列约束作为列定义的一部分只作用于该列。表约束作为表定义的一部分，作用于表的多列。4.2.1 约束的类型在SQL Server 2005中实施数据完整性表 SQL Server 2005包含六种基本约束：（1）PRIMARY KEY 约束：实施实体完整性。（2）UNIQUE 约束：实施实体完整性。（3）DEFAULT约束：实施域完整性。（4）CHECK约束：实施域完整性。（5）NOT NULL约束：实

8、施域完整性。（6）FOREIGN KEY约束：实施参照完整性。4.2.1 约束的类型在SQL Server 2005中实施数据完整性表 如下代码中既包含列约束，又有表约束。CREATE TABLE course_info4(course_id char(10)NOT NULL,-列约束，列定义的一部分 course_name nvarchar(40)NOT NULL,-列约束，book_id char(10)NOT NULL,-列约束，列定义的一部分 week_period tinyint NULL DEFAULT(4),-列约束，列定义的一部分 total_period tinyint NU

9、LL DEFAULT(60),-列约束，列定义的一部分 credit tinyin NOT NULL CHECK(0=credit AND credit=0 AND grade=100GOsp_bindrule rule_grade,student_grade.gradeGOsp_unbindrule student_grade.gradeGODROP RULE rule_gradeGO应用规则 在SQL Server 2005中实施数据完整性表 在创建、绑定、解除绑定规则的过程中应注意以下问题：（1）将规则绑定到某个列时，这个规则将首先检查该列中 现有的数据。（2）规则可以是值的范围、单值或

10、值的清单，或者是由 like子句定义的掩码。（3）当将多个规则绑定到同一列时，只有最后被绑定的 规则起作用。（4）要想删除一个规则对象，必须先解除它与所有对象 的绑定。应用规则 在SQL Server 2005中实施数据完整性表 任务：了解触发器的作用、特点及类型，使用T-SQL的CREATE TRIGGER语句创建触发器，对“高职院校教学管理系统”数据库JXGL实施数据完整性及其他特殊业务。在SQL Server 2005中实施数据完整性表4.6.1 触发器的类型4.6.2 触发器中的inserted表和deleted表4.6.3 创建触发器4.6.4 使用触发器实施特殊业务规则4.6.5

11、管理触发器4.6 应用触发器应用触发器 SQL Server 2005 提供了两种主要机制来强制执行数据完整性和业务规则：约束和触发器（TRIGGER）。触发器是一种实施复杂数据完整性的特殊存储过程，如使用外键约束所不能实现的复杂参照完整性。它在数据操作语句执行时自动生效。通过触发器可实现跨数据库的参照完整性。SQL Server 2005包括两大类触发器：DML 触发器和 DDL 触发器。1DDL触发器触发器 DDL触发器是SQL Server 2005 的新增功能，当服务器或数据库中发生数据定义语言（DDL）事件时触发。2DML触发器触发器 当数据库中发生数据操作语言（DML）事件时，如在

12、指定的表或视图中执行 INSERT、UPDATE或 DELETE 语句时，触发 DML 触发器。4.6.1 触发器的类型在SQL Server 2005中实施数据完整性表4.6.1 触发器的类型 DML 触发器又分为以下几种类型：（1）AFTER触发器。在执行了 INSERT、UPDATE 或 DELETE 语句之后触发AFTER触发器，其只能在表上指定。被INSERT语句触发的触发器称为INSERT型 AFTER触发器，被DELETE语句触发的触发器称为DELETE型 AFTER触发器，被UPDATE语句触发的触发器称为UPDATE型 AFTER触发器。（2）INSTEAD OF触发器。执行

13、 INSTEAD OF 触发器代替触发动作进行激活，但同一种操作只能定义一种触发器。INSTEAD OF 触发器可以定义在表或视图上。（3）CLR触发器。CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器，还可以是 DDL 触发器。提示：提示：使用FOR和AFTER声明的触发器在行为上是相同的，一般情况下用户习惯于使用FOR子句指定AFTER触发器。如例4-6中，使用语句FOR INSERT指定INSERT型 AFTER触发器。如果没有特殊说明，本书定义、使用的都是AFTER触发器。提示：提示：简单地说，AFTER 触发器告诉SQL语句执行之后（数据操作完成，数据已经变动

14、）做什么。而INSTEAD OF触发器告诉当要执行INSERT、UPDATE 或 DELETE 操作时用什么别的操作来代替，即数据操作不做，而用触发器指定的操作代替。在SQL Server 2005中实施数据完整性表4.6.1 触发器的类型3DML触发器的特点触发器的特点DML触发器的特点如下：（1）强制比CHECK 约束更为复杂的数据完整性。（2）实现数据库中多个表的级联更改。例如，表A的触发器A中包含对表B的数据操作A，而该操作A（插入、删除或修改）会导致表B中的触发器B被触发，触发器B指定的操作B又会影响表C的数据。（3）一个表中可包含多个同类DML触发器，并允许采取不同的操作响应同一个

15、修改语句。（4）维护非规范化数据，实现特殊业务。（5）使用自定义的错误消息，捕获破坏数据完整性的操作并返回自定义的错误提示信息。提示：提示：如果触发器表上存在约束，则在INSTEAD OF触发器执行后、AFTER触发器执行前检查这些约束。如果违反了约束，则回滚INSTEAD OF触发器操作，但不执行AFTER触发器。在SQL Server 2005中实施数据完整性表 触发器被定义在数据库中的表上，与表紧密联系在一起，这个表称为触发器表。当触发器被触发，SQL Server 2005为每个触发器在服务器内存中创建两个临时表：inserted表和deleted表。这是两个逻辑表（不会存储在数据库文

16、件中），由系统自动维护而不允许用户直接对它进行修改。触发器工作完成后，这两个表也随之被删除。它们的结构总是与对应的触发器表的结构相同。4.6.2 触发器中的inserted表和deleted表在SQL Server 2005中实施数据完整性表4.6.2 触发器中的inserted表和deleted表1inserted表表用于存储触发器表中INSERT和UPDATE语句所影响的记录的副本。在一个插入或更新的事务处理中，新插入的记录被同时添加到触发器表和inserted表中。INSERT触发器的工作过程如下：（1）在定义了INSERT 触发器的触发器表上执行 INSERT。（2）该表中的INSER

17、T 触发器被触发，在服务器内存中创建inserted表，表结构与触发器表相同。（3）INSERT 语句插入新记录到触发器表中，同时该记录被复制到inserted表中。（4）触发器指定的操作被执行，由于触发器的作用，使该操作既可以影响触发器表本身，也可以影响另外的表。在SQL Server 2005中实施数据完整性表2deleted表表deleted表用于存储触发器表中DELETE和UPDATE语句所影响的记录的副本。在触发器表中执行DELETE或UPDATE操作时，记录从触发器表中删除，同时传输（复制）到deleted表中暂存起来。因此，deleted表和触发器表通常没有相同的数据行，因为此时

18、记录已经从触发器表中被删除。DELETE 触发器的工作过程如下：（1）在定义了 DELETE 触发器的触发器表上执行 DELETE。（2）该表的DELETE触发器被触发，在服务器内存中创建一个deleted表，表结构与触发器表相同。（3）DELETE 语句删除的记录同时被deleted 表记录下来。（4）触发器指定的操作被执行，由于触发器的作用，使该操作既可以影响触发器表本身，也可以影响另外的表。4.6.2 触发器中的inserted表和deleted表在SQL Server 2005中实施数据完整性表 提示：提示：一个UPDATE操作可以分解为一个DELETE操作加一个INSERT操作。因此

19、，UPDATE型触发器被触发时会同时创建deleted 表和inserted 表。触发器的定义包含以下四个要素：（1）名称：名称：有一个符合标志符命名规则的触发器名称。（2）定义的目标：定义的目标：触发器必须定义在表或视图上，而AFTER触发器只能定义在表上。（3）触发条件：触发条件：分别由INSERT、UPDATE 或 DELETE 语句触发。（4）触发逻辑：触发逻辑：被触发后会执行哪些操作（处理逻辑）。4.6.3 创建触发器在SQL Server 2005中实施数据完整性表1CREATE TRIGGER语句的语法语句的语法使用CREATE TRIGGER语句创建触发器，基本语法如下：CRE

20、ATE TRIGGER trigger_nameON table_name|view_nameWITH ENCRYPTIONFOR|AFTER|INSTEAD OF INSERT,DELETE,UPDATEAS(sql_statement)4.6.3 创建触发器其中：其中：trigger_name：触发器名称，必须符合标识符的命名规则，且在当前数据库中唯一。table_name|view_name：指定创建触发器的表或视图，即触发器表或视图。INSERT,DELETE,UPDATE：指明在触发器表中执行哪些操作时会激活对应类型的触发器。必须指定一个选项，选项顺序任意，选项之间用逗号分隔。AS：

21、用来指定触发器要执行的操作序列。sql_statement：触发器要执行的操作序列。在SQL Server 2005中实施数据完整性表2创建创建INSERT触发器触发器【例4-6】在数据库JXGL的表class_info中创建触发器class_info_insert，实现向表class_info中插入记录时显示提示信息“该表中插入了新数据行！”。代码如下：USE JXGLGOCREATE TRIGGER class_info_insert ON class_info -指定触发器表 FOR INSERT -指定触发条件，也可使用AFTER INSERT语句 AS BEGIN PRINT N该表

22、中插入了新数据行！-显示提示信息 SELECT*FROM inserted -显示输入的新数据行 ENDGO4.6.3 创建触发器在SQL Server 2005中实施数据完整性表3创建创建DELETE触发器触发器【例4-7】在数据库JXGL的表student_info中创建触发器student_info_delete，实现从表student_info中删除记录时自动更新表class_info中该班级的学生数（stud_num）。代码如下：USE JXGLGOCREATE TRIGGER student_info_deleteON student_info -指定触发器表AFTER DELET

23、E -指定触发条件,也可使用FOR DELETE语句AS /*更新表class_info中的该班级的学生数*/4.6.3 创建触发器在SQL Server 2005中实施数据完整性表UPDATE class_info SET class_info.stud_num=(SELECT COUNT(student_info.stud_id)FROM student_info,deleted WHERE student_info.class_id=deleted.class_id)-将deleted表中class_id字段的值（班级号）指定给表student_info，作为统计数据的条件 FROM c

24、lass_info,deleted WHERE class_info.class_id=deleted.class_id-将deleted表中class_id字段的值（班级号）指定给class_info，作为更新数据的条件 GO4.6.3 创建触发器 其中：其中：UPDATE语句：更新删除数据的班级的学生数（与其他班级学生数无关）。其中子句WHERE class_info.class_id=deleted.class_id的作用是，临时表deleted中的数据是向触发表student_info删除记录的副本，其中class_id字段的值正是刚删除的那条记录的班级号，则把该该班级号指定给clas

25、s_info表，更新该表中指定班级号的学生数。SELECT语句：重新统计删除数据的班级的学生数（与其他班级无关）。其中子句WHERE student_info.class_id=deleted.class_id的作用是，临时表deleted中的数据是向触发表student_info删除记录的副本，其中class_id字段的值正是刚删除新记录的班级号，则把该班级号指定给student_info表，统计该表中指定班级号的学生数。COUNT(student_info.student_id)：计数函数，统计student_id的个数（因为在同一个班级中学号是唯一的，因此学号数就是学生数）。在SQL S

26、erver 2005中实施数据完整性表4创建创建UPDATE触发器触发器【例4-8】在数据库JXGL的表student_info中创建触发器student_info_update，实现当一名学生由一个班调到另一个班后，自动更新表class_info中相应班级的学生数（stud_num）。代码如下：USE JXGL GO CREATE TRIGGER student_info_update ON student_info -指定触发器表 FOR UPDATE -指定触发条件 AS IF(UPDATE(class_id)BEGIN4.6.3 创建触发器在SQL Server 2005中实施数据完整

27、性表 UPDATE class_info /*更新数据前class_id值所指定的班级学生数，在更新后要减少一个*/SET stud_num=stud_num-1 FROM class_info,deleted WHERE class_info.class_id=deleted.class_id -将临时表deleted中class_id字段的值（班级号）指定给表class _info，作为减少人数的条件 /*更新数据后，class_id值所指定的班级的学生数要增加一个*/UPDATE class_info SET stud_num=stud_num+1 FROM class_info,ins

28、erted WHERE class_info.class_id=inserted.class_id -将临时表inserted中class_id字段的值（班级号）指定给表class_info，作为增加人数的条件 GO END GO4.6.3 创建触发器提示：提示：执行以上代码：在“消息”窗格中显示“命令已成功完成”。再次执行，会显示错误提示信息“数据库中已存在名为student_info_update 的对象”。这充分说明，在一个数据库中触发器不能重名。在SQL Server 2005中实施数据完整性表验证以上代码：验证以上代码：将表student_info中学号为D06010209的学生的c

29、lass_id值由D060102改为D060103。（1）更新数据前，先查看D060102和D060103两个班的学生人数。代码如下：SELECT*FROM class_info WHERE class_id=D060102 OR class_id=D060103 执行以上代码。（2）更新班级号，代码如下：UPDATE student_info SET class_id=D060103 WHERE stud_id=D06010209 执行以上代码。（3）再次执行的代码，4.6.3 创建触发器在SQL Server 2005中实施数据完整性表5应用触发器实施参照完整性【例4-9】在数据库JXGL

30、的表student_info中创建触发器student_info_update2，实现当stud_id值发生变化时，在student_grade中的stud_id值作相同的更新。代码如下：CREATE TRIGGER student_info_update2 ON student_info FOR UPDATE AS IF(UPDATE(stud_id)BEGIN UPDATE student_grade SET stud_id=inserted.stud_id FROM inserted,student_grade,deleted WHERE student_grade.stud_id=de

31、leted.stud_id END4.6.3 创建触发器在SQL Server 2005中实施数据完整性表1使用SSMS管理触发器 创建触发器时，如果使用WITH ENCRYPTION子句进行加密，将不能通过快捷菜单中的“修改”命令（该命令已变成灰色）对触发器定义进行修改。4.6.5 管理触发器在SQL Server 2005中实施数据完整性表4.6.5 管理触发器2使用T-SQL语句查看触发器（1）使用系统存储过程sp_helptrigger 可以查看指定表中的触发器及其类型信息。如查看表student_info中的触发器信息，代码如下：sp_helptrigger student_info

32、 GO（2）使用系统存储过程sp_helptext 可以查看指定触发器的定义信息。查看触发器student_info_delete的定义，代码如下：sp_helptext student_info_deleteGO 在SQL Server 2005中实施数据完整性表3使用T-SQL语句修改触发器 使用ALTER TRIGGER语句可以修改已经创建好的触发器定义。其语法与CREATE TRIGGER语句一致。使用ALTER TRIGGER语句不能对触发器进行更名操作。4使用T-SQL语句删除触发器 使用DROP TRIGGER语句可删除当前数据库中的一个或多个触发器。4.6.5 管理触发器在SQ

33、L Server 2005中实施数据完整性表【例4-11】删除触发器student_info_delete。代码如下：USE JXGLGO/*判断当前数据库中是否存在名为student_info_delete的触发器*/IF EXISTS(SELECT name FROM sysobjects WHERE name=student_info_delete AND type=TR)DROP TRIGGER student_info_deleteGO【例4-12】删除触发器student_info_delete2和student_info_update。代码如下：USE JXGLGODROP TR

34、IGGER student_info_delete2,student_info_updateGO4.6.5 管理触发器在SQL Server 2005中实施数据完整性表5触发器的嵌套当一个触发器被触发时又触发了另一个触发器，而另一个触发器的操作又触发了下一个触发器，这些触发器被称为嵌套触发器。触发器最多可以嵌套32层，并且可以控制是否通过“嵌套触发器”服务器配置选项进行触发器嵌套设置。当允许使用嵌套触发器时，如果嵌套链中的一个触发器开始无限循环，直到超出嵌套层数时，触发器将自动停止。6触发器的递归当一个触发器被触发时，其包含的操作再次触发该触发器。触发器的再次触发执行又会因重新修改数据而激活触

35、发器，如此循环，直到某个条件限制终止触发器的触发，或超出系统限制的情况下自动停止。这种触发器调用或触发自身的现象称为触发器的递归。4.6.5 管理触发器在SQL Server 2005中实施数据完整性表任务：了解触发器与约束的不同应用特点。了解触发器与约束的不同应用特点。在SQL Server 2005中实施数据完整性表4.7 知识进阶知识进阶4.7.1 触发器与约束的比较4.7.2 级联参照完整性 触发器与约束的比较如下：（1）如果触发器表中同时有约束存在，则约束优先。（2）对同一张表，若约束与触发器定义冲突，则触发器定义的操作将不被执行。（3）触发器与约束的最大区别在于：创建约束时，约束会

36、首先对数据库中已有的数据进行强制；而触发器对创建之前的数据不起作用。（4）INSERT触发器也可以由INSERT INTO语句触发。（5）在触发器定义中不允许使用以下T-SQL语句：CREATE/ALTER/DROP/LOAD/RESTORE DATABASE LOAD/RESTORE LOG DISK INIT DISK RESIZE RECONFIGURE4.7.1 触发器与约束的比较在SQL Server 2005中实施数据完整性表 CREATE TABLE 和 ALTER TABLE 语句的 REFERENCES 子句支持 ON DELETE 和 ON UPDATE 子句。（1）ON

37、DELETE NO ACTION。如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 DELETE 语句，即不能首先删除主键值。（2）ON UPDATE NO ACTION。如果试图更新某一行中的键值，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 UPDATE 语句，即不能首先更新主键值。4.7.2 级联参照完整性在SQL Server 2005中实施数据完整性表1利用CASCADE选项实现级联参照 在SQL Server 2005中，FOREIGN KEY约束包含一个CASCADE选项，允许对定义了UNIQUE和PRIMARY KEY约束的列值的修改

38、传播到参照该列值的任何外键值上，称为级联参照完整性。（1）ON DELETE CASCADE：指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则也将删除所有包含那些外键的行。（2）ON UPDATE CASCADE：指定如果试图更新某一行中的键值，而该行的键值被其他表的现有行中的外键所引用，则外键值也将更新为该键指定的新值。4.7.2 级联参照完整性在SQL Server 2005中实施数据完整性表2多个级联参照 单独的 DELETE 或 UPDATE 语句可启动一系列级联参照操作。例如，一个数据库包含三个表：TableA、TableB 和 TableC。针对 TableA

39、中的主键，用 ON DELETE CASCADE 定义TableB 中的外键。针对TableB 中的主键，用 ON DELETE CASCADE 定义 TableC 中的外键。如果 DELETE 语句删除 TableA 中的行，则该操作也将删除 TableB 中具有与 TableA 中所删除的主键匹配的任何外键的所有行，然后删除 TableC 中具有与 TableB 中所删除的主键匹配的任何外键的所有行。4.7.2 级联参照完整性在SQL Server 2005中实施数据完整性表3触发器和级联参照级联参照操作按下列方式激发AFTER UPDATE或AFTER DELETE触发器：首先执行由原始

40、DELETE或UPDATE操作导致的所有级联引用操作。如果为受影响的表定义了任何AFTER触发器，则在执行完所有级联操作后激发这些触发器。这些触发器将按与级联操作相反的顺序激发。如果单个表中存在多个触发器，它们将按随机顺序激发，除非专门为表指定了第一个或最后一个触发器，此顺序是使用 sp_settriggerorder 指定的。4.7.2 级联参照完整性在SQL Server 2005中实施数据完整性表项目实训项目实训 1实训题目实训题目 “销售合同管理系统”数据库实施数据完整性。2实训目的实训目的（1）了解数据完整性的概念。（2）掌握约束的创建与应用。（3）掌握规则和默认值对象的创建与应用。（4）掌握触发器的创建与使用。3实训内容实训内容（1）使用SSMS或T-SQL为DEPARTMENT表创建主键，创建检查约束、非空约束。（2）使用SSMS或T-SQL为EMPLOYEE表创建外键约束。（3）用关系图描述数据库所有表的关系，如图4-31所示。（4）在表DEPARTMENT中创建触发器department_info_update，实现当depart_id值发生变化时，在表EMPLOYEE中的depart_id值作相同更新。在SQL Server 2005中实施数据完整性表