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

《数据完整性和一致性控制约束规则触发器事务锁.ppt》由会员分享，可在线阅读，更多相关《数据完整性和一致性控制约束规则触发器事务锁.ppt（65页珍藏版）》请在三一办公上搜索。

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完整性控制方法,参照完整性的控制方法：主要有外键约束，有时利用触发器也可实现参照完整性控制。,8.1

4、.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作为表名指定的表增加一个由con

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

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

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

8、e_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表的Telephone字段创建一个惟一约束，约束名为IX_Telephon

9、e。,（2）惟一约束的创建使用Transact-SQL语句,USE CollegeMISGOALTER TABLE DepartmentADD CONSTRAINT IX_Telphone UNIQUE(Telephone)GO,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-4】为CollegeMIS数据库中的“课程表”的“学分”字段创建一个检查约束，约束名为CK_Grade。检查条件只允许为17分，不允许小于1分的学分和大于7分的学分出现。,（3）检查约束的创建使用对象资源管理器,检查约束对输入的数据的值做检查，可以限定数据输入，从而维护数据的域完整性。,操作步骤如下：（1）

10、在“对象资源管理器”窗格中，右击需要设置唯一约束的表（本例为“课程表”），在弹出的快捷菜单中选择“修改”命令，打开“表设计器”窗口。（2）在“表设计器”窗口中右击需要创建检查约束的字段（本例为“学分”字段），在弹出的快捷菜单中选择“CHECK约束”命令，如图6-11所示，打开“CHECK约束”对话框。,图6-11 选择“CHECK约束”命令,（3）在“CHECK约束”对话框中，单击“添加”按钮，然后在“(名称)”文本框中输入检查约束名称，在约束“表达式”文本框中输入约束条件，这里输入“(学分=1 AND 学分=7)”，如图6-12所示。,图6-12 设置“CHECK约束”条件,（4）单击“关闭

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

12、式或逻辑表达式。,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-5】使用Transact-SQL语句，为CollegeMIS数据库中的Student表的Birthday字段创建一个检查约束，约束名为CK_Birthday。检查条件为Birthday字段的值必须小于当前日期。,（3）检查约束的创建使用Transact-SQL语句,USE CollegeMISGOALTER TABLE StudentADD CONSTRAINT CK_Birthday CHECK(Birthday=GetDate()GO,8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-6】为Coll

13、egeMIS数据库中的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_expression FOR column_name,（4）默认约束的创建 使用Transact-SQL语句,【功能】为table_n

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

16、8.1.2 使用约束实施数据完整性控制,1约束的建立,【例8-8】为CollegeMIS数据库中的“教师”表的创建一个外键约束，约束名为FK_ Teacher _Department。约束对应的主键表为Department（引用的主键字段为系部代码），本表（“教师”表）的外键字段为系部代码。,（5）外键约束的创建 使用对象资源管理器,操作步骤如下：（1）在“对象资源管理器”窗格中，右击需要创建外键约束的表（这里为“教师”表），在弹出的快捷菜单中选择“修改”命令，打开“表设计器”窗口。（2）选择需要创建外键约束的字段（这里为“系部代码”字段），单击工具栏中的“关系”按钮，或右击该字段，在弹出的快

17、捷菜单中选择“关系”命令，打开“外键关系”对话框，如图6-14所示。,外键是用来维护表与表之间对应唯一关系的一种方法。可以利用对象资源管理器或SQL语句来创建外键约束。,图6-14“外键关系”对话框,（3）在“外键关系”对话框中，单击“添加”按钮，然后单击“表和列规范”的按钮，打开“表和列”对话框。在“主键表”下拉列表中选择“系部”表，在“外键表”的下拉列表框中选择“教师”表，分别在“主键表”和“外键表”的下面选择“系部代码”字段，如图6-15所示。,图6-15“表和列”对话框,（4）单击“确定”按钮，然后在“外键关系”对话框中进行相关设置后单击“关闭”按钮即可。,8.1.2 使用约束实施数据

18、完整性控制,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）外键约束的创建使用Transact-SQL语句,【功能】为由table_name作为表名指定的表创建一个由constraint_name作为约束名的外键约束。,8.1.2 使用

19、约束实施数据完整性控制,1约束的建立,【例8-9】为CollegeMIS数据库中的SelectCourse表的创建一个外键约束，约束名为FK_SelectCourse_Student。约束对应的主键表为Student（引用的主键字段为StuNo），本表（SelectCourse表）的外键字段为StuNo。并设置级联删除和级联更新。,（6）默认约束的创建使用Transact-SQL语句,USE CollegeMISGOALTER TABLE SelectCourse ADD CONSTRAINT FK_SelectCourse_Student FOREIGN KEY(StuNo)REFERENC

20、ES Student(StuNo)ON DELETE CASCADE ON UPDATE CASCADEGO,8.1.2 使用约束实施数据完整性控制,2约束定义的查看,对于创建好的约束，根据实际需要可以查看其定义信息。SQL Server 2005提供了多种查看约束信息的方法，经常使用的有利用对象资源管理器和系统存储过程。（1）利用对象资源管理器查看约束信息 使用对象资源管理器查看约束信息的操作步骤如下：（1）在“对象资源管理器”窗格中，右击要查看约束的表，在弹出的快捷菜单中选择“修改”命令，打开“表设计器”窗口。（2）右击该表，在弹出的快捷菜单中分别选择“关系”、“索引/键”、“CHECK约

21、束”等命令查看约束信息，如图6-16所示。,图6-16 查看约束信息菜单,（2）利用系统存储过程查看约束的定义,存储过程sp_helptext是用来查看约束的一个系统提供的存储过程，可以通过查询分析器来查看约束的名称、创建者、类型和创建时间。其语法格式为：EXEC sp_help 约束名称 如果该约束有具体的定义和文本，那么可以用sp_helptext来查看。其语法格式为：EXEC sp_helptext 约束名称【例6.9】使用系统存储过程查看student数据库中定义的入学时间（名称为ck_rxsj）的约束信息和文本信息。代码如下，结果如图6-17所示。,USE studentGOEXEC

22、 sp_help ck_rxsjGOUSE studentGOEXEC sp_helptext ck_rxsjGO,8.1.2 使用约束实施数据完整性控制,3删除约束,使用对象资源管理器删除约束非常方便，正如在建立约束时一样，只需要在“表设计器”窗口中，将如图6-2所示的“设置主键”前的复选框取消即可删除主键约束，或删除默认值以删除默认约束；如图6-8所示，单击“删除”按钮删除唯一约束；如图6-12所示，单击“删除”按钮删除检查约束；如图6-14所示，单击“删除”按钮删除外键约束。,（1）使用对象资源管理器来删除表约束,（2）使用Transact-SQL语句删除约束,【格式】ALTER TAB

23、LE table_name DROP CONSTRAINT constraint_name，n,【功能】删除由“table_name”作为表名指定的表中由“constraint_name”作为约束名的约束。可以同时删除多个约束，约束名之间用“,”隔开。,8.1.2 使用约束实施数据完整性控制,3删除约束,【例8-10】删除CollegeMIS数据库中的SelectCourse表的外键约束fk_scStuNo（该约束在第5章中建立）。,USE CollegeMISGOALTER TABLE SelectCourse DROP CONSTRAINT fk_scStuNoGO,8.1.3 使用规则实

24、施数据完整性控制,规则概述:,规则：是一种数据库对象，与CHECK约束的作用基本相同，也是用来限制输入值的取值范围，从而实施域完整性控制。使用规则：首先要创建规则，然后把规则绑定到列上，或从列上解绑和删除。它与CHECK约束有以下几点不同：,（1）CHECK约束可以在建表时由CREATE TABLE语句将其作为表的一部分进行指定，而规则需要单独创建并绑定到列上；,（2）在一个列上只能应用一个规则，但是却可以应用多个CHECK约束。,（3）一个规则只需定义一次就可以被多次应用，可以应用于多个表或多个列，还可以应用到用户定义的数据类型上。,（4）可以在查询分析器中用SQL语句完成。,8.1.3 使

25、用规则实施数据完整性控制,1使用对象资源管理器管理规则,（1）规则的创建,【例8-11】为CollegeMIS数据库创建一个名为xb_rule规则，将它绑定到“学生”表的“性别”字段，保证输入数据只能为“男”或“女”,规则作为一种数据库对象，在使用前必须被创建。创建规则的SQL命令是CREATE RULE。其语法格式如下：CREATE RULE rule_name AS condition_expression其中：rule_name是规则的名称，命名必须符合SQL Server 2005的命名规则。condition_expression是条件表达式。,CREATE RULE xb_rule

26、ASxb in(男,女),8.1.3 使用规则实施数据完整性控制,（2）规则的绑定,要使创建好的规则作用到指定的列或表等，还必须将规则绑定到列或用户定义的数据类型上才能够起作用。在查询分析器中，可以利用系统存储过程将规则绑定到字段或用户定义的数据类型上。其语法格式如下：EXECUTE sp_bindrule 规则名称,表名.字段名|自定义数据类型名,EXEC sp_bindrule xb_rule,学生.性别,将规则“xb_rule”绑定到“学生”表的“性别”列上,如果字段已经不再需要规则限制输入了，那么必须把已经绑定了的规则去掉，这就是解绑规则。在查询分析器中，同样用存储过程来完成解绑操作。

27、其语法格式如下：EXECUTE sp_unbindrule 表名.字段名|自定义数据类型名,图6-18 删除xb_rule规则,（3）解绑规则,如果规则已经没有用了，那么可以将其删除。在删除前应先对规则进行解绑，当规则已经不再作用于任何表或字段等时，则可以用DROP RULE删除一个或多个规则。其语法格式如下：DROP RULE 规则名称,n,（4）删除规则,【例6.12】从student数据库中删除xb_rule规则。代码和结果如图6-18所示。,8.1.3 使用规则实施数据完整性控制,1使用Transact-SQL语句和系统存储过程管理规则,（1）创建规则,【格式】CREATE RULE

28、rule_nameAS condition_expression,【功能】创建一个由“rule_name”指定名称的规则，规则的条件表达式由“condition_expression”指定。,（2）绑定规则,【格式】EXEC sp_bindrule rule_name tablename_columnname|UDTname,【功能】把rule_name规则绑定到tablename_columnname指定的列上或UDTname指定的用户自定义类型上。,【例8-13】为CollegeMIS数据库创建一个名为Sex_Rule的规则，规则的条件表达式为“Sex=男 OR Sex=女”。然后把该规则

29、分别绑定到Student表的Sex字段和Teacher表的Sex字段。,USE CollegeMISGOCREATE RULE Sex_Rule As Sex=男 OR Sex=女GOEXEC sp_bindrule Sex_Rule,Student.SexEXEC sp_bindrule Sex_Rule,Teacher.SexGO,8.1.3 使用规则实施数据完整性控制,1使用Transact-SQL语句和系统存储过程管理规则,（3）解绑规则,【格式】EXEC sp_unbindrule tablename_columnname|UDTname,【功能】把tablename_columnn

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

31、efault_name”指定名称的默认值，默认值的值由“default_constant”确定。default_constant是常量表达式，可以包含常量、内置函数或数学表达式。,（2）绑定默认值,【格式】EXEC sp_bindefault default_name tablename_columnname|UDTname,【功能】把default_name默认值绑定到tablename_columnname指定的列上或UDTname指定的用户自定义数据类型上。,【例8-14】为CollegeMIS数据库创建一个名为Sex_Default的默认值，值为“男”。然后把该默认值绑定到Teache

32、r表的Sex字段上。,USE CollegeMISGOCREATE Default Sex_Default As 男GOEXEC sp_bindefault Sex_Default,Teacher.SexGO,8.1.4 使用默认值实施数据完整性控制,1使用Transact-SQL语句和系统存储过程管理默认值,（3）解绑默认值,【格式】EXEC sp_unbindefault tablename_columnname|UDTname,【功能】把tablename_columnname指定的列上或UDTname指定的用户自定义数据类型上绑定的默认值去掉。,（4）删除默认值,【格式】DROP DE

33、FAULT default_name,.n,【功能】删除由default_name作为默认值名指定的默认值。可删除多个默认值，默认值之间用“,”分开。,【例如】EXEC sp_unbindefault Teacher.Sex,【例如】DROP DEFAULT Sex_Default,数据完整性强制选择方法,SQL Server 2005提供了许多实现数据完整性的方法。除了本章介绍的约束、默认和规则外，还有前面章节介绍的数据类型和后面需要学习的触发器等。对于某一问题可能存在多种解决办法，应该根据系统的实际要求，从数据完整性方法实现的功能和开销综合考虑。下面来简单讨论一下各种实现数据完整性的方法的

34、功能和性能开销。触发器功能强大，既可以维护基础的数据完整性逻辑，又可以维护复杂的完整性逻辑，如多表的级联操作，但是开销较高；约束的功能比触发器弱，但开销低；默认和规则功能更弱，开销也更低；数据类型提供最低级别的数据完整性功能，开销也是最低的。在选择完整性方案时，应该遵循在完成同样任务的条件下，选择开销低的方案解决。也就是说，能用约束完成的功能，就不用触发器完成；能用数据类型完成的功能，就不用规则来完成。,应 用 举 例,1使用约束（1）用SQL语句创建cust_sample表，在其中创建四个字段，将cust_id创建为主键，并用检查约束限制cust_id。代码如下：USE studentGOC

35、REATE 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 BETWEEN 0 and 10000)GO,（2）用SQL语句将“教师”表中的“学历”字段的默认值改为“本科”。代码如下：IF EXISTS(SELECT NAME FROM sysobjectsWHERE NAME=df_xl AND TYPE=D)BEGIN ALTER TABLE 教师 DROP CO

36、NSTRAINT df_xl ENDGOALTER TABLE 教师ADD CONSTRAINT df_xlDEFAULT 本科 FOR 学历GO,2使用规则 用SQL语句创建一个xbdm_rule规则，将其绑定到“系部”表的“系部代码”字段上，用来保证输入的“系部代码”只能是数字字符，最后显示规则的文本信息。代码如下：USE studentGOIF EXISTS(SELECT name FROM sysobjects WHERE name=xbdm_rule AND TYPE=R)BEGIN EXEC sp_unbindrule 系部.系部代码 DROP RULE xbdm_rule END

37、GOCREATE RULE xbdm_ruleASch like0-90-9GOEXEC sp_bindrule xbdm_rule,系部.系部代码GOEXEC sp_helptext xbdm_ruleGO,3使用默认 用SQL语句创建一个df_bz默认对象，将其绑定到“班级”表的“备注”字段上，使默认值为“教学班”。最后查看默认对象定义的文本信息。代码如下：USE studentGOIF EXISTS(SELECT name FROM sysobjects WHERE name=df_bz AND TYPE=D)BEGIN EXEC sp_unbindefault 班级.备注 DROP D

38、EFAULT df_bz ENDGOCREATE DEFAULT df_bzAS教学班GOEXEC sp_bindefault df_bz,班级.备注GOEXEC sp_helptext df_bzGO,8.1.5 使用触发器实施数据完整性控制,1触发器概述,（1）触发器的概念,触发器：也是实施数据完整性控制的一种手段，它是一种特殊类型的存储过程，由Transact-SQL语句组成，不允许带参数，与表紧密相连，可以看作表定义的一部分。使用：它是在用户对表中的数据进行修改、插入或删除时，由系统自动调用，而不允许由用户或程序通过名称调用。,功能：触发器是基于一个表创建的，但是可以针对多个表进行操作

39、，因此利用触发器不但能够对数据库中的相关表实施级联操作，而且可以引用其他表中的列来完成检查工作以实现比CHECK约束更为复杂的数据完整性约束。利用触发器还可以评估数据修改前后的表状态，并根据其差异采取对策。种类：在一个表中可以存在三种不同操作（INSERT、UPDATE、DELETE）的触发器，以对相应的修改操作响应并进行处理。,8.1.5 使用触发器实施数据完整性控制,1触发器概述,（2）触发器的种类,SOL Server 2000按触发器被激活的时机可以分为两种类型：AFTER触发器和INSTEAD OF触发器。,AFTER触发器：又称为后触发器，该类触发器是在引起触发器执行的修改语句成功

40、执行后被触发执行，如果修改语句执行失败，触发器将不会执行。此类触发器只能定义在表上，而且可以为每个触发操作(INSERT,UPDATE或DELETE)创建多个AFTER触发器。注意，该类触发器不能创建在视图上。,INSTEAD OF触发器：又称为替代触发器，表示不执行引起触发器执行的修改语句，而只执行触发器本身。该类触发器既可在表上定义，也可在视图上定义。对于每个触发操作(INSERT,UPDATE和DELETE)，只能定义一个INSTEAD OF触发器。,8.1.5 使用触发器实施数据完整性控制,1触发器概述,（3）Deleted表与Inserted表,在触发器的执行过程中，SQL Serv

41、er建立并管理两个临时表：Deleted表和Inserted表。这两个表包含了在激发触发器的操作中插入、修改或删除的记录。可以利用这一特性来检查某些数据修改情况，并根据修改情况决定触发器应执行的操作。这两个特殊表用户只能查询，而不能直接修改。,在执行INSERT语句（插入操作）时被添加的记录将存储在Inserted表中。在执行DELETE语句（删除操作）时，表中被删除的记录会发送到Deleted表。在执行UPDATE语句（修改操作）时，SQL Server先将要进行修改的记录存储到Deleted表中，然后再将修改后的记录复制到Inserted表中。,8.1.5 使用触发器实施数据完整性控制,2

42、创建触发器,（1）使用对象资源管理器创建触发器,【例8-15】为CollegeMIS数据库的Teacher表创建一个名为Insert_DepartNo的INSERT触发器。当在Teacher表插入数据时，如果在Department（系）表中没有的DepartNo（系号）的值，则提示用户不能插入记录，否则提示记录插入成功。,在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名称、激发时机、激活触发器的修改语句（INSERT、UPDATE或DELETE）。,操作步骤如下：在“对象资源管理器”窗格中，展开“数据库”结点。展开相应的数据库（选择CollegeMIS数据库）和“表”结点。单击相应

43、的表（选择“Teacher”表），右击“触发器”结点，在弹出的快捷菜单中选择“新建触发器”命令，打开新建触发器初始界面，如图9-6所示。,单击“分析”按钮，然后单击“执行”按钮，完成触发器的创建。,CREATE TRIGGER Insert_DepartNo ON dbo.Teacher FOR INSERTAS DECLARE DepartNo char(2)SELECT DepartNo=Department.DepartNo FROM Department,Inserted WHERE Department.DepartNo=Inserted.DepartNo IF DepartNo P

44、RINT(教师信息插入成功)ELSE BEGIN PRINT(系表中没有该系的信息，插入失败)ROLLBACK TRANSACTION/*撤消事务*/END,输入触发器文本，本例中输入的代码如下：,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 ASIF UPDATE(column)AND|OR

45、 UPDATE(column),.n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask,.n sql_statement,.n,【功能】为table指定的表或view指定的视图创建一个由trigger_name指定名称的触发器。,【例8-16】为CollegeMIS数据库中的表Teacher表建立一个名为delete_Teacher的DELETE触发器。当用户删除Teacher表中的记录时，如果SelectCourse表中引用了该记录的TeaNo值，则提示用户不能删除记录，

46、否则提示记录已删除。,USE CollegeMISGOCREATE TRIGGER Delete_Teacher ON Teacher FOR DELETE AS IF(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_Te

47、acher的Update触发器。当用户修改Teacher表的TeaID的值时，提示用户不能修改身份证号。,USE CollegeMISGOCREATE TRIGGER 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_helptr

48、igger来查看触发器信息。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,命令中有许多选

49、项，含义与CREATE TRIGGER含义相同。,语法：ALTER TRIGGER trigger_nameON(table|view)WITH ENCRYPTION(FOR|AFTER|INSTEAD OF)INSERT,DELETE,UPDATE NOT FOR REPLICATION AS sql_statement,n|(FOR|AFTER|INSTEAD OF)INSERT,UPDATE NOT FOR REPLICATION AS IF UPDATE(column)AND|OR UPDATE(column),n|IF(COLUMNS_UPDTED()bitwise_operator

50、 updated_bitmask)(comparison_operator)column_bitmask,n sql_statement,n 其中的参数与创建触发器语句中的参数相同。,8.1.5 使用触发器实施数据完整性控制,5禁止或启用触发器,【格式】ALTER TABLE ENABLE|DISABLE trigger_name,【功能】对由“trigger_name”作为名称指定的触发器禁止或启用。,8.1.5 使用触发器实施数据完整性控制,6删除触发器,（1）使用对象资源管理器删除触发器信息,（2）使用Transact-SQL语句删除触发器,【格式】DROP TRIGGER trigge