数据完整性和一致性控制(约束_规则_触发器_事务_锁.ppt

1、第 8 章,第8章 SQL Server 2005数据库控制与维护,数据库应用教程,数据库的完整性控制,数据库的安全性管理,数据库备份与还原,第8章 SQL Server 2005数据库控制与维护,8.1.1 数据完整性概述,8.1.2 使用约束实施数据完整性控制,8.1.3 使用规则实施数据完整性控制,8.1 数据库的完整性控制,8.1.4 使用默认值实施数据完整性控制,8.1.5 使用触发器实施数据完整性控制,8.1.1 数据完整性概述,1完整性的概念,数据完整性是指数据的正确性、有效性、一致性和相容性。由于数据库是一个动态的集合，数据不断地被插入和被修改，因此由于主观或客观的原因，可能破

2、坏数据库的数据完整性 。,2破坏数据库的数据完整性的情况,（1）无效的数据被添加到数据库中。如：某在教师管理系统中，输入的教师号不存在 。,（2）对数据库的修改不一致。如：在两个不同的表中，同一教师的系号不同。,（3）将存在的数据修改为无效的数据。如：将某教师所在的系号修改为并不存在的系 。,8.1.1 数据完整性概述,3完整性机制,为了保证存放数据的完整性，DBMS应能对数据库进行数据完整性控制。在SQL Server2000中，提供了约束、默认值、规则、触发器等维护机制对数据完整性进行控制。,4完整性分类,在SQL Server中，数据完整性分成四类：实体完整性、域完整性、参照完整性和用户

3、自定义完整性。,8.1.1 数据完整性概述,域完整性：也可称为列完整性。 域完整性要求：向表中指定列输入的数据必须具有正确的数据类型、格式以及有效的数据范围。例如，在CollegeMIS数据库的SelectCourse表中，向Score（成绩）列输入数据时，不能出现字符，也不能输入小于0或大于100的数值。 域完整性的控制方法：主要有CHECK约束、外键约束、默认约束、默认值、非空定义、规则以及在创建表时设置的数据类型 。,实体完整性的控制方法：主要有主键约束、惟一索引、惟一约束和指定IDENTITY属性。,5完整性控制方法,参照完整性的控制方法：主要有外键约束，有时利用触发器也可实现参照完整

4、性控制。,8.1.2 使用约束实施数据完整性控制,在SQL Server中，可通过约束来实施数据库的数据完整性控制，常用的约束有NOT NULL(非空)约束、CHECK(检查)约束、UNIQUE(惟一)约束、PRIMARY KEY(主键)约束、FOREIGN KEY(外键)约束和DEFAULT(默认)约束 。,1约束的建立,【格式】ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED ( column ,.n ) ,（1）主键约束的创建,【功能】为table_name

5、作为表名指定的表增加一个由constraint_name指定名称的主键约束。,【例8-1】把CollegeMIS数据库中SelectCourse表中的主键pk_xuanke删除，然后重建。,USE CollegeMIS GO ALTER TABLE SelectCourse DROP CONSTRAINT pk_xuanke GO ALTER TABLE SelectCourse ADD CONSTRAINT pk_xuanke PRIMARY KEY(StuNo,TeaNo,CourseNo) GO,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-2】为CollegeMIS数据

6、库中的Department表的DepartName字段创建一个惟一约束，约束名为IX_DepartName。,（2）惟一约束的创建使用企业管理器创建惟一约束,在一张数据表中，有时除主键需要具有唯一性外，还有其他列也需要具有唯一性。例如，在“系部”表中，主键为“系部代码”，但是另外一个字段“系部名称”虽不是主键，也需保证它的唯一性，这时就需要创建表中的唯一约束。,使用对象资源管理器创建唯一约束 下面以“系部”表为例，为“系部名称”字段创建唯一约束。操作步骤如下： （1）在“对象资源管理器”窗格中，右击需要设置唯一约束的表（本例为“系部”表），在弹出的快捷菜单中选择“修改”命令，打开“表设计器”窗

7、口。,（2）在“表设计器”窗口中，右击需要设置为唯一约束的字段（本例为“系部名称”字段），在弹出的快捷菜单中选择“索引/键”命令，如图6-7所示，也可以直接单击工具栏中的“管理索引和键”按钮，打开“索引/键”对话框，如图6-8所示。,图6-7 选择“索引/键”命令,图6-8 “索引/键”对话框,（3）在打开的“索引/键”对话框中，单击“添加”按钮，结果如图6-9所示。,图6-9 单击“添加”按钮创建唯一约束,（4）设置好相关选项后，单击“关闭”按钮，完成唯一约束的创建。这时，不只是该表的主键必须为唯一，并且被设置为唯一约束的字段同样也必须为唯一。,8.1.2 使用约束实施数据完整性控制,1约束

8、的建立,【格式】 ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED ( column ,.n ) ,（2）惟一约束的创建使用Transact-SQL语句,【功能】为table_name作为表名指定的表增加一个由constraint_name指定名称的惟一约束。 说明：惟一约束默认的索引类型为NONCLUSTERED。,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-3】通过Transact-SQL语句为CollegeMIS数据库中的Department表的Te

9、lephone字段创建一个惟一约束，约束名为IX_Telephone。,（2）惟一约束的创建使用Transact-SQL语句,USE CollegeMIS GO ALTER TABLE Department ADD CONSTRAINT IX_Telphone UNIQUE (Telephone) GO,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-4】为CollegeMIS数据库中的“课程表”的“学分” 字段创建一个检查约束，约束名为CK_Grade。检查条件只允许为17分，不允许小于1分的学分和大于7分的学分出现。 。,（3）检查约束的创建使用对象资源管理器,检查约束对输入

10、的数据的值做检查，可以限定数据输入，从而维护数据的域完整性。,操作步骤如下： （1）在“对象资源管理器”窗格中，右击需要设置唯一约束的表（本例为“课程表”），在弹出的快捷菜单中选择“修改”命令，打开“表设计器”窗口。 （2）在“表设计器”窗口中右击需要创建检查约束的字段（本例为“学分”字段），在弹出的快捷菜单中选择“CHECK约束”命令，如图6-11所示，打开“CHECK约束”对话框。,图6-11 选择“CHECK约束”命令,（3）在“CHECK约束”对话框中，单击“添加”按钮，然后在“(名称)”文本框中输入检查约束名称，在约束“表达式”文本框中输入约束条件，这里输入“(学分=1 AND 学分

11、=7)”，如图6-12所示。,图6-12 设置“CHECK约束”条件,（4）单击“关闭”按钮关闭对话框，完成检查约束的创建。 注意：如果表中原来就有数据，并且数据类型或范围与所创建的约束相冲突，那么约束将不能成功创建。,8.1.2 使用约束实施数据完整性控制,1约束的建立,【格式】 ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK ( logical_expression),（3）检查约束的创建使用Transact-SQL语句,【功能】为table_name作为表名指定的表增加一个由constraint_name指定名称的检

12、查约束。 说明：“logical_expression”是检查约束的检查条件，通常是一个关系表达式或逻辑表达式。,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-5】使用Transact-SQL语句，为CollegeMIS数据库中的Student表的Birthday字段创建一个检查约束，约束名为CK_Birthday。检查条件为Birthday字段的值必须小于当前日期。,（3）检查约束的创建使用Transact-SQL语句,USE CollegeMIS GO ALTER TABLE Student ADD CONSTRAINT CK_Birthday CHECK (Birthda

13、y=GetDate() GO,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-6】为CollegeMIS数据库中的Student表的Sex字段创建一个默认约束，默认值为“男”。,（4）默认约束的创建 使用对象资源管理器,操作步骤如下： （1）在“对象资源管理器”窗格中，右击需要创建默认约束的表（这里为“学生”表），在弹出的快捷菜单中选择“修改”命令，打开“表设计器”窗口。 （2）选择需要创建默认约束的字段（这里为“性别”字段），然后在下方的“列属性”选项卡中的“默认值或绑定”文本框中输入默认值，本例为选择“性别”字段，在默认值中输入“男”，如图6-13所示。,在用户输入某些数据时

14、，希望一些数据在没有特例的情况下被自动输入，例如，学生的注册日期应该是数据录入的当天日期；学生的修学年限是固定的值；学生性别默认是“男”等情况，这个时候需要对数据表创建默认约束。 下面分别用例子说明如何在对象资源管理器中和利用SQL语句创建默认约束,图 6 | 13 输入默认值,注意：单引号不需要输入，在表保存后，在单引号外还会自动生成一对小括号。 （3）关闭“表设计器”窗口。,8.1.2 使用约束实施数据完整性控制,1约束的建立,【格式】 ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT constant_expres

15、sion FOR column_name,（4）默认约束的创建 使用Transact-SQL语句,【功能】为table_name作为表名指定的表增加一个由constraint_name指定名称的默认约束。 说明：“column_name”是要设置默认约束的列名，“constant_expression”是设置的默认值。,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-7】为CollegeMIS数据库中的Student表的Grade字段创建一个默认约束，约束名为DF_Grade，默认值为1。,（4）默认约束的创建使用Transact-SQL语句,USE CollegeMIS GO

16、ALTER TABLE Student ADD CONSTRAINT DF_Grade DEFAULT 1 FOR Grade GO,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-8】为CollegeMIS数据库中的“教师”表的创建一个外键约束，约束名为FK_ Teacher _Department。约束对应的主键表为Department （引用的主键字段为系部代码），本表（ “教师”表）的外键字段为系部代码。,（5）外键约束的创建 使用对象资源管理器,操作步骤如下： （1）在“对象资源管理器”窗格中，右击需要创建外键约束的表（这里为“教师”表），在弹出的快捷菜单中选择“修改”

17、命令，打开“表设计器”窗口。 （2）选择需要创建外键约束的字段（这里为“系部代码”字段），单击工具栏中的“关系”按钮，或右击该字段，在弹出的快捷菜单中选择“关系”命令，打开“外键关系”对话框，如图6-14所示。,外键是用来维护表与表之间对应唯一关系的一种方法。可以利用对象资源管理器或SQL语句来创建外键约束。,图6-14 “外键关系”对话框,（3）在“外键关系”对话框中，单击“添加”按钮，然后单击“表和列规范”的按钮，打开“表和列”对话框。在“主键表”下拉列表中选择“系部”表，在“外键表”的下拉列表框中选择“教师”表，分别在“主键表”和“外键表”的下面选择“系部代码”字段，如图6-15所示。,

18、图6-15 “表和列”对话框,（4）单击“确定”按钮，然后在“外键关系”对话框中进行相关设置后单击“关闭”按钮即可。,8.1.2 使用约束实施数据完整性控制,1约束的建立,【格式】 ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name ,.n) REFERENCES rel_table (ref_column ,.n) ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION NOT FOR REPLICATION,（5）外键约束的创建使

19、用Transact-SQL语句,【功能】为由table_name作为表名指定的表创建一个由constraint_name作为约束名的外键约束。,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-9】为CollegeMIS数据库中的SelectCourse表的创建一个外键约束，约束名为FK_SelectCourse_Student。约束对应的主键表为Student（引用的主键字段为StuNo），本表（SelectCourse表）的外键字段为StuNo。并设置级联删除和级联更新 。,（6）默认约束的创建使用Transact-SQL语句,USE CollegeMIS GO ALTER T

20、ABLE SelectCourse ADD CONSTRAINT FK_SelectCourse_Student FOREIGN KEY (StuNo) REFERENCES Student(StuNo) ON DELETE CASCADE ON UPDATE CASCADE GO,8.1.2 使用约束实施数据完整性控制,2约束定义的查看,对于创建好的约束，根据实际需要可以查看其定义信息。SQL Server 2005提供了多种查看约束信息的方法，经常使用的有利用对象资源管理器和系统存储过程。 （1）利用对象资源管理器查看约束信息 使用对象资源管理器查看约束信息的操作步骤如下： （1）在“对象

21、资源管理器”窗格中，右击要查看约束的表，在弹出的快捷菜单中选择“修改”命令，打开“表设计器”窗口。 （2）右击该表，在弹出的快捷菜单中分别选择“关系”、“索引/键”、“CHECK约束”等命令查看约束信息，如图6-16所示。,图6-16 查看约束信息菜单,（2）利用系统存储过程查看约束的定义,存储过程sp_helptext是用来查看约束的一个系统提供的存储过程，可以通过查询分析器来查看约束的名称、创建者、类型和创建时间。 其语法格式为： EXEC sp_help 约束名称 如果该约束有具体的定义和文本，那么可以用sp_helptext来查看。 其语法格式为：EXEC sp_helptext 约束

22、名称 【例6.9】使用系统存储过程查看student数据库中定义的入学时间（名称为ck_rxsj）的约束信息和文本信息。代码如下，结果如图6-17所示。,USE student GO EXEC sp_help ck_rxsj GO USE student GO EXEC sp_helptext ck_rxsj GO,8.1.2 使用约束实施数据完整性控制,3删除约束,使用对象资源管理器删除约束非常方便，正如在建立约束时一样，只需要在“表设计器”窗口中，将如图6-2所示的“设置主键”前的复选框取消即可删除主键约束，或删除默认值以删除默认约束；如图6-8所示，单击“删除”按钮删除唯一约束；如图6-

23、12所示，单击“删除”按钮删除检查约束；如图6-14所示，单击“删除”按钮删除外键约束。,（1）使用对象资源管理器来删除表约束,（2）使用Transact-SQL语句删除约束,【格式】 ALTER TABLE table_name DROP CONSTRAINT constraint_name ，n,【功能】删除由“table_name”作为表名指定的表中由“constraint_name”作为约束名的约束。可以同时删除多个约束，约束名之间用“,”隔开。,8.1.2 使用约束实施数据完整性控制,3删除约束,【例8-10】删除CollegeMIS数据库中的SelectCourse表的外键约束fk

24、_scStuNo（该约束在第5章中建立）。,USE CollegeMIS GO ALTER TABLE SelectCourse DROP CONSTRAINT fk_scStuNo GO,8.1.3 使用规则实施数据完整性控制,规则概述:,规则：是一种数据库对象，与CHECK约束的作用基本相同，也是用来限制输入值的取值范围，从而实施域完整性控制。 使用规则：首先要创建规则，然后把规则绑定到列上，或从列上解绑和删除。它与CHECK约束有以下几点不同：,（1）CHECK约束可以在建表时由CREATE TABLE语句将其作为表的一部分进行指定，而规则需要单独创建并绑定到列上；,（2）在一个列上只能

25、应用一个规则，但是却可以应用多个CHECK约束。,（3）一个规则只需定义一次就可以被多次应用，可以应用于多个表或多个列，还可以应用到用户定义的数据类型上。,（4）可以在查询分析器中用SQL语句完成。,8.1.3 使用规则实施数据完整性控制,1使用对象资源管理器管理规则,（1）规则的创建,【例8-11】为CollegeMIS数据库创建一个名为xb_rule规则，将它绑定到“学生”表的“性别”字段，保证输入数据只能为“男”或“女”,规则作为一种数据库对象，在使用前必须被创建。 创建规则的SQL命令是CREATE RULE。其语法格式如下： CREATE RULE rule_name AS cond

26、ition_expression 其中： rule_name是规则的名称，命名必须符合SQL Server 2005的命名规则。 condition_expression是条件表达式。,CREATE RULE xb_rule AS xb in(男,女),8.1.3 使用规则实施数据完整性控制,（2）规则的绑定,要使创建好的规则作用到指定的列或表等，还必须将规则绑定到列或用户定义的数据类型上才能够起作用。 在查询分析器中，可以利用系统存储过程将规则绑定到字段或用户定义的数据类型上。其语法格式如下： EXECUTE sp_bindrule 规则名称,表名.字段名|自定义数据类型名,EXEC sp_

27、bindrule xb_rule,学生.性别,将规则“xb_rule”绑定到“学生”表的“性别”列上,如果字段已经不再需要规则限制输入了，那么必须把已经绑定了的规则去掉，这就是解绑规则。在查询分析器中，同样用存储过程来完成解绑操作。其语法格式如下： EXECUTE sp_unbindrule 表名.字段名|自定义数据类型名,图6-18 删除xb_rule规则,（3）解绑规则,如果规则已经没有用了，那么可以将其删除。在删除前应先对规则进行解绑，当规则已经不再作用于任何表或字段等时，则可以用DROP RULE删除一个或多个规则。 其语法格式如下：DROP RULE 规则名称,n,（4）删除规则,【

28、例6.12】从student数据库中删除xb_rule规则。代码和结果如图6-18所示。,8.1.3 使用规则实施数据完整性控制,1使用Transact-SQL语句和系统存储过程管理规则,（1）创建规则,【格式】CREATE RULE rule_nameAS condition_expression,【功能】创建一个由“rule_name”指定名称的规则，规则的条件表达式由“condition_expression”指定。,（2）绑定规则,【格式】EXEC sp_bindrule rule_name tablename_columnname|UDTname,【功能】把rule_name规则绑定

29、到tablename_columnname指定的列上或UDTname指定的用户自定义类型上 。,【例8-13】为CollegeMIS数据库创建一个名为Sex_Rule的规则，规则的条件表达式为“Sex=男 OR Sex=女”。然后把该规则分别绑定到Student表的Sex字段和Teacher表的Sex字段。,USE CollegeMIS GO CREATE RULE Sex_Rule As Sex=男 OR Sex=女 GO EXEC sp_bindrule Sex_Rule ,Student.Sex EXEC sp_bindrule Sex_Rule,Teacher.Sex GO,8.1.3

30、 使用规则实施数据完整性控制,1使用Transact-SQL语句和系统存储过程管理规则,（3）解绑规则,【格式】EXEC sp_unbindrule tablename_columnname|UDTname,【功能】把tablename_columnname指定的列上或UDTname指定的用户自定义数据类型上绑定的规则去掉。,（4）删除规则,【格式】DROP RULE rule_name ,.n ,【功能】删除由rule_name作为规则名指定的规则。可删除多个规则，规则之间用“,”分开。,【例如】EXEC sp_unbindrule Teacher.Sex,【例如】DROP RULE Sex

31、_Rule,8.1.4 使用默认值实施数据完整性控制,1使用Transact-SQL语句和系统存储过程管理默认值,（1）创建默认值,【格式】CREATE DEFAULT default_nameAS default_constant,【功能】创建一个由“default_name”指定名称的默认值，默认值的值由“default_constant”确定。default_constant是常量表达式，可以包含常量、内置函数或数学表达式。,（2）绑定默认值,【格式】EXEC sp_bindefault default_name tablename_columnname|UDTname,【功能】把def

32、ault_name默认值绑定到tablename_columnname指定的列上或UDTname指定的用户自定义数据类型上。,【例8-14】为CollegeMIS数据库创建一个名为Sex_Default的默认值，值为“男”。然后把该默认值绑定到Teacher表的Sex字段上。,USE CollegeMIS GO CREATE Default Sex_Default As 男 GO EXEC sp_bindefault Sex_Default,Teacher.Sex GO,8.1.4 使用默认值实施数据完整性控制,1使用Transact-SQL语句和系统存储过程管理默认值,（3）解绑默认值,【格

33、式】EXEC sp_unbindefault tablename_columnname|UDTname,【功能】把tablename_columnname指定的列上或UDTname指定的用户自定义数据类型上绑定的默认值去掉。,（4）删除默认值,【格式】DROP DEFAULT default_name ,.n ,【功能】删除由default_name作为默认值名指定的默认值。可删除多个默认值，默认值之间用“,”分开。,【例如】EXEC sp_unbindefault Teacher.Sex,【例如】DROP DEFAULT Sex_Default,数据完整性强制选择方法,SQL Server

34、2005提供了许多实现数据完整性的方法。除了本章介绍的约束、默认和规则外，还有前面章节介绍的数据类型和后面需要学习的触发器等。对于某一问题可能存在多种解决办法，应该根据系统的实际要求，从数据完整性方法实现的功能和开销综合考虑。 下面来简单讨论一下各种实现数据完整性的方法的功能和性能开销。 触发器功能强大，既可以维护基础的数据完整性逻辑，又可以维护复杂的完整性逻辑，如多表的级联操作，但是开销较高； 约束的功能比触发器弱，但开销低； 默认和规则功能更弱，开销也更低； 数据类型提供最低级别的数据完整性功能，开销也是最低的。 在选择完整性方案时，应该遵循在完成同样任务的条件下，选择开销低的方案解决。也

35、就是说，能用约束完成的功能，就不用触发器完成；能用数据类型完成的功能，就不用规则来完成。,应 用 举 例,1使用约束 （1）用SQL语句创建cust_sample表，在其中创建四个字段，将cust_id创建为主键，并用检查约束限制cust_id。代码如下： USE student GO CREATE TABLE cust_sample (cust_id int PRIMARY KEY, cust_name char(16), cust_address char(30), cust_credit_limit money, CONSTRAINT chk_id CHECK (cust_id BETW

36、EEN 0 and 10000) GO,（2）用SQL语句将“教师”表中的“学历”字段的默认值改为“本科”。代码如下： IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=df_xl AND TYPE=D) BEGIN ALTER TABLE 教师 DROP CONSTRAINT df_xl END GO ALTER TABLE 教师 ADD CONSTRAINT df_xl DEFAULT 本科 FOR 学历 GO,2使用规则 用SQL语句创建一个xbdm_rule规则，将其绑定到“系部”表的“系部代码”字段上，用来保证输入的“系部代码”只能是

37、数字字符，最后显示规则的文本信息。代码如下： USE student GO IF EXISTS (SELECT name FROM sysobjects WHERE name=xbdm_rule AND TYPE=R) BEGIN EXEC sp_unbindrule 系部.系部代码 DROP RULE xbdm_rule END GO CREATE RULE xbdm_rule AS ch like0-90-9 GO EXEC sp_bindrule xbdm_rule,系部.系部代码 GO EXEC sp_helptext xbdm_rule GO,3使用默认 用SQL语句创建一个df_b

38、z默认对象，将其绑定到“班级”表的“备注”字段上，使默认值为“教学班”。最后查看默认对象定义的文本信息。代码如下： USE student GO IF EXISTS (SELECT name FROM sysobjects WHERE name=df_bz AND TYPE=D) BEGIN EXEC sp_unbindefault 班级.备注 DROP DEFAULT df_bz END GO CREATE DEFAULT df_bz AS 教学班 GO EXEC sp_bindefault df_bz,班级.备注 GO EXEC sp_helptext df_bz GO,8.1.5 使用触

39、发器实施数据完整性控制,1触发器概述,（1）触发器的概念,触发器：也是实施数据完整性控制的一种手段，它是一种特殊类型的存储过程，由Transact-SQL语句组成，不允许带参数，与表紧密相连，可以看作表定义的一部分。 使用：它是在用户对表中的数据进行修改、插入或删除时，由系统自动调用，而不允许由用户或程序通过名称调用。,功能：触发器是基于一个表创建的，但是可以针对多个表进行操作，因此利用触发器不但能够对数据库中的相关表实施级联操作，而且可以引用其他表中的列来完成检查工作以实现比CHECK约束更为复杂的数据完整性约束。利用触发器还可以评估数据修改前后的表状态，并根据其差异采取对策。 种类：在一个

40、表中可以存在三种不同操作（INSERT、UPDATE、DELETE）的触发器，以对相应的修改操作响应并进行处理。,8.1.5 使用触发器实施数据完整性控制,1触发器概述,（2）触发器的种类,SOL Server 2000按触发器被激活的时机可以分为两种类型：AFTER触发器和INSTEAD OF触发器。,AFTER触发器：又称为后触发器，该类触发器是在引起触发器执行的修改语句成功执行后被触发执行，如果修改语句执行失败，触发器将不会执行。此类触发器只能定义在表上，而且可以为每个触发操作(INSERT,UPDATE或DELETE)创建多个AFTER触发器。注意，该类触发器不能创建在视图上。,INS

41、TEAD OF触发器：又称为替代触发器，表示不执行引起触发器执行的修改语句，而只执行触发器本身。该类触发器既可在表上定义，也可在视图上定义。对于每个触发操作(INSERT,UPDATE和DELETE)，只能定义一个INSTEAD OF触发器。,8.1.5 使用触发器实施数据完整性控制,1触发器概述,（3）Deleted表与Inserted表,在触发器的执行过程中，SQL Server建立并管理两个临时表：Deleted表和Inserted表。这两个表包含了在激发触发器的操作中插入、修改或删除的记录。可以利用这一特性来检查某些数据修改情况，并根据修改情况决定触发器应执行的操作。这两个特殊表用户只

42、能查询，而不能直接修改。 。,在执行INSERT语句（插入操作）时被添加的记录将存储在Inserted表中。 在执行DELETE语句（删除操作）时，表中被删除的记录会发送到Deleted表。在执行UPDATE语句（修改操作）时，SQL Server先将要进行修改的记录存储到Deleted表中，然后再将修改后的记录复制到Inserted表中。,8.1.5 使用触发器实施数据完整性控制,2创建触发器,（1）使用对象资源管理器创建触发器,【例8-15】为CollegeMIS数据库的Teacher表创建一个名为Insert_DepartNo的INSERT触发器。当在Teacher表插入数据时，如果在D

43、epartment（系）表中没有的DepartNo（系号）的值，则提示用户不能插入记录，否则提示记录插入成功。,在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名称、激发时机、激活触发器的修改语句（INSERT、UPDATE或DELETE）。,操作步骤如下： 在“对象资源管理器”窗格中，展开“数据库”结点。 展开相应的数据库（选择CollegeMIS数据库）和“表”结点。 单击相应的表（选择“Teacher”表），右击“触发器”结点，在弹出的快捷菜单中选择“新建触发器”命令，打开新建触发器初始界面，如图9-6所示。, 单击“分析”按钮，然后单击“执行”按钮，完成触发器的创建。,CRE

44、ATE TRIGGER Insert_DepartNo ON dbo.Teacher FOR INSERT AS DECLARE DepartNo char(2) SELECT DepartNo=Department.DepartNo FROM Department,Inserted WHERE Department.DepartNo=Inserted.DepartNo IF DepartNo PRINT(教师信息插入成功) ELSE BEGIN PRINT(系表中没有该系的信息，插入失败) ROLLBACK TRANSACTION /*撤消事务*/ END, 输入触发器文本，本例中输入的代码

45、如下：,8.1.5 使用触发器实施数据完整性控制,2创建触发器,（2）使用Transact-SQL语句创建触发器,【格式】CREATE TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR|AFTER|INSTEAD OFINSERT,UPDATE ,DELETE NOT FOR REPLICATION AS IF UPDATE(column) AND|OR UPDATE(column),.n |IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask) comparison_operator

46、column_bitmask,.n sql_statement ,.n ,【功能】为table指定的表或view指定的视图创建一个由trigger_name指定名称的触发器。,【例8-16】为CollegeMIS数据库中的表Teacher表建立一个名为delete_Teacher的DELETE触发器。当用户删除Teacher表中的记录时，如果SelectCourse表中引用了该记录的TeaNo值，则提示用户不能删除记录，否则提示记录已删除。,USE CollegeMIS GO CREATE TRIGGER Delete_Teacher ON Teacher FOR DELETE AS IF(

47、SELECT Count(*) FROM SelectCourse INNER JOIN Deleted ON SelectCourse.TeaNo=Deleted.TeaNo)0 BEGIN PRINT 该教师在选课表中已经有授课信息，不能删除！ ROLLBACK TRANSACTION END ELSE PRINT 记录删除成功 GO,【例8-17】为CollegeMIS数据库中的表Teacher表建立一个名为Update_Teacher的Update触发器。当用户修改Teacher表的TeaID的值时，提示用户不能修改身份证号。,USE CollegeMIS GO CREATE TRIG

48、GER Update_Teacher ON Teacher FOR UPDATE AS IF UPDATE(TeaID) BEGIN PRINT 不能修改教师的身份证号 ROLLBACK TRANSACTION END GO,8.1.5 使用触发器实施数据完整性控制,3查看触发器信息,（1）使用对象资源管理器查看触发器信息,（2）使用系统存储过程查看触发器信息,可以使用系统存储过程sp_help、sp_helptext和sp_helptrigger来查看触发器信息。sp_help和sp_helptext的使用方法，在前面的各章已经多次讲解。调用sp_helptrigger的语句格式如下： EXEC sp_helptrigger table_name INSERT,UPDATE ,DELETE,例如： USE CollegeMIS GO EXEC sp_helptrigger Teacher GO,8.1.5 使用触发器实施数据完整性控制,4修改触发器,（1）使用对象资源管理器修改触发器,（2）使用Transact-SQL语句修改触发器,修改触发器的定义，可以使用Transact-SQL语句： ALTER TRIGGER,