6 存储过程与触发器 (1)存储过程是T-SQL语句的集合，它作为数据库对象之一被存储在数据库中。 (2)存储过程的作用和使用方式类似于一些编程语言中的过程。 使用存储过程有以下优点： 1)可以在一个存储过程中执行多条SQL语句； 2)可通过输入参数的变化调用存储过程进行动态执行； 3)存储过程在创建时就在服务器端进行了编译，节省SQL语句的运行时间； 4)提供了安全机制，它限制了用户访问SQL语句的权利，只为特定用户开放存储过程。

6.1 创建存储过程 用户首先创建存储过程以实现特定的功能，然后可在程序中调用该存储过程执行。 1．使用企业管理器创建视图 【例5-63】在School数据库中，创建存储过程proc_Course， 查询所有课程信息。 1）打开企业管理器，展开School数据库文件夹，右单击“存储过程”，在弹出的快捷菜单上选择“新建存储过程”命令，打开新建存储过程对话框。 2）将“[OWNER]”和“[PROCEDURE NAME]”参数分别替换成存储过程所有者的名称和存储过程的名称。默认为dbo和[PROCEDURE NAME]。

3）从“文本”列表框的第二行开始输入存储过程的T-SQL语句。这里输入的T-SQL语句仅有一条: SELECT * FROM T_Course。 4）单击【语法检查】按钮可以检查创建存储过程的T-SQL语句的语法是否正确。单击【另存为模板】按钮可将所创建的存储过程设置为模板。 5）完成后，单击【确定】按钮即创建和保存了存储过程proc_Course。 图5-64 创建存储过程示例

2．使用T-SQL语句创建存储过程 可以直接在SQL查询分析器中定义和调用存储过程。 常用存储过程的语法格式： CREATE PROC[DURE] 存储过程名 {@形式参数 数据类型}[VARYING][=默认值][OUTPUT] AS SQL语句1 … SQL语句n 说明： “形式参数”名称必须符合标识符规则；OUTPUT表示该参数是可以返回的，可将信息返回调用者；如果有多个参数，可以依次按以上参数定义规则列出，用逗号“,”隔开。

【例5-64】在School中创建一个的存储过程proc_SearchStudent,查询指定学生的选课情况。 CREATE PROC proc_SearchStudent @stcode char(8) AS SELECT T_Student.StudentName,T_Course.CourseName FROM T_Student JOIN T_Grade JOIN T_Course ON T_Grade.CourseCode=T_Course.CourseCode ON T_Student.StudentCode=T_Grade.StudentCode WHERE T_Student.StudentCode=@scode

采用“参数=值”的形式，各个参数的顺序可以任意排列。 6.2 执行存储过程 存储过程定义后，可通过EXECTE语句来执行。 语法格式： EXEC[UTE] 存储过程名[[@形参=]实参值|@变量[OUTPUT]|[DEFAULT]] 1)“@形参”是创建存储过程时定义的形参名； 2)“实参值”是输入参数的值； 3)“@变量”表示用来保存参数或者返回参数的变量；OUTPUT表示指定参数为返回参数； 4)DEFAULT表示使用该参数的默认值作为实参。 5)如有多个参数，可依次按以上参数定义规则列出，用逗号“，”隔开。 调用【例5-63】创建的存储过程proc_Course的语句为： EXEC proc_Course 调用【例5-64】创建的存储过程proc_SearchStudent，查询‘05101101’学生的选课情况。 EXEC proc_SearchStudent '05101101' 或 EXEC proc_ SearchStudent @scode='05101101' 实参顺序和定义时的参数顺序一致。 采用“参数=值”的形式，各个参数的顺序可以任意排列。

【例5-65】在School中创建一个的存储过程proc_SearchStudentAvgGrade，查询某个学生的选课数目和平均成绩。 本例是带一个输入参数和二个输出参数的存储过程，在定义输出参数时要用OUTPUT进行说明。 CREATE PROC proc_SearchStudentAvgGrade @stcode char(8), @stcount int OUTPUT,@stavg int OUTPUT AS SELECT @stcount=COUNT(T_Grade.StudentCode),@stavg=AVG(T_Grade.Grade) FROM T_Grade WHERE T_Grade.StudentCode=@stcode GROUP BY T_Grade.StudentCode 调用存储过程proc_SearchStudentAvgGrade，查询‘05101101’学生的选课数目和平均成绩。 DECLARE @scount int ,@savg int EXEC proc_SearchStudentAvgGrade '05101101',@scount OUTPUT,@savg OUTPUT PRINT ‘学生’+ ‘05101101’+‘的选课数目为’+CAST(@scount AS char(2))+'门' PRINT ‘学生+ ’05101101‘+’的平均成绩为'+STR(@savg,5,2)+'分

6.3 查看、修改和删除存储过程 用户可以利用企业管理器或T-SQL语句来查看、修改和删除用户创建的存储过程。 1．使用企业管理器创建视图 （1）查看和修改存储过程 在企业管理器中，展开存储过程所属的数据库，单击“存储过程”文件夹，在右边窗格中就可看到所有存储过程； 右击相应的存储过程名，在弹出的快捷菜单上选择“属性”命令，打开存储过程属性窗口，出现和创建存储过程相似的窗口，在中间的文本编辑子窗口中可以查看或修改该存储过程的文本。 （2）删除存储过程 右击要删除的存储过程名，在弹出的快捷菜单上选择“删除”命令，弹出“除去对象”对话框。单击【显示相关性】按钮可查看该存储过程所依附的对象，从而了解删除该存储过程对数据库的影响。单击【全部除去】按钮，即删除了相应的存储过程。

6.4 触发器 触发器是一种特殊的存储过程。触发器的创建主要用来维护数据表中的数据一致性，当对数据表进行插入、删除、更新等操作时，触发器可自动执行。 SQL Server提供了两种类型的触发器： （1）AFTER触发器：是在表中数据被修改之后才被触发，触发器对变动的数据进行检查，如果发现错误，将拒绝或回滚变动的数据。 （2）INSTEAD OF触发器：在数据修改以前被触发，并取代修改数据的操作，转去执行触发器定义的操作。

6.4.1 创建触发器 1．使用企业管理器创建触发器 【例5-68】在School数据库中创建一个简单的触发器tri_StudentInsDel，当用户插入或删除T_Student表中学生记录时，能自动显示表中的内容。 1）展开School“数据库/表”文件夹，在右边窗格中表中，右击T_Student表，在弹出的快捷菜单上选择“所有任务/管理触发器”命令，打开触发器属性对话框。

当对表T_Student的数据进行插入或删除操 作时，触发器“tri_StudentUpdate”将会自动执行。 2）系统创建触发器语句了CREATE TRIGGER的框架。修改框架中的内容为： CREATE TRIGGER tri_StudentInsDel ON [dbo].[T_Student] FOR INSERT, DELETE AS SELECT * FROM T_Student 3）单击【语法检查】按钮可以检查创建触发器的T-SQL语句的语法是否正确。 4）单击【确定】按钮即保存和创建了名称为“tri_StudentInsDel”的触发器。 当对表T_Student的数据进行插入或删除操 作时，触发器“tri_StudentUpdate”将会自动执行。

２．使用T-SQL创建触发器 触发器语法格式： CREATE TRIGGER 触发器名 ON 表名|视图名 FOR [INSERT][,UPDATE][,DELETE]} AS SQL语句段 【例5-69】在School数据库的T_Student表上创建一个触发器tri_StudentCodeUpdate，当对学号列进行修改时，给出提示信息并取消修改操作。 CREATE TRIGGER tri_StudentCodeUpdate ON T_Student FOR UPDATE AS DECLARE @text varchar(50) IF UPDATE(StudentCode) BEGIN SET @text='学生数据被修改!!!' RAISERROR(@text,16,1) ROLLBACK TRANSACTION END RAISEERROR(@text,16,1)语句的作用是返回用户定义的错误信息,并给出与信息关联的严重级别(1～18)和有关错误调用状态的信息(1～127 ) 。这里分别取“16”和“1”。

【例5-70】在School数据库的T_Grade表上创建一个触发器tri_Grade，当向T_Grade表中插入一条记录时，检查该记录的学号是否在T_Student表中，如果不存在则取消插入操作,否则显示“插入操作成功完成”。 CREATE TRIGGER tri_Grade ON T_Grade FOR INSERT AS DECLARE @text varchar(50) IF EXISTS(SELECT * FROM inserted WHERE inserted.StudentCode NOT IN (SELECT StudentCode FROM T_Student)) BEGIN SET @text='学生的学号不存在，将取消该插入操作 ' RAISERROR(@text,16,1) ROLLBACK TRANSACTION END Else SET @text='插入操作成功完成' SELECT @text

6.4.2 查看、修改和删除触发器 1）在企业管理器，展开“表”文件夹，右击触发器所属的表，在弹出的快捷菜单上选择“所有任务/管理触发器”命令，打开触发器属性对话框。在名称栏可选择已建立的触发器。修改方法同创建方法相同。 2）如要删除，单击【删除】按钮，在弹出的确认对话框中单击【是】，即删除了所选择的触发器。