第5章 SQL的高级应用 教学目标：通过本章学习，明确索引与视图的作用，掌握索引及视图的使用方法；掌握存储过程与触发器的创建及使用方法。

Presentation on theme: "第5章 SQL的高级应用 教学目标：通过本章学习，明确索引与视图的作用，掌握索引及视图的使用方法；掌握存储过程与触发器的创建及使用方法。"— Presentation transcript:

1 第5章 SQL的高级应用 教学目标：通过本章学习，明确索引与视图的作用，掌握索引及视图的使用方法；掌握存储过程与触发器的创建及使用方法。

2 第5章 SQL的高级应用 5.1 索引 5.2 视图 5.3 存储过程 5.4 触发器 5.5 本章小结

3 1索引 2视图 3存储过程 4触发器 5本章小结 5.1 索引 1 索引的定义 2 索引的类型 3 创建索引 4 删除索引

4 1. 索引的定义 1索引 2视图 3存储过程 4触发器 5本章小结
索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 1. 索引的定义 SQL Server的索引是对数据库表中一个或多个列(如Student表中学生的姓名(Sname)列)的值进行排序的结构。 1) 索引可以提高数据的访问速度 只要为适当的字段建立索引，就能大幅度提高下 列操作的速度。 (1) 查询操作中WHERE子句的数据提取。 (2) 查询操作中ORDER BY子句的数据排序。 (3) GROUP BY子句的数据分组。 (4) 更新和删除数据记录。 2）索引可以确保数据的唯一性

5 2. 索引的类型 1索引 2视图 3存储过程 4触发器 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 2. 索引的类型 (1) 按存储结构可以将索引分为聚集索引(Clustered Index，也可以称为聚簇索引)和非聚集索引(Non Clustered Index，也可以称为非聚簇索引)。 (2) 按数据的唯一性可以将索引分为唯一性索引(Unique Index)和非唯一性索引(Non Unique Index)。

6 2. 索引的类型 1索引 2视图 3存储过程 4触发器 聚集索引： 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 2. 索引的类型 聚集索引： 在聚集索引中，行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序。因为行是经过排序的，所以每个表只能有一个聚集索引。 因为聚集索引的顺序与数据行存放的物理顺序相同，所以聚集索引最适合范围搜索。因为在找到一个范围内开始的行后可以很快地取出后面的行。 如果表中没有创建其他的聚集索引，则在表的主键列上自动创建聚集索引。

7 2. 索引的类型 1索引 2视图 3存储过程 非聚集索引： 4触发器 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 2. 索引的类型 非聚集索引： 非聚集索引并不在物理上排列数据，即索引中的逻辑顺序并不等同于表中行的物理顺序。索引仅仅记录指向表中行位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据。非聚集索引作为与表分离的对象存在，所以，可以为表中每个常用于查询的列定义非聚集索引。 非聚集索引的特点是它很适合于那种直接匹配单个条件的查询，而不太适合于返回大量结果的查询。例如Student表的Sname列上就很适合创建非聚集索引。 为一个表创建索引默认都是非聚集索引，在一列上设置唯一性约束也自动在该列上创建非聚集索引。

8 2. 索引的类型 1索引 2视图 3存储过程 唯一性索引： 4触发器 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 2. 索引的类型 唯一性索引： 唯一性索引能够保证在创建索引的列或多列的组合上不包括重复的数据，聚集索引和非聚集索引都可以是唯一性索引。 在创建主键约束(PRIMARY)和唯一性约束(UNIQUE)的列上，SQL Server 2000会自动创建唯一性索引。 唯一性索引和非唯一性索引都能提高数据的查询速度，但是唯一性索引更能进一步确保数据的唯一性。

9 1索引 索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 3. 创建索引 在实际创建索引之前，有如下几个注意事项。 (1) 当给表创建PRIMARY或UNIQUE约束时，SQL Server会自动创建索引。 (2) 索引的名称必须符合SQL Server的命名规则，且必须是表中唯一的。 (3) 可以在创建表时创建索引，或是给现存表创建索引。 (4) 只有表的所有者才能给表创建索引。 (5) 每个表最多可同时拥有1个聚集索引和249个非聚集索引。 (6) 最多可以给16个字段(来自同一个表)的组合创建一个多列索引，而这些字段的总长度不能超过900个字节。 (7) 在创建一个聚集索引时，所有现存的非聚集索引会重新创建。因此要先创建聚集索引，再创建各个非聚集索引。

10 1索引 索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 3. 创建索引 另外，在创建聚集索引时还要考虑数据库剩余空间的问题。在创建聚集索引时所需的可用空间应是数据库表中数据量的120%，这是因为在创建聚集索引时表中的数据将被复制以便进行排序，排序完成后，再将旧的未加索引的表删除，所以数据库必须有足够用来复制数据的空间。 创建唯一性索引时，应保证创建索引的列不包括重复的数据，并且没有两个或两个以上的空值(NULL)。因为创建索引时将两个空值也视为重复的数据，如果有这种数据，必须先将其删除，否则索引不能被成功创建。

11 3. 创建索引 1索引 2视图 3存储过程 使用企业管理器创建索引： 4触发器 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 3. 创建索引 使用企业管理器创建索引： (1) 创建表的同时创建索引。在表结构定义窗口中完成各个字段特性定义后，右击，在弹出的快捷菜单中选择【属性】命令，也可直接单击工具栏中的【表和索引属性】按钮，打开【属性】对话框。给现有的表创建索引，先打开表的结构定义窗口，然后右击，在弹出的快捷菜单中选择【属性】命令，也可直接单击工具栏中的【表和索引属性】按钮，打开【属性】对话框。 (2) 当【属性】对话框打开后，选择【索引/键】选项卡，切换到该页面，如图5.1所示。 (3) 单击【新建】按钮，表示要创建新索引。 (4) 在【索引名】文本框中输入索引的名称。

12 3. 创建索引 1索引 2视图 3存储过程 4触发器 使用企业管理器创建索引： 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 3. 创建索引 使用企业管理器创建索引： (5) 在【列名】列表框中，选取索引的键列。如果给某一字段创建索引，在【列名】列表框中的第一列选取该字段；如果给多个字段创建索引，依次在【列名】列表框中的各列选取各个字段。 (6) 从【索引文件组】下拉列表框中选取一文件组，以便决定将索引放在数据库的哪一个文件组中。 (7) 如果创建的是唯一性索引，选中【创建UNIQUE】复选框，并且选中【索引】单选按钮。 如果希望该唯一性索引忽略重复值，选中【忽略重复值】复选框。

13 3. 创建索引 1索引 2视图 3存储过程 4触发器 使用企业管理器创建索引： 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 3. 创建索引 使用企业管理器创建索引： (8) 如果要创建一个聚集索引，选中创建为【CLUSTERED】复选框；反之，如果创建的是非聚集索引，不要选中【创建为CLUSTERED】复选框。 (9) 如果在索引更新时重新计算统计信息，不要选中【不自动重新计算统计】复选框；反之，选中。 (10) 反复执行(3)～(9)的操作，直到给表定义完全部索引，然后单击【关闭】按钮。 (11) 用鼠标单击结构定义窗口工具栏中的【保存】按钮，此时SQL Server会开始创建各个索引。 (12) 关闭结构定义窗口。

14 3. 创建索引 1索引 2视图 3存储过程 4触发器 用Transact-SQL语句创建索引： 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 3. 创建索引 用Transact-SQL语句创建索引： 可以用CREATE INDEX语句在一个已经存在的表上创建索引，CREATE INDEX语句的格式如下： CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON {table|view} (column[ASC|DESC][,...n]) [ON filegroup]

15 3. 创建索引 1索引 2视图 3存储过程 用Transact-SQL语句创建索引： 4触发器 5本章小结 参数说明:
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 3. 创建索引 用Transact-SQL语句创建索引： 参数说明: UNIQUE、CLUSTERED和NONCLUSTERED选项：指定所创建索引的类型，分别为唯一性索引、聚集索引和非聚集索引。省略UNIQUE时，SQL Server所创建的是非唯一性索引；省略LUSTERED|NONCLUSTERED选项时，SQL Server所创建的是非聚集索引。index_name：说明所创建的索引名称，应遵守SQL Server标识符命名规则。 table|view：指定为其创建索引的表或视图。 Column：指定索引的键列。不能对text、ntext、image数据类型列创建索引。 ASC|DESC：指定索引列的排序方式是升序还是降序，默认为升序(ASC)。 ON子句：指定保存索引文件的数据库文件组名称。

16 3. 创建索引 1索引 2视图 3存储过程 用Transact-SQL语句创建索引： 4触发器 5本章小结
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 3. 创建索引 用Transact-SQL语句创建索引： 【例5-1】 为Student表Sno列创建一个唯一性的聚集索引，索引排列顺序为降序。 CREATE UNIQUE CLUSTERED INDEX Sno_student ON (Sno DESC)  注：在执行此命令前先删除原来该表的主关键字属性。

17 1索引 索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 4. 删除索引 在表【属性】对话框的【索引/键】选项卡中，从【选定的索引】下拉列表框中选取要删除的索引，然后单击【删除】按钮。 删除索引的另一种方法是，在企业管理器的右侧窗口中，右击要删除索引的表，从快捷菜单中选取【所有任务】命令，再从子菜单中选取【管理索引】命令，如图5.2所示，此时将打开【管理索引】对话框，如图5.3所示。在该对话框的【现有索引】列表中选中要删除的索引，单击对话框下部的【删除】按钮，在出现的确认对话框中单击【是】按钮即可。 

18 4. 删除索引 1索引 2视图 3存储过程 4触发器 5本章小结 使用DROP INDEX语句可删除索引，语句格式为：
索引的定义 索引的类型 创建索引 删除索引 2视图 3存储过程 4触发器 5本章小结 4. 删除索引 使用DROP INDEX语句可删除索引，语句格式为： DROP INDEX <表名.索引名> 以下语句删除了student表上的Sno_student索引： DROP INDEX student.Sno_student 也可以用一条DROP INDEX语句删除多个索引，索引名 之间要用逗号隔开。   

19 1索引 2视图 3存储过程 4触发器 5本章小结 5.2 视图 1 视图概述 2 视图的创建 3 视图的使用 4 视图的修改 5 视图的删除

20 1. 视图概述 1索引 2视图 3存储过程 视图的定义： 4触发器 5本章小结
视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 1. 视图概述 视图的定义： 视图是从一个或者多个基本表(或已定义的视图)中导出的虚表。视图常用于集中、简化和定制显示数据库中的信息。视图是一个过滤器，对于一个或多个基表中的数据进行筛选、引用。

21 1. 视图概述 1索引 2视图 视图的 优点： 3存储过程 4触发器 5本章小结 简化数据的操作 视图可以简化用户操作数据的方式。
视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 1. 视图概述 视图的 优点： 简化数据的操作 视图可以简化用户操作数据的方式。 2) 自定义数据 视图允许用户以不同的方式查看数据，即使当用户同时使用相同的数据时也是如此。 3) 将数据集中显示 视图让用户能够着重于所感兴趣的特定数据和所负责的特定任务。 4) 提供重构数据库的逻辑独立性 数据的物理独立性是指用户和用户程序不依赖于数据库的物理结构。

22 2. 视图的创建 1索引 2视图 使用企业管理器创建视图： 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 2. 视图的创建 使用企业管理器创建视图： (1) 打开企业管理器，展开要使用的数据库(如Grademanager)。 (2) 右击该数据库中的【视图】图标，在弹出的菜单中选择【新建视图】命令，打开如图5.4所示的窗口，右击工具栏上的图标窗格打开【添加表】对话框，如图5.5所示。

23 2. 视图的创建 1索引 2视图 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 2. 视图的创建 (3)基表或视图选择完成之后，表Student的结构出现在视图创建/修改窗口的数据表显示区。在表中选择需要在视图中显示的列，此时在窗口下边的视图定义列显示表格中和SQL语句区中也会相应地出现所选择的列和SQL语句，若需加入限制条件、函数或计算列，可以手动在SQL语句区输入，如图5.6所示。 (4)单击工具栏按钮运行所定义的视图，在视图执行结果显示区显示出这个视图的查询结果，如图5.6下部分所示。 用户可以发现当定义了WHERE子句时，视图执行之后，在基表相应列的右边会出现一个图标，并且在视图定义列的表格中出现相应的列名与准则，即视图的查询条件。

24 2. 视图的创建 1索引 2视图 使用Transact-SQL命令创建视图： 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 2. 视图的创建 使用Transact-SQL命令创建视图： 使用CREATE VIEW命令创建视图，语法格式为： CREATE VIEW view_name [(Column [,…n])] [WITH ENCRYPTION] AS Select_statement [WITH CHECK OPTION]

25 2. 视图的创建 1索引 2视图 3存储过程 4触发器 5本章小结 命令中的参数含义如下:
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 2. 视图的创建 命令中的参数含义如下: View_name：定义视图名，其命名规则与标识符的相同，并且在一个数据库中要保证是唯一的，该参数不能省略。 Column：声明视图中使用的列名。只有当视图中的某列来自于算术表达式、内置函数或常量，或多个列有相同的名称，或给某列一个不同于基表中的列名时，才有必要使用此项。如果省略这个可选项，就使用基表的列名作为视图的列名，也可以在Select_statement语句中指定列名。 WITH ENCRYPTION：给系统表syscomments中视图定义的SELECT命令加密。这个选项可用于提高SQL Server系统的安全性。 AS：说明视图要完成的操作。

26 2. 视图的创建 1索引 2视图 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 2. 视图的创建 Select_statement：定义视图的SELECT命令，可以使用多个表及其他视图，也可以是由UNION关键字连接起来的多个SELECT命令，可以定义查询条件(WHERE子句)，还可以使用函数。 注意：视图中的SELECT命令不能包括INTO、ORDER BY等子句。临时表也不能在查询中引用。 WITH CHECK OPTION：强制所有通过视图修改的数据满足Select_statement语句中指定的选择条件。 视图创建成功后，可以在企业管理器的视图窗口中看到新定义的视图名称。视图可以由一个或多个表或视图来定义。

27 2. 视图的创建 1索引 2视图 3存储过程 4触发器 5本章小结 视图概述 视图的创建 视图的使用 视图的修改
视图的删除 3存储过程 4触发器 5本章小结 2. 视图的创建 【例5-2】 带有条件的视图定义。定义视图v_student，查询所有选修数据库课程的学生的学号(SNO)、姓名(SNAME)、课程名称(CNAME)和成绩(DEGREE)。 该视图的定义涉及了STUDENT表、COURSE表和SC表。 CREATE VIEW v_student AS SELECT SNO,SNAME,CNAME,DEGREE FROM STUDENT A,COURSE B,SC C WHERE A.SNO=C.SNO AND B.CNO=C.CNO AND CNAME='数据库'

28 2. 视图的创建 1索引 2视图 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 2. 视图的创建 视图定义后，可以像基本表一样进行查询。例如，若要查询以上定义的视图v_student，可以使用如下命令： SELECT * FROM v_student 在安装系统和创建数据库之后，只有系统管理员sa和数据库所有者DBO具有创建视图的权限，此后他们可以使用GRANT CREATE VIEW命令将这个权限授予其他用户。此外，视图创建者必须具有在视图查询中包括的每一列的访问权。

29 3. 视图的使用 1索引 2视图 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 3. 视图的使用 视图的使用主要包括视图的检索、通过视图对基表进行插入、修改、删除操作。视图的检索几乎没有什么限制，但是对通过视图实现表的插入、修改、删除操作则有一定的限制条件。 1．使用视图进行数据检索 视图的查询总是转换为对它所依赖的基本表的等价查询。利用Transact-SQL的SELECT命令和企业管理器都可以对视图进行查询，其使用方法与基本表的查询完全一样。 2．通过视图修改数据 视图也可以使用INSERT命令插入行，当执行INSERT命令时，实际上是向视图所引用的基本表插入了行。视图中的INSERT命令与在基本表中使用INSERT命令的格式完全一样。

30 3. 视图的使用 1索引 2视图 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 3. 视图的使用 【例5-3】 利用视图向表student中插入一条数据。V1_student是前面创建的视图，脚本如下。 CREATE VIEW V1_student AS SELECT SNO,SNAME,SADDRESS FROM STUDENT 执行下面脚本： INSERT INTO V1_student VALUES(' '、'王小龙'、'山东省青岛市') 查看结果： SELECT * FROM student WHERE SNAME='王小龙'

31 3. 视图的使用 1索引 2视图 3存储过程 4触发器 5本章小结 执行结果可以看出，数据在基本表中已经正确插入。 视图概述 视图的创建
视图的修改 视图的删除 3存储过程 4触发器 5本章小结 3. 视图的使用 执行结果可以看出，数据在基本表中已经正确插入。

32 3. 视图的使用 1索引 2视图 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 3. 视图的使用 如果视图中有下面所述属性，则插入、更新或删除基表将失败。 (1) 视图定义中的FROM子句包含两个或多个表，且SELECT选择列表达式中的列包含来自多个表的列。 (2) 视图的列是从集合函数派生的。 (3) 视图中的SELECT语句包含GROUP BY子句或DISTINCT选项。 (4) 视图的列是从常量或表达式派生的。

33 3. 视图的使用 1索引 2视图 3存储过程 4触发器 5本章小结 同样，利用视图可以更新行，改变所引用的基表中一行或多行或列的值。
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 3. 视图的使用 同样，利用视图可以更新行，改变所引用的基表中一行或多行或列的值。 【例5-4】将例5-3中插入的数据删除。 DELETE FROM V1_student WHERE SNAME='王小龙'  这个例子执行后会将基本表student中的所有SNAME为“王小龙”的行删除。

34 4. 视图的修改 1索引 2视图 使用企业管理器修改视图： 3存储过程 4触发器 5本章小结 (1) 展开服务器，展开数据库。
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 4. 视图的修改 使用企业管理器修改视图： (1) 展开服务器，展开数据库。 (2) 单击【视图】图标，在窗口右侧显示出当前数据库中的所有视图。 (3) 右击要修改的视图名，在弹出的快捷菜单中选择【设计视图】命令，便可进入视图设计窗口，用户可以在这个窗口中对视图进行修改。 另外可以利用视图属性对话框修改视图，双击要修改的视图名，弹出视图属性对话框，在这个对话框中显示出定义视图的Transact-SQL命令，用户可以直接在这个对话框中进行修改。

35 4. 视图的修改 1索引 2视图 使用命令修改视图： 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 4. 视图的修改 使用命令修改视图： 使用ALTER VIEW命令修改视图，语法格式为： ALTER VIEW view_name [(Column[,…n])] [WITH ENCRYPTION] AS select-statement [WITH CHECK OPTION] 命令行中的参数与CREATE VIEW命令中的参数的含义相同。

36 4. 视图的修改 1索引 2视图 使用命 令修改视图： 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 4. 视图的修改 使用命 令修改视图： 如果在创建视图时使用了WITH ENCRYPTION选项和WITH CHECK OPTION选项，则在使用ALTER VIEW命令时，也必须包括这些选项。 【例5-5】修改例5-3中的视图V1_student。 ALTER VIEW V1_student AS SELECT SNO,SNAME FROM STUDNET

37 5. 视图的删除 1索引 2视图 使用企业管理器删除视图： 3存储过程 4触发器 5本章小结
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 5. 视图的删除 使用企业管理器删除视图： 操作步骤如下。 (1) 在当前数据库中单击【视图】图标。 (2) 在右边视图列表窗口内单击需删除的视图(如V1_student)。 (3) 单击工具栏上的【删除】图标，弹出【除去对象】对话框，如图5.8所示。

38 5. 视图的删除 1索引 2视图 使用命令删除视图： 3存储过程 4触发器 5本章小结 语法格式为：
视图概述 视图的创建 视图的使用 视图的修改 视图的删除 3存储过程 4触发器 5本章小结 5. 视图的删除 使用命令删除视图： 语法格式为： DROP VIEW {view} [,…n] DROP VIEW命令可以删除多个视图，各视图名之间用逗号分隔。 【例5-6】 删除视图v_student。 DROP VIEW v_student

39 5.3 存储过程 1索引 2视图 3存储过程 4触发器 5本章小结 1 存储过程概述 2 创建存储过程 3 修改存储过程 4 执行存储过程
5 删除存储过程 6 在存储过程中使用参数

40 1. 存储过程概述 1索引 2视图 3存储过程 什么是存储过程： 4触发器 5本章小结
创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 1. 存储过程概述 什么是存储过程： 在开发SQL Server应用程序时，为了修改和扩充方便，经常会将负责不同功能的语句集中起来并且按照用途分别独立存储，以便能够反复调用，这些独立存储且拥有不同功能的语句即是“存储过程(Stored Procedure)”。

41 1. 存储过程概述 1索引 2视图 3存储过程 存储过程的优点： 4触发器 5本章小结 存储过程有以下几个优点。 执行速度快。
创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 1. 存储过程概述 存储过程的优点： 存储过程有以下几个优点。 执行速度快。 (2) 模块化的程序设计。 (3) 减少网络通信量。 (4) 保证系统的安全性。

42 1. 存储过程概述 1索引 2视图 3存储过程 存储过程的分类： 4触发器 5本章小结 本地存储过程 临时存储过程 扩展存储过程
创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 1. 存储过程概述 存储过程的分类： 系统存储过程 本地存储过程 临时存储过程 扩展存储过程 远程存储过程

43 1. 存储过程概述 1索引 2视图 3存储过程 4触发器 5本章小结 1)系统存储过程(System Stored Procedures)
创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 1. 存储过程概述 1)系统存储过程(System Stored Procedures) 系统存储过程是SQL Server内置的存储过程，可以作为命令直接执行；还可以作为模板存储过程，指导用户如何编写有效的存储过程。系统存储过程存储在master数据库中，且名称均以sp_开头。系统存储过程可以在任意一个数据库中执行。

44 1. 存储过程概述 1索引 2视图 3存储过程 4触发器 5本章小结 2) 本地存储过程(Local Stored Procedure)
创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 1. 存储过程概述 2) 本地存储过程(Local Stored Procedure) 本地存储过程也叫做用户存储过程，是用户自行创建并存储在用户数据库中的存储过程，其名称前面一般不加sp_前缀。本地存储过程主要在应用程序中使用，以完成特定的任务。

45 1. 存储过程概述 1索引 2视图 3存储过程 4触发器 5本章小结
创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 1. 存储过程概述 3) 临时存储过程(Temporary Stored Procedure) 临时存储过程属于用户存储过程。如果用户在创建存储过程时，以井字号“#”作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb中的本地临时存储过程。 本地临时存储过程只有创建并连接它的用户才能够执行，而且一旦这个用户断开与SQL Server的连接，本地临时存储过程会自动删除。 如果用户所创建的存储过程名称以两个井字号“##”开头，则该存储过程将成为一个存放在tempdb中的全局临时存储过程。全局临时存储过程一旦被创建，以后连接到SQL Server的任何用户都能够执行它，而且不需要特定的权限。

46 1. 存储过程概述 1索引 2视图 3存储过程 4触发器 5本章小结
创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 1. 存储过程概述 4) 扩展存储过程(Extended Stored Procedure) 扩展存储过程是用户利用外部程序语言(如C语言)编写的存储过程。扩展存储过程 的名称通常以xp_开头，是以动态链接库(DLL)的形式存在的，能让SQL Server动态装载和执行。 5) 远程存储过程(Remote Stored Procedure) 远程存储过程是指从远程服务器上调用的存储过程，或者是从连接到另外一个服务器上的客户机上调用的存储过程，是非本地服务器上的存储过程。

47 2. 创建存储过程 1索引 2视图 3存储过程 使用企业管理器创建存储过程： 4触发器 5本章小结 利用企业管理器创建存储过程的步骤如下:
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 2. 创建存储过程 使用企业管理器创建存储过程： 利用企业管理器创建存储过程的步骤如下: (1) 展开要在其中创建存储过程的数据库。 (2) 右击【存储过程】对象，在快捷菜单中选择【新建存储过程】命令，打开【新建存储过程】对话框，如图5.9所示。 (3) 在【存储过程属性-新建存储过程】对话框的文本框中书写存储过程定义。【检查语法】按钮用于检查存储过程语法的正确性。定义完存储过程后，单击【确定】按钮保存存储过程。

48 2. 创建存储过程 1索引 2视图 3存储过程 使用命令创建存储过程： 4触发器 5本章小结 语法格式为：
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 2. 创建存储过程 使用命令创建存储过程： 语法格式为： CREATE PROC[EDURE] procedure_name [;number] data_type } [VARYING] [=default] [OUTPUT] ] [,...n ] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS sql_statement [,...n]

49 2. 创建存储过程 1索引 2视图 3存储过程 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 2. 创建存储过程 命令中的参数含义如下: procedure_name：存储过程名，其命名规则遵守SQL Server 标识符的命名规则，最长为128个字符。 [;number]：可选整数，用于将同名的存储过程分成组，以便用单独的一条DROP VARYING：只用于光标参数。 default：参数的默认值，可以为NULL，也可以包含通配符(％或_)。

50 2. 创建存储过程 1索引 2视图 3存储过程 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 2. 创建存储过程 OUTPUT：表明参数为一个输出参数，当使用EXEC[UTE]执行时作为返回值，不能是TEXT类型。 WITH RECOMPILE：每次执行存储过程时重新编译，产生新的执行计划，不能与FOR REPLICATION同时使用。 WITH ENCRYPTION：对syscomments表中的存储过程文本进行加密，使用户不能利用sp_helptext查看存储过程内容。 FOR REPLICATION：表示该存储过程不能在订阅器上执行，只能在复制期间执行。 sql_statement：作为存储过程主体部分的Transact-SQL内容。

51 2. 创建存储过程 1索引 2视图 3存储过程 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 2. 创建存储过程 存储过程只能在当前数据库中创建，且只有 System Administrators、db_owner或db_ddladmin角色中的成员才可以创建。 在CREATE PROCEDURE语句中，可以包括任意数量的Transact-SQL语句，但是不能使用CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER和CREATE VIEW这些创建对象的语句。 【例5-7】 创建一个简单的存储过程。 CREATE PROCEDURE PROC_S AS SELECT * FROM STUDENT WHERE SSEX='女'

52 2. 创建存储过程 1索引 2视图 3存储过程 4触发器 5本章小结 说明：建议使用下面的方式创建存储过程。
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 2. 创建存储过程 说明：建议使用下面的方式创建存储过程。 IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'PROC_S' AND TYPE = 'P') DROP PROCEDURE PROC_S CREATE PROCEDURE PROC_S AS SELECT * FROM STUDENT WHERE SSEX='女' 这个例子首先检查在sysobjects表中是否存在名字相同的存储过程的记录，如果有，则先删除原有的存储过程，再创建与之同名的存储过程。

53 2. 创建存储过程 1索引 2视图 3存储过程 4触发器 5本章小结 【例5-8】 通过多表连接查询，创建较复杂的存储过程。
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 2. 创建存储过程 【例5-8】 通过多表连接查询，创建较复杂的存储过程。 CREATE PROCEDURE PROC_SC AS SELECT A.SNO,SNAME,B.CNAME,C.DEGREE FROM STUDENT A,COURSE B,SC C WHERE A.SNO=C.SNO AND B.CNO=C.CNO

54 3.修改存储过程 1索引 2视图 3存储过程 使用企业管理器进行修改： 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 3.修改存储过程 使用企业管理器进行修改： 要修改存储过程，只需在相应数据库的存储过程对象项中找到要修改的存储过程，双击存储过程打开存储过程属性窗口，在窗口中直接进行修改，完成后单击【确定】按钮。

55 3.修改存储过程 1索引 2视图 3存储过程 使用命令进行修改： 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 3.修改存储过程 使用命令进行修改： 使用ALTER PROCEDURE命令修改存储过程，语法格式为： ALTER PROC [EDURE] procedure_name [;number] data_type } [VARYING] [=default] [OUTPUT] ] [,...n] [WITH {RECOMPILE|ENCRYPTION |RECOMPILE,ENCRYPTION } ] [FOR REPLICATION] AS sql_statement[,...n]

56 3.修改存储过程 1索引 2视图 3存储过程 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 3.修改存储过程 说明：ALTER PROCEDURE与CREATE PROCEDURE很相似。在CREATE PROCEDURE命令中使用的选项也必须在ALTER ROCEDURE中使用。ALTER ROCEDURE只能修改一个存储过程。如果该存储过程调用了其他存储过程，则不影响被调用的存储过程。只有具有db_owner和db_ddladmin权限的成员才被允许执行ALTER ROCEDURE，执行ALTER ROCEDURE语句的权限不能授予其他用户。

57 4.执行存储过程 1索引 2视图 3存储过程 4触发器 5本章小结 EXECUTE命令的语法格式为： [[EXEC[UTE]]
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 4.执行存储过程 EXECUTE命令的语法格式为： [[EXEC[UTE]] = ] [,…n] [WITH RECOMPILE]

58 4.执行存储过程 1索引 2视图 3存储过程 4触发器 5本章小结 命令中的参数含义如下:
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 4.执行存储过程 命令中的参数含义如下: @return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用EXECUTE 变量可以是任何标量数据类型。 procedure_name：是拟调用的存储过程的完全合法或者不完全合法的名称。过程名称必须符合标识符规则。 ;number：是可选的整数，用于将相同名称的过程进行分组，使它们可以用一条DROP PROCEDURE语句除去。该参数不能用于扩展存储过程。

59 4.执行存储过程 1索引 2视图 3存储过程 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 4.执行存储过程 @procedure_name_var：是局部定义的变量名，代表存储过程名称。 @parameter：该参数是过程参数，在CREATE value：是过程中参数的值。如果参数名称没有指定，参数值必须按CREATE PROCE- DURE语句中定义的顺序给出。 @variable：用来保存参数或者返回参数的变量。 OUTPUT：指定存储过程必须返回一个参数，该存储过程的匹配参数也必须由关键字OUTPUT创建。使用游标变量作参数时使用该关键字。 DEFAULT：根据过程的定义，提供参数的默认值。当过程需要的参数值没有事先定义的默认值，或缺少参数，或指定了DEFAULT关键字，那么就会出错。

60 5.删除存储过程 1索引 2视图 3存储过程 使用企业管理器删除存储过程： 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 5.删除存储过程 使用企业管理器删除存储过程： 利用SQL Server企业管理器删除存储过程是一种简单有效的方法。展开数据库后，单击存储过程图标，在右边窗口中出现的存储过程列表中选择要删除的存储过程，右击，在弹出的快捷菜单中选择【删除】命令，即将选中的存储过程删除。

61 5.删除存储过程 1索引 2视图 3存储过程 使用命令删 除存储过程： 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 5.删除存储过程 使用命令删 除存储过程： 利用DROP PROCEDURE命令删除存储过程，语法格式为： DROP PROCEDURE{procedure}[,…n] 【例5-9】 删除例5-8中创建的存储过程PROC_S。 DROP PROCEDURE PROC_S 存储过程被删除后，其存储在sysobjects和syscomments中的定义也会被删除，如果用系统过程sp_helptext来查看存储过程文本，则不会看到定义的内容。

62 6.在存储过程中使用参数 1索引 2视图 3存储过程 使用输入参数： 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 6.在存储过程中使用参数 使用输入参数： 为参数提供合适的默认值，这样用户可以不指定参数就能执行存储过程。在存储过程开始时，所有的参数都要被检查，以尽早发现丢失和无效的值。 【例5-10】 查询某同学选修的课程名称和成绩。 CREATE PROCEDURE procstud CHAR(8)='李勇') AS SELECT CNAME, DEGREE FROM STUDENT A,SC B,COURSE C WHERE A.SNO=B.SNO AND B.CNO=C.CNO AND

63 6.在存储过程中使用参数 1索引 2视图 3存储过程 使用输入参数来执行存储过程： 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 6.在存储过程中使用参数 使用输入参数来执行存储过程： 使用带有参数的EXEC语句来执行存储过程，注意，参数的顺序不能错。 EXEC procstud '张三'

64 6.在存储过程中使用参数 1索引 2视图 3存储过程 使用输出参数返回值： 4触发器 5本章小结
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 6.在存储过程中使用参数 使用输出参数返回值： 存储过程可以返回多个值，每个值都必须使用OUTPUT关键字在存储过程和调用语句之间来为对象定义。如果在执行存储过程时，OUTPUT关键字被忽略，存储过程依然会执行，但是不返回值。除了text或是image，参数可以是任何数据类型。调用语句要包含一个变量来接受返回值。

65 6.在存储过程中使用参数 1索引 2视图 3存储过程 4触发器 5本章小结 【例5-11】 查询某门课的平均成绩。
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 6.在存储过程中使用参数 【例5-11】 查询某门课的平均成绩。 CREATE PROCEDURE ss NUMERIC OUTPUT ) AS FROM SC WHERE 执行过程为： numeric EXEC ss OUTPUT SELECT

66 6.在存储过程中使用参数 1索引 2视图 3存储过程 4触发器 5本章小结 【例5-12】 查询某位同学的平均成绩。
存储过程概述 创建存储过程 修改存储过程 执行存储过程 删除存储过程 在存储过程中使用参数 4触发器 5本章小结 6.在存储过程中使用参数 【例5-12】 查询某位同学的平均成绩。 CREATE PROCEDURE procavg numeric OUTPUT ) AS FROM student a,SC b WHERE a.sno=b.sno AND 执行过程为： numeric EXEC procave OUTPUT SELECT

67 5.4 触发器 1索引 2视图 3存储过程 4触发器 5本章小结 1 触发器概述 2 触发器的创建 3 触发器的类型 4 修改触发器
5 删除触发器

68 1. 触发器概述 1索引 2视图 3存储过程 4触发器 5本章小结
触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 1. 触发器概述 触发器是一种特殊的存储过程，是SQL Server为保证数据完整性、确保系统正常工作而设置的一种高级技术。触发器在特定的表上定义，该表也称为触发器表。当触发器所保护的数据发生变化时，触发器就会自动运行，以保证数据的完整性与正确性。

69 1. 触发器概述 1索引 2视图 3存储过程 4触发器 触发器及其使用： 5本章小结 可以对数据库进行级联修改。
触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 1. 触发器概述 触发器及其使用： 可以对数据库进行级联修改。 (2)可以完成比CHECK更复杂的约束。与CHECK约束不同，在触发器中可以引用其他的表。 例如，当向SC(成绩)表中插入一条记录时，可以查看student表中是否存在该学生的基本信息，从而判定能否插入该记录。 (3) 可以发现改变前后表中不同的数据，并据此来进行相应的操作。 (4) 对于一个表上的不同的操作(INSERT、UPDATE或DELETE)可以采用不同的触发器，即使是相同的语句也可以调用不同的触发器来完成不同的操作。

70 1. 触发器概述 1索引 2视图 3存储过程 4触发器 触发器的特点： 5本章小结 (1) 在操作有效后才执行的，即其他约束优先于触发器。
触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 1. 触发器概述 触发器的特点： (1) 在操作有效后才执行的，即其他约束优先于触发器。 (2) 它与存储过程的不同之处在于存储过程可以由用户直接调用，而触发器不能被直接调用，它是由事件触发的。 (3) 一个表可以有多个触发器，在不同表上同一类型的触发器也可以有多个。 (4) 触发器允许嵌套，最多为32层。 (5) 触发器可以提高对表及表行有级联操作的应用程序的性能。

71 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 使用企业管理器创建触发器： 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 使用企业管理器创建触发器： 步骤如下: (1) 展开服务器，展开数据库(如Grademanger)。 (2) 单击【表】图标，在右边的窗口中，右击需要在上面创建触发器的表(如student)，在弹出的快捷菜单中选择【所有任务】选项下的【管理触发器】选项。 (3) 打开【触发器属性】对话框，如图5.10所示。在【名称】下拉列表框中选择【新建】选项，在【文本】框中系统给出了定义触发器的基本格式，在此基础上输入具体的触发器定义语句。单击【检查语法】按钮可检查定义语句的语法正确性。 (4) 单击【完成】按钮完成触发器的定义。

72 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 使用命令创建触发器： 5本章小结 (1) 利用命令创建触发器的语法格式 触发器概述
触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 使用命令创建触发器： (1) 利用命令创建触发器的语法格式 CREATE TRIGGER trigger_name ON {table|view} [WITH ENCRYPTION] { {{FOR|AFTER|INSTEAD OF} {[DELETE][,][INSERT][,][UPDATE]} [WITH APPEND] [NOT FOR REPLICATION] AS [{IF UPDATE(column) [{AND|OR}UPDATE(column)] [...n] |IF (COLUMNS_UPDATED( ) {bitwise_operator} updated_bitmask) {comparison_operator } column_bitmask [...n] }] sql_statement[...n]

73 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结 (2) 命令中的参数含义 trigger_name：是触发器的名称。
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 (2) 命令中的参数含义 trigger_name：是触发器的名称。 table|view：是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。 AFTER：指定触发器只有在SQL语句中指定的所有操作都已成功执行后才触发，只有在所有的引用级联操作和约束检查成功完成后才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。不能在视图上定义AFTER触发器。

74 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 INSTEAD OF：指定执行触发器而不是执行触发的SQL语句，从而替代触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。但是，可以在每个具有INSTEAD OF触发器的视图上定义视图。 {[DELETE][,][INSERT][,][UPDATE]}：是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字，如果指定的选项多于一个，需用逗号分隔这些选项。

75 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结 AS：是触发器要执行的操作。
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 AS：是触发器要执行的操作。 sql_statement：是触发器的条件和操作。触发器条件指定其准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。 DELETED和INSERTED：是逻辑(概念)表。这些表在结构上类似于定义触发器的表(也就是在其中尝试用户操作的表)；这些表用于保存用户操作可能更改的行的旧值或新值。

76 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 IF UPDATE (column)：检查在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。 IF (COLUMNS_UPDATED())：检查是否插入或更新了提及的列，仅用于INSERT或UPDATE触发器中。COLUMNS_UPDATED返回varbinary位模式，表示插入或更新了表中的哪些列。

77 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结 bitwise_operator：是用于比较运算的位运算符。
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 bitwise_operator：是用于比较运算的位运算符。 updated_bitmask：是整型位掩码，表示实际更新或插入的列。 comparison_operator：是比较运算符。column_bitmask：是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。

78 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结 (3) 触发器限制
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 (3) 触发器限制 ① CREATE TRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。 ② 触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。 ③ 如果指定触发器所有者名称以限定触发器，那么应以相同的方式限定表名。 ④ 在同一条CREATE TRIGGER语句中，可以为多种用户操作(如INSERT和UPDATE)定义相同的触发器操作。 ⑤ 如果一个表的外键在 DELETE/UPDATE 操作上定义了级联操作，则不能在该表上定义INSTEAD OF DELETE/UPDATE触发器。 ⑥ 在触发器内可以指定任意的SET语句。所选择的SET选项在触发器执行期间有效，并在触发器执行完后自动恢复到以前的设置。

79 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结 ⑦ 与使用存储过程一样，当触发器激发时，将向调用应用程序返回结果。
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 ⑦ 与使用存储过程一样，当触发器激发时，将向调用应用程序返回结果。 ⑧ DELETE触发器不能捕获TRUNCATE TABLE语句。尽管TRUNCATE TABLE语句是没有WHERE子句的DELETE(它删除所有行)，但它是无日志记录的，因而不能执行触发器。TRUNCATE TABLE语句的权限默认授予表所有者且不可传播，所以只有表所有者才需要考虑无意中用TRUNCATE TABLE语句来避免DELETE触发器的问题。 ⑨ 无论有日志记录还是无日志记录，WRITETEXT语句都不激活触发器。 ⑩ 触发器中不允许以下Transact-SQL语句。

80 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结 ALTER DATABASE CREATE DATABASE
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG

81 2. 触发器的创建 1索引 2视图 3存储过程 4触发器 5本章小结 【例5-13】 创建约束性别字段的触发器。
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 2. 触发器的创建 【例5-13】 创建约束性别字段的触发器。 CREATE TRIGGER trigsex ON student FOR INSERT, UPDATE AS char(2) FROM inserted BEGIN ROLLBACK RAISERROR('性别必须为男或女',16,1) END

82 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 SQL Server 2000提供了两种触发器：INSTEAD OF和AFTER触发器。这两种触发器的差别在于它们被激活的时机不同。 (1) INSTEAD OF触发器用于替代引起触发器执行的Transact-SQL语句。除用于表之外，INSTEAD OF触发器还可以用于视图，用来扩展视图可以支持的更新操作。 (2) AFTER触发器在一个INSERT、UPDATE或DELETE语句之后执行，进行约束检查等操作都将在AFTER触发器被激活之前发生。AFTER触发器只能用于表。

83 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 一个表或视图的每个修改操作(INSERT、UPDATE和DELETE)只有一个INSTEAD OF触发器，一个表的每个修改操作可以有多个AFTER触发器。 在触发器执行的时候，会产生两个临时表：inserted表和deleted表。它们的结构和触发器所在的表的结构相同，SQL Server自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表的数据进行更改。

84 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 deleted表用于DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。deleted表和触发器表通常没有相同的行。 inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。inserted表中的行是触发器表中新行的副本。

85 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 5本章小结 在对具有触发器的表（触发器表）进行操作时，其操作过程如下：
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 在对具有触发器的表（触发器表）进行操作时，其操作过程如下：  执行INSERT操作插入到触发器表中的新行被插入到inserted表中。  执行DELETE操作从触发器表中删除的行被插入到deleted表中。  执行UPDATE操作先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到deleted表中，插入的新行被插入到inserted表中。

86 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 INSERT与UPDATE触发器： 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 INSERT与UPDATE触发器： 当向表中插入数据时，并且所有数据约束都通过之后，INSERT触发器才会被执行。新的记录不但加到触发器表中，而且还会有副本加到INSERTED表中。INSERTED表与DELETED表一样，它们的记录是可读的，可以进行比较，以便确认这些数据是否正确。 利用UPDATE修改一条记录时，相当于删除一条记录然后再增加一条新记录，所以UPDATE操作使用DELETED和INSERTED两个表。当使用UPDATE操作时，触发器表中原来的记录被移到DELETED表中，修改过的记录插入到INSERTED表中，触发器可以检查这两个表，以便确定该执行什么样的操作。

87 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 【例5-14】 在数据库Grademanager中，当在表SC中插入某一位同学的成绩时，先检测student表中是否存在该生的基本信息。 CREATE TRIGGER triginsert ON SC FOR INSERT AS CHAR(10) FROM STUDENT WHERE SNO= (SELECT SNO FROM INSERTED) IS NULL BEGIN ROLLBACK RAISERROR('STUDENT表中没有该生基本信息，不能插入该成绩',16,1) END

88 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 【例5-15】 在数据库Grademanager中，当修改student表中学生的学号时，SC表中相应的学号随之修改。 CREATE TRIGGER trigupdate ON student FOR UPDATE AS UPDATE SC SET SC.SNO= (SELECT SNO FROM INSERTED) WHERE SC.SNO=(SELECT SNO FROM DELETED)

89 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 DELETE触发器： 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 DELETE触发器： 对表进行删除时，如果此表有DELETE型的触发器，则触发器被触发执行。被删除的记录存在DELETED表中。当执行DELETE操作时，应注意以下几点。 (1) 被放到DELETED表中的记录，不再存于触发器表中，因此触发器表与DELETED表没有共同的记录。 (2) DELETED表放在内存储器中，因此执行效率很高。

90 3. 触发器的类型 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 3. 触发器的类型 【例5-16】 当删除 student表中的学生记录时，SC表中成绩信息随之删除。 CREATE TRIGGER trigdele ON student FOR DELETE AS DELETE SC WHERE SC.SNO= (SELECT SNO FROM DELETED)

91 4. 修改触发器 1索引 2视图 3存储过程 4触发器 5本章小结 利用Transact-SQL命令修改触发器的语法与创建触发器类似：
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 4. 修改触发器 利用Transact-SQL命令修改触发器的语法与创建触发器类似： ALTER TRIGGER trigger_name ON(table|view) [WITH ENCRYPTION] { {(FOR|AFTER|INSTEAD OF){[DELETE][,][INSERT][,][UPDATE]} [NOT FOR REPLICATION] AS sql_statement[...n] } |

92 4. 修改触发器 1索引 2视图 3存储过程 4触发器 5本章小结
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 4. 修改触发器 {(FOR|AFTER|INSTEAD OF){[INSERT][,][UPDATE]} [NOT FOR REPLICATION] AS {IF UPDATE(column) [{AND|OR}UPDATE(column)] [...n] |IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask) {comparison_operator}column_bitmask[...n] } sql_statement[...n] } } 语法中的参数与创建触发器时的意义相同。

93 5. 删除触发器 1索引 2视图 3存储过程 4触发器 5本章小结 当与触发器相关的表被删除时，触发器也随之被删除。删除触发器的语法格式为：
触发器概述 触发器的创建 触发器的类型 修改触发器 删除触发器 5本章小结 5. 删除触发器 当与触发器相关的表被删除时，触发器也随之被删除。删除触发器的语法格式为： DROP TRIGGER{trigger}[,...n  例如，删除例5-13中创建的触发器trigsex的命令为： DROP TRIGGER trigsex

94 1索引 2视图 3存储过程 4触发器 5本章小结 5.5 本章小结 本章重点介绍了索引、视图、存储过程及触发器的创建和使用方法。索引能够提高查询速度，视图能够简化用户操作，存储过程能够提高执行速度，并使程序设计模块化，触发器能够提高数据库的安全性。 

95 第5章 SQL的高级应用 谢谢！ 再见！