数据库原理电子教案：第八章-存贮过程和触发器.doc

第8章 存贮过程和触发器
课题：存贮过程和触发器（一）
目的要求：
掌握存贮过程的功能与分类
掌握存储过程的建立用法
掌握存储过程的调用方法
掌握存储过程的维护方法
重点难点：
存储过程的设计
课外作业布置：
复习本节课内容，完成本章习题
存贮过程和触发器
复习与提问
1、索引分为哪几类？聚集索引和非聚集索引有什么不同？
2、实体完整性、参照完整性和自定义完整性分别是通过什么约束实现的？
3、解释下面语句的功能：
Constraint pk_no primary key(姓名, 性别)
constraint fk_ks2 foreign key(课程号) references 课程(课程号)
4、解释下面语句的功能：
constraint chk_cj check (result>=40 and result<=100)
create default address as ‘长沙市香樟路22号’
8.1 存储过程
1．存储过程的定义
存储过程(Stored Procedure)：是被SQL Server编译成一个单一执行计划的一组SQL语句，执行一次后调入内存，可以反复使用，相当于过程（函数）或子程序。
存储过程可以接收参数，也能以参数形式返回输出结果，或者返回是否成功的状态信息。
存储过程属于数据库。
存储过程分为五类：系统提供的存储过程、本地存储过程、用户定义的存储过程、扩展存储过程、扩展存储过程。
（1）系统提供的存储过程：在安装SQL Server时，系统创建了很多系统存储过程。系统存贮过程均以SP_开头，开如SP_XXXX，如SP_HELPDB，可以在任何一个数据库中执行。
（2）本地存储过程：即自定义的存储过程，是由用户为完成某一特定功能而编写的存储过程。其名称不能以sp_为前缀。
（3）临时存储过程：属于本地存储过程，如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用；如果本地存储过程的名称前有两个“##”，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。
（4）远程存储过程：从远程服务器上调用的存储过程。
（5）扩展存储过程：是对动态链接库（DLL）函数的调用。前缀名是SP_或XP_，使用前需要加载到SQL中，才能象普通存储过程那样调用。
2．存储过程的优点
存储过程具有如下的优点：
（1）减少网络流量，存储过程执行一次后，其执行规划就驻留在高速缓冲存储器。
（2）增强代码的重用性和共享性。
（3）加快系统运行速度：在服务器端运行。
（4）加强安全性：带参数调用，利用参数返回。
3．用T-SQL语句创建存储过程
创建存储过程的语法为：
CREATE PROCEDURE <存储过程名> --设置过程名
[WITH ENCRYPTION] --将存贮过程代码加密
[< @参数名 类型> [= 默认值] [OUTPUT]][ ,...n ]
AS sql语句[ ...n ]

说明： @参数名 类型：用于接受指定的实际参数及类型，注意参数前面有@标志。procedure可以简写为proc。
OUTPUT：表示参数是输出参数,
SQL语句：构造存贮过程的SQL语句。
存储过程可以带输入输出参数，也可以不带
创建存储过程的步骤：
编写SQL语句：如select count(*) from student_info
测试SQL语句：执行这些语句，确认符合要求。
如果能够返回正确结果，则加上其它语句，写入存贮过程
建立存贮过程举例一：不带参数
例1：创建一存储过程，检索学生信息表（student_info）表中的所有记录。该存储过程不带任何输入输出参数。
USE student --使用student数据库
/*判断是否存在存储过程proc1，存在则删除*/
IF EXISTS(SELECT NAME FROM SYSOBJECTS
WHERE NAME=‘proc1’) --请记住此语句
DROP PROCEDURE proc1 --删除存储过程
GO
CREATE PROCEDURE proc1 --创建存储过程
AS
SELECT * FROM student_info --SQL语句
建立存贮过程举例二：带输入参数
例2：创建一个存储过程，根据传入的学号，检索此学生的基本情况，包括学号、姓名、性别、出生日期、班级，课程号、成绩。（带输入参数）
USE student --使用student数据库
/*判断是否存在存储过程proc2，存在则删除*/
IF EXISTS(SELECT NAME FROM SYSOBJECTS
WHERE NAME='proc2')
DROP PROCEDURE proc2
GO
CREATE PROCEDURE proc2 --创建存储过程
@id char(8) --指定传入的参数名，要求与表student_info中student-id的类型和宽度相同
AS
SELECT student_info.student_id,student_name,student_sex,born_date,class_no,course_no,result
FROM student_info,result_info
WHERE student_info .student_id=result_info.student_id and student_info. student_id=@id
建立存贮过程举例三：带输入和输出参数
例3：创建一个存储过程，根据传入的姓名，判断此学生是否是男生，如果是男生，返回“性别为男”，如果是女生，返回“性别为女”，否则返回“性别未填写”。
USE student --使用student数据库
/*判断是否存在存储过程proc3，存在则删除*/
IF EXISTS(SELECT NAME FROM SYSOBJECTS
WHERE NAME='proc3')
DROP PROCEDURE proc3
GO
CREATE PROCEDURE proc3 --创建存储过程
@name char(10),@outputinfo char(20) output
AS
declare @sex char(2)
set @sex=(select student_sex from student_info Where student_name=@name)
if exists(select student_name from student_info where student_name=@name)
if @sex='男'
set @outputinfo='性别为男'
else if @sex='女'
set @outputinfo='性别为女'
else
set @outputinfo='性别未填写'
说明：
如果需要执行本存贮过程并返回显示结果，可以在程序最后加上语句：
select @outputinfo

执行存贮过程的方法：
proc3 参数1,参数2,…..
如：
exec proc3 '陈白露',i
结果：

4．执行存储过程
执行存储过程的完整语法如下：
[EXEC] 存储过程名 [实际参数列表]
其中：
[EXEC]可以省略，也可以写成execute
存储教程的执行及结果的显示
例4：执行例3的存储过程，查看“陈白露”性别。
DECLARE @result CHAR(20)
EXECUTE proc3 '陈白露',@result OUTPUT
SELECT @ result as 性别
执行结果如下：

5．删除存储过程
删除存储过程是指删除由用户创建的存储过程。
格式：DROP PROCEDURE 存储过程名[,……]
例：删除例1所创建的存储过程：
DROP PROCEDURE proc1
存储过程的查看
查看存储过程也是通过执行一个存储过程sp_helptext来实现的。
格式：exec sp_helptext 存储过程名
例： exec sp_helptext proc2
说明：结果是产生存储过程的代码
不能同时查看几个存储过程的代码
存贮过程名不可省略
6．用企业管理器创建存储过程

7．用向导创建存储过程
工具－向导－数据库－创建存储过程
8．修改存储过程
命令：与建立存储过程命令类似
ALTER PROCEDURE 存储过程名
[ { @参数 类型 }
[=默认值] [OUTPUT]] [ ,...n ]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS sql语句[ ...n ]
第26教案 实训13：存贮过程和触发器（一）
实训13：存贮过程和触发器（一）
（一）、实训目的
通过实训掌握存贮过程的功能与分类
通过实训掌握存储过程的建立用法
通过实训掌握存储过程的调用方法
通过实训掌握存储过程的维护方法
（二）、实训内容
【例1】创建一个用于向class_info表中插入记录的存储过程add_class。
USE student
GO
CREATE PROC add_class
(
@class_id int,
@class_name char(20) ,
@director char(10),
@profession char(14) )
AS
INSERT INTO class_info
VALUES(@class_id, @class_name, @director , @profession)
则可以使用下面的SQL语句调用该存储过程：
EXEC add_class 200202,'计算机应用022', '张波', '计算机应用'
执行语句：
select * from class_info
【例2】在数据库student中，查询指定学生名的指定课程的开课时间、成绩以及学分。
USE student
IF EXISTS
(SELECT name FROM sysobjects
WHERE name='stu_score_pro' AND type='P')
DROP PROCEDURE stu_score_pro
GO
CREATE PROCEDURE stu_score_pro
@sname char(10),@cname char(10)
AS
SELECT a. student_name, c.course_name,
c.course_start,b.result,c.course_score
FROM student_info a JOIN result_info b
ON a.student_id= b.student_id
JOIN course_info c
ON b.course_no=c.course_no
WHERE c.course_name=@cname
AND a.student_name=@sname
GO
则可以使用下面的SQL语句调用该存储过程：
EXEC stu_score_pro '陈白露','马克思主义'
【例3】创建一个用于向class_info表中插入记录的存储过程add_class，如果指导老师和专业没有，默认值为'无'。
USE student
GO
CREATE PROC add_class
(@class_id int,
@class_name char(20),
@director char(10) ='无',
@profession char(14) ='无'
)
AS
INSERT INTO class_info
VALUES(@class_id,@class_name,@director,@profession)
在上面创建的存储过程中，包含4个参数，其中@director和@profession具有默认值“无”。如果调用该存储过程，则可以使用下面的SQL语句：
EXEC add_class 200202, '计算机应用022'
GO
SELECT * FROM class_info
GO
【例4】下面的存储过程Query_student返回两个参数@student_name和@address，分别代表学生姓名和家庭住址。
CREATE PROC Query_student
(
@student_id int(4),
@student_name char(10) OUTPUT,
@address varchar(50) OUTPUT
)
AS
SELECT @student_name =student_name, @address =address
FROM book WHERE student_id =@student_id
执行该存储过程，查询学号为student_id 20050203的学生姓名和家庭地址。
DECLARE @student_name char(10)
DECLARE @address varchar(50)
EXEC Query_student 20050203, @student_name OUTPUT, @address OUTPUT
SELECT '姓名'=@student_name,'家庭住址'=@address
GO
【例5】下面的存储过程根据输入的参数来判断返回值。
USE student
GO
CREATE PROC test_return
(
@input_num int =0
)
AS
IF @input_num=0
RETURN 0
IF @input_num>0
RETURN 1
IF @input_num<0
RETURN -1

第27教案 第8章 存储过程与触发器（二）
课题：存储过程与触发器（二）
目的要求：
掌握触发器的功能与分类
掌握触发器的建立用法
掌握触发器的调用方法
掌握触发器的维护方法
重点难点：
触发器的设计
课外作业布置：
复习本节课内容，完成本章习题
8.2 触发器（ Trigger）
触发器（Trigger）是用户对某一表进行插入、更新和删除操作时被触发执行的一段程序，用来检查用户对表的操作是否合乎整个应用系统的需求，是否合乎商业规则，以及维持表内数据的完整性和正确性。
1．触发器的概念和工作原理
触发器和存储过程一样是由T-SQL语句写成的程序。存储过程由用户利用EXECUTE命令执行，但触发器是在用户对某一表做操作时自动触发执行的，属于表。
触发器的功用和表内所设置的一些列限制（Constraints）有些重叠，事实上如果列限制的功能能够达到应用程序的要求则无须用额外的触发器，但触发器处理多表更新问题更为方便。
系统为触发器自动创建2个表：inserted表和deleted表，由系统自动管理，可以查看，不允许用户修改，其结构与被触发的表结构相同。触发器工作完成后，与该触发器相关的这两个表也会被删除。
触发器的工作机制：
（1）INSERTED表：存放由于INSERT或UPDATE语句的执行而导致要加到该触发器作用的表中去的所有新行。即把插入或更新表的新行值，在插入或更新表的同时，也将其副本存入INSERTED表中。
（2）DELETED表：存放由于DELETE或UPDATE语句的执行而导致要从被该触发器作用的表中删除的所有行。
2.触发器的分类
触发器有两种：
（1）AFTER触发器：之后触发器，在触发的语句执行之后才执行。AFTER触发器要求只有执行INSERT、UPDATE和DELETE中某一操作之后才被触发且只能针对表进行定义。可以针对一个表的同一操作定义多个触发器。
（2）INSTEAD OF触发器：代替触发器，代替触发的语句执行。即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，其动作要早于表的约束处理。
AFTER触发器与INSTEAD OF触发器性能比较：
AFTER触发器的缺点是:因为只能在修改语句执行之后才能执行,如果触发器出现异常或更新操作不满足触发器中定义的规则,则要撤销事务，称为回滚（ROLLBACK）。
INSTEAD OF触发器可以对表或视图生成,但每个操作只能定义一个INSTEAD OF触发器，对于视图,只能一次更新、插入或删除一个基本表数据，INSTEAD OF触发器能改变这一限制，一旦操作出错，无须撤销事务。
3．AFTER触发器
创建AFTER触发器的语法如下：
CREATE TRIGGER <触发器名> --必须是第一条语句
ON <表名> --触发的对象（表）
[WITH ENCRYPTION] --加密
<FOR | AFTER | INSTEAD OF> --触发器类型
< [ INSERT ] [ , ] [ UPDATE ] [ , ] [DELETE]> --执行什么操作时触发
AS -- 两种触发器功能完全相同
[IF UPDATE (列名 ) [ ...n ]] --触发条件
SQL语句 [ ...n ]> --触发动作
（1）INSERT触发器
INSERT触发器由INSERT语句触发，即用户在表中插入一条记录且插入成功时，触发INSERT触发器。
（2）DELETE触发器
DELETE触发器的过程与INSERT触发器相似，只是使用的是DELETED表，其中包含刚刚删除的记录，而不是INSERTED表。
（3）UPDATE触发器。
更新操作可以看成一个插入加一个删除：插入新值和删除旧值。
（1）INSERT触发器。
INSERT触发器的工作过程如下：
1）用户或系统运行INSERT语句。
2）如果记录不违反限制（完整性检查），则将记录插入到临时表INSERTED中。
3）触发触发器。
4）如果触发器执行完毕而无错误，则INSERTED表被删除，插入操作完成。
（2）DELETE触发器
DELETE触发器的工作过程如下：
1）用户或系统运行DELETE语句。
2）如果记录不违反外键限制，则删除表中的记录并将其插入到临时表DELETED中。
3）触发触发器。
4）如果触发器执行完毕而无错误，则DELETED表被删除，删除操作完成。
4.触发器应用举例
例1：创建一触发器以实现如下功能：当试图删除result _info表中的一条记录时，若成绩为空或不够60，则撤消事务。我们基于这样的假设：如果成绩为空，还要重修；若小于60，需要补考。
USE student --使用BookSys数据库
/*判断是否存在触发器t1，存在则删除*/
IF EXISTS(SELECT NAME FROM SYSOBJECTS
WHERE NAME='t1' and xtype='TR')
DROP TRIGGER t1
GO
CREATE TRIGGER t1 --创建触发器
ON result_info
AFTER DELETE --DELETE型触发器
AS
DECLARE @cj float --声明一个变量以存储成绩
/*从临时表中检索出被删除记录的成绩至变量中*/
SELECT @cj=result FROM DELETED
/*如果成绩为空或者成绩<60*/
IF (@cj IS NULL) OR @cj<60
BEGIN
RAISERROR('不允许删除这条记录，因为该学生需要重新考试', 16,1)
ROLLBACK TRANSACTION --撤消从表中删除的记录
END

--语句RAISERROR('不允许删除这条记录，因为该学生需要重新考试',16,1)是什么意思？
语句RAISERROR的用法
功能：返回用户定义的错误信息并设系统标志，记录发生错误。
格式：RAISERROR (提示信息,错误程序代码,状态值）
参数说明：提示信息--最多可包含 400 个字符。
错误程序代码--用户定义的与消息关联的严重级别。可使用从 0 到 18 之间的严重级别，18以上被认为是致命错误。
状态值--从 1 到 127 的任意整数，表示有关错误调用状态的信息，默认为 1。
DELETE触发器实例演示
例：删除学号为’20050101’的成绩记录，观察运行结果，并分析原因。
delete
from result_info
where cardid=‘20050101’

（3）UPDATE触发器
UPDATE触发器的工作过程如下：
1）用户或系统运行UPDATE语句。
2）如果记录不违反限制，则更新表并把旧记录插入到DELETED表中，把新记录插入到INSERTED表中。
3）触发触发器。
4）如果触发器执行完毕而无错误，则DELETED和INSERTED表被删除，更新操作完成。
【例2】在数据库student中，新建一表文件B1，试创建一触发器，在表文件B1中插入、修改和删除记录时，能自动显示表中的内容。
USE student
IF EXISTS (SELECT name FROM sysobjects WHERE name ='B1'and xtype='U')
DROP TABLE B1
GO
/*创建表B1 */
CREATE TABLE B1(学号 int, 姓名 char(10))
GO
/*如果触发器T1_B1存在，则删除*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘T1_B1' AND xtype = 'TR')
DROP TRIGGER T_B1
GO
/*创建触发器T1_B1 */
CREATE TRIGGER T1_B1
ON B1
FOR INSERT, UPDATE, DELETE
AS
SELECT * FROM B1
GO
运行实例：
insert into B1
values(0100,'刘小春')
系统自动显示：

5.inserted表和deleted表的作用
例3：显示inserted表和deleted表的内容
/*如果触发器T2_B1存在，则删除*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'T2_B1' AND xtype = 'TR')
DROP TRIGGER T2_B1
GO
/*创建触发器T2_B1 */
CREATE TRIGGER T2_B1 ON B1
FOR INSERT, UPDATE, DELETE
AS
SELECT * FROM inserted
SELECT * FROM deleted
GO
如果此时执行下面的UPDATE语句：
UPDATE B1 SET 姓名='张峰'
WHERE 学号=2
应用举例
当向表中插入或者更新记录时，INSERT或者UPDATE触发器被执行。一般情况下，这两种触发器常用来检查插入或者修改后的数据是否满足要求。
【例4】在数据库student中创建一触发器，当向result_info表插入一记录时，检查该记录的学号在student_info表中是否存在且课程号在course_info表中是否存在，如有一项不成立，则不允许插入。
USE student
IF EXISTS
(SELECT name FROM sysobjects
WHERE name ='check_insert' AND xtype='TR')
DROP TRIGGER check_insert
GO
CREATE TRIGGER check_insert ON result_info
FOR INSERT
AS
IF EXISTS（SELECT * FROM inserted a
WHERE a.student_id NOT IN
(SELECT b.student_id FROM student_info b)
OR a. course_no NOT IN
(SELECT c.course_no FROM course_info c ))
BEGIN
RAISERROR('违背外键约束规则',16,1)
ROLLBACK TRANSACTION
END
【例5】在数据库student中创建一个表score，然后为这个表创建一个触发器check_score，检查插入的成绩是否在0到100之间。
USE student
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'score' and xtype='TR')
DROP TABLE score
GO
CREATE TABLE score --*创建score表*
( student_no int,
score int
)
/*检查是否存在check_score触发器，若存在，则删除*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'check_score' AND xtype = 'TR')
DROP TRIGGER check_score
/*在score表上创建check_score触发器*/
CREATE TRIGGER check_score
ON score
FOR INSERT
AS
DECLARE @score int
SELECT @score=score FROM inserted
IF @score<0 OR @score>100
BEGIN
ROLLBACK
RAISERROR('成绩必须在0到100之间！',16,1)
END
6．使用企业管理器创建触发器
使用企业管理器创建触发器的步骤如下：
（1）右击要创建触发器的表，“所有任务”→“管理触发器” ，出现如图的对话框。
（2）在名称下拉列表中，选择“新建”选项（也是缺省的选项），在“文本”框中按照前面讲述的语法输入新建触发器的文本。
（3）输完后，单击“检查语法”按钮，则系统自动检查上面所输入的文本。
（4）通过了语法检查之后，单击“确定”按钮，触发器创建完毕。

7．修改与删除触发器
修改触发器的命令是alter，格式与建立相同
ALTER TRIGGER trigger_name ON ……
DROP TRIGGER用于删除触发器，可同时删除一个或多个触发器，典型的语法结构如下：
DROP TRIGGER trigger_name [ ,...n ]
第28教案 实训14：存储过程与触发器（二）
实训14：存储过程与触发器（二）
（一）、实训目的
通过实训掌握触发器的功能与分类
通过实训掌握触发器的建立用法
通过实训掌握触发器的调用方法
通过实训触发器的维护方法
（二）、实训内容
例1：创建一触发器以实现如下功能：当试图删除result _info表中的一条记录时，若成绩为空或不够60，则撤消事务。我们基于这样的假设：如果成绩为空，还要重修；若小于60，需要补考。
USE student --使用BookSys数据库
/*判断是否存在触发器t1，存在则删除*/
IF EXISTS(SELECT NAME FROM SYSOBJECTS
WHERE NAME='t1' and xtype='TR')
DROP TRIGGER t1
GO
CREATE TRIGGER t1 --创建触发器
ON result_info
AFTER DELETE --DELETE型触发器
AS
DECLARE @cj float --声明一个变量以存储成绩
/*从临时表中检索出被删除记录的成绩至变量中*/
SELECT @cj=result FROM DELETED
/*如果成绩为空或者成绩<60*/
IF (@cj IS NULL) OR @cj<60
BEGIN
RAISERROR('不允许删除这条记录，因为该学生需要重新考试', 16,1)
ROLLBACK TRANSACTION --撤消从表中删除的记录
END
【例2】在数据库student中，新建一表文件B1，试创建一触发器，在表文件B1中插入、修改和删除记录时，能自动显示表中的内容。
USE student
IF EXISTS (SELECT name FROM sysobjects WHERE name ='B1'and xtype='U')
DROP TABLE B1
GO
/*创建表B1 */
CREATE TABLE B1(学号 int, 姓名 char(10))
GO
/*如果触发器T1_B1存在，则删除*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘T1_B1' AND xtype = 'TR')
DROP TRIGGER T_B1
GO
/*创建触发器T1_B1 */
CREATE TRIGGER T1_B1
ON B1
FOR INSERT, UPDATE, DELETE
AS
SELECT * FROM B1
【例3】在数据库student中创建一触发器，当向result_info表插入一记录时，检查该记录的学号在student_info表中是否存在且课程号在course_info表中是否存在，如有一项不成立，则不允许插入。
USE student
IF EXISTS
(SELECT name FROM sysobjects
WHERE name ='check_insert' AND xtype='TR')
DROP TRIGGER check_insert
GO
CREATE TRIGGER check_insert ON result_info
FOR INSERT
AS
IF EXISTS（SELECT * FROM inserted a
WHERE a.student_id NOT IN
(SELECT b.student_id FROM student_info b)
OR a. course_no NOT IN
(SELECT c.course_no FROM course_info c ))
BEGIN
RAISERROR('违背外键约束规则',16,1)
ROLLBACK TRANSACTION
END
【例4】在数据库student中创建一个表score，然后为这个表创建一个触发器check_score，检查插入的成绩是否在0到100之间。
USE student
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'score' and xtype='TR')
DROP TABLE score
GO
CREATE TABLE score --*创建score表*
( student_no int,
score int
)
/*检查是否存在check_score触发器，若存在，则删除*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'check_score' AND xtype = 'TR')
DROP TRIGGER check_score
GO
/*在score表上创建check_score触发器*/
CREATE TRIGGER check_score
ON score
FOR INSERT
AS
DECLARE @score int
SELECT @score=score FROM inserted
IF @score<0 OR @score>100
BEGIN
ROLLBACK
RAISERROR('成绩必须在0到100之间！',16,1)
END