第7章 存储过程和触发器

1、7 . 1 存储存储过程过程7 . 2 触发器触发器在 Microsoft SQL Server 2005 中，使用T-SQL语句编写存储过程。存储过程可以接受输入参数、返回表格或标题结果和消息，调用“数据定义语言（DDL）”和“数据操作语言（DML）”语句，然后返回输出参数。7.1.1 存储过程的类型存储过程的类型在 Microsoft SQL Server 2005 中有下列几种类型存储过程：(1) 系统存储过程。系统存储过程是由SQL Server提供的存储过程，可以作为命令执行。(2) 扩展存储过程。扩展存储过程是指在SQL Server 2005环境之外，使用编程语言（例如C+语言）

2、创建的外部例程形成的动态链接库（DLL）。(3) 用户存储过程。Microsoft SQL Server 2005中，用户存储过程可以使用T-SQL语言编写，也可以使用CLR方式编写。h1使用使用T-SQL命令创建存储过程命令创建存储过程创建存储过程的语句是CREATE PROCEDURE或CREATE PROC，二者同义。语法：CREATE PROC | PROCEDURE /*定义参数的类型*/ VARYING = default OUT | OUTPUT /*定义参数的属性*/ ,.n WITH ENCRYPTION /*对语句文本加密*/AS .n /*执行的操作*/;说明：如果希望其

3、他用户无法查看存储过程的定义，则可以使用WITH ENCRYPTION子句创建存储过程。这样，过程定义将以不可读的形式存储。不要以sp_为前缀创建任何存储过程。sp_前缀是SQL Server用来命名系统存储过程的，使用这样的名称可能会与以后的某些系统存储过程发生冲突VARYING：指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变，仅适用于cursor参数。h2存储过程的执行存储过程的执行通过EXECUTE或EXEC命令可以执行一个已定义的存储过程，EXEC是EXECUTE的简写。语法格式： EXEC | EXECUTE return_status = = | OUT

4、PUT | DEFAULT ,.n ;h3举例举例(1) 设计简单的存储过程。【例7.1】 返回05880101号学生的成绩情况，该存储过程不使用任何参数。存储过程定义后，执行存储过程student_info：EXECUTE student_info(2) 使用带参数的存储过程。【例7.2】 从PXSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接收与传递参数精确匹配的值。执行存储过程student_info1：EXECUTE student_info1 周一, java(3) 使用带OUPUT参数的存储过程。【例7.3】 创建一个存储过程do_action，根据条件处理相应数据

5、，处理后输出相应的信息。接下来执行存储过程do_action，并查看结果：DECLARE s_str char(8)EXEC do_action 0, s_str OUTPUTSELECT s_str;(4) 使用带有通配符参数的存储过程。【例7.4】 从三个表的连接中返回指定学生、指定课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。 执行存储过程：EXECUTE st_info /*参数使用默认值*/或者：EXECUTE st_info 张% /*传递给sname 的实参为张%*/(5) 使用OUTPUT游标参数的存储过程。OUTPUT游标用于返回存储过程

6、的局部游标。【例7.4】 在表student上声明并打开一个游标。在如下的批处理中，声明一个局部游标变量，执行上述存储过程，并将游标赋值给局部变量，然后通过该游标变量读取记录。使用界面方式定义与修改存储过程。对于一个已创建的存储过程，可有2种方法删除：(1) 通过对象资源管理器删除，此方法非常简单，请读者自己练习。(2) 利用T-SQL语句DROP PROCEDURE删除，语法格式：DROP PROC | PROCEDURE ,.n 触发器是一个被指定关联到一个表的数据对象，触发器是不需要调用的，当对一个表的特别事件出现时，它就会被激活。触发口碑 代码也是由SQL语句组成的，因此用在存储过程中

7、的语句也可以在触发器的定义中。触发器是一类特殊的存储过程，与表的关系密切，用于保护表中的数据。当有操作影响到触发器保护的数据时，触发器将自动执行。 触发器的作用触发器的作用 触发器的主要作用是能实现由主键和外键所不能保证的、复杂的参照完整性和数据的一致性，除此之外，触发器还有其他许多不同的功能。 跟踪变化 触发器可以侦测数据库内的操作，从而禁止了数据库未经许可的更新和变化，使数据库的修改、更新操作更安全，数据库运行更稳定。 可以强化数据条件约束 触发器能够实现比CHECK语句更为复杂的约束，更适合在大型数据库管理系统中用来约束数据的完整性。 级联和并行运行 触发器可以侦测数据库内的操作，并自动

8、地级联影响整个数据库的各项内容。例如，某个表的触发器中包含有对另外一个表的数据操作，如删除、更新、插入，而该操作又导致该表上的触发器被触发。 由此可见，触发器可以实现高级形式的业务规则、复杂行为限制和定制记录等功能。7.2.1 触发器的类型触发器的类型(1) DML触发器。当数据库中发生数据操纵语言（DML）事件时将调用DML触发器。DML事件包括对表或视图的INSERT语句、UPDATE语句和DELETE语句，因而DML触发器可分为3种类型：INSERT、UPDATE和DELETE。(2) DDL触发器。DDL触发器是SQL Server 2005新增的功能，也是由相应的事件触发，但DDL触

9、发器触发的事件是数据定义语句（DDL）语句。这些语句主要是以CREATE、ALTER、DROP等关键字开头的语句。h1创建创建DML触发器触发器语法格式：CREATE TRIGGER . ON /*指定操作对象*/ WITH ENCRYPTION /*说明是否采用加密方式*/ AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS ; .n After：用于说明触发器在指定操作成功执行后触发，如After Insert表示向表中插入数据时激活触发器。Insert|Update|Delete：指定激活触发器的语句类型，必须至少指定一个选项。在触发器定义中允许

10、使用上述选项的任意顺序组合。如果触发器表存储约束，则在after触发器执行之前检查这些约束。如果违反了约束，则不after触发器。触发器中使用的特殊表。触发器中使用的特殊表。在执行触发器时，系统创建了两个特殊的临时表inserted表和deleted表，这两个表的内容如下、Inserted表：当向表中插入数据时，INSERT触发器角发执行，新的记录插入到触发器表和inserted表中。Deleted表：用于保存已从表中删除的记录，当触发一个DELETED触发器时，被删除的记录存放到deleted表中。修改一条记录等于插入一条新记录，同时删除旧记录。当对定义了UPDATE触发器的表记录修改时，表

11、中愿望录移到deleted表中。修改过的记录插入到inserted表中。由于inserted表和deleted表都是临时表，它们在触发器执行时被创建，触发器执行完后就消失了，所以只可以在触发器的主唱 句中使用SELECT语句查询这两个表。 Update(column)函数函数 Update()函数返回一个布尔值，指示是否对表的指定列进行了insert或update操作。其中insert操作可是看做是把原来的null值换成了新值。 例：在student表中创建update和delete触发器，当修改或删除student表中的学号时，同时修改或删除sc表中的该学号。Delete from stud

12、entWhere age=21update student set sno=05880304where sno=05880101 Instead of 触发器触发器 After触发器是在触发语句执行后触发的。与after触发器不同的是，instead of 触发触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句。 在表或视图上，每个insert、update或delete语句最多可以定义一个instead of触发器。 Instead of触发器的主要作用是用于不可更新视图支持更新。如果视图不可更新，则必须使用instead of触发器支持基表中数据的插入、更新和删除操作。例

13、：创建表table_2，属性只有一个a，整数类型，在表中创建instead of insert触发器，当向表中插入记录时显示相应消息。select * from table_2查询的表中没有插入任何的数据查询的表中没有插入任何的数据insert into table_2 values(10) 视图更新操作的限制视图更新操作的限制 如果视图是从多个基本表使用连接操作导出的，则不允许更新。 如果定义视图的select语句包含group by、distinct、聚组函数（虚列），则不允许更新。 如果建立视图时带with read only选项，则不能对该视图进行任何插入、更新和删除操作。h2创建创建

14、DDL触发器触发器语法格式：CREATE TRIGGER ON ALL SERVER | DATABASE WITH ENCRYPTION AFTER event_type | event_group ,.n AS ; .n DDL触发要用于：防止对数据库架构进行某些修改；希望数据库中发生某些变化以利于相应数据库架构中的更改；记录数据库架构中的更改或事件。DDL触发器只在响应由T-SQL语法所指定的DDL事件时才会触发。ALL SERVER | DATABASE：ALL SERVER是指当前DDL触发器的作用于当前服务器。DATABASE指DDL触发器作用于当前数据库Event_type：此选项包括CREATE_TABLE、ALTER_TABLE、DROP_TABLE、CREATE_VIEW等。Event_group：ON关键字后面为ALL SERVER此选项使用该名称，如CREATE_DATA