SQL Server Stored Procedure and Trigger
概述
存储过程（Stored Procedure）
是一组完成特定功能的Transact- SQL语句的集合，即将一些固定的操作集中起来由SQL Server服务器来完成，应用程序只需调用它就可以实现某个特定的任务。
存储过程是可以通过用户、其他存储过程或触发器来调用执行。SQL Serve 2016的存储过程分为服务器编译和本机编译的存储过程2类。
触发器（Trigger） 一种特殊的存储过程
触发器通常在特定的表上定义，当该表的相应事件发生时自动执行，用于实现强制业务规则和数据完整性等
存储过程
在SQL Serve 2016中，利用存储过程可以保证数据的完整性，提高执行重复任务的性能和数据的一致性。
存储过程在被调用的过程中，参数可以被传递和返回，出错代码也可以被检验。
应用场景：
存储过程主要应用于控制访问权限、为数据库表中的活动创建审计追踪、将关系到数据库及其所有相关应用程序的数据定义语句和数据操作语句分隔开。
目的：
- 提高了处理复杂任务的能力。
- 增强了代码的复用率和共享性。
- 减少了网络中数据的流量。
- 存储过程在服务器注册，加快了过程的运行速度。
- 加强了系统的安全性。
可以强制应用程序的安全性，参数化存储过程有助于保护应用程序不受SQL注入式攻击。
分类
SQL Server 2016支持的存储过程有如下4种类型。
-
系统存储过程
从物理意义上讲，系统存储过程存储在源数据库中，并且带有sp_ 前缀。
从逻辑上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys 构架中。 -
用户定义的存储过程
用户为了完成某一特定的功能，可以自己创建存储过程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言 (DDL) 和数据操作语言 (DML) 语句，然后返回输出参数。 -
临时存储过程
临时存储过程以“#”或“##”为前缀，分别表示局部临时存储过程和全局临时存储过程。当SQL Serve关闭后，所有临时存储过程将自动被删除。 -
扩展存储过程
扩展存储过程以xp_为前缀，是SQL Server 2016的实例可以动态加载和运行的 DLL。其使用方法与系统存储过程一样。
设计原则
用户创建存储过程时，应注意遵循以下几点原则。
-
存储过程最大不能超过128MB。
-
用户定义的存储过程只能在当前数据库中创建。
如果执行对远程SQL Server 2016实例进行更改的远程存储过程，则不能回滚这些更改，而且远程存储过程不参与事务处理。 -
存储过程是为了处理那些需要被多次运行的Transact-SQL语句集，所以不要为只运行一次的Transact-SQL语句集构建存储过程。
-
SQL Server允许在存储过程创建时引用一个不存在的对象，在创建的时候，系统只检查创建存储过程的语法。
在执行的时候，存储过程引用了一个不存在的对象，则这次执行操作将会失败。 -
存储过程可以嵌套使用。嵌套的最大层次可以用@@NESTLEVEL函数来查看。
-
可以在存储过程内引用临时表。
如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失。
系统存储过程
SQL Server 2016提供了许多系统存储过程，主要包括用于 SQL Server 数据库引擎的常规维护的数据库引擎存储过程，用于实现游标变量功能的游标存储过程、用于设置管理数据库性能所需的核心维护任务的数据库维护计划存储过程等多种，下面介绍几种常用的系统存储过程。
（1）sp_helpdb （2）sp_helptext
（3）sp_renamedb （4）sp_rename
（5）sp_helplogins （6）sp_helpsrvrolemember
| 名称 | 描述 |
|---|---|
| sp_helpdb | 报告有关指定数据库或所有数据库的信息 |
| sp_helptext | |
| sp_renamedb | 更改数据库的名称 |
| sp_rename | |
| sp_helptext | |
| sp_helplogins | |
| sp_helpsrvrolemember |
创建和执行
创建存储过程：
在SQL Server 2016中可以使用SSMS或CREATE PROCEDURE语句来创建存储过程，一般使用create procedure语句。
create procedure语句的基本语法格式如下：
CREATE PROC [ EDURE ] procedure_name [;number ]
[ { @parameter data_type }
[ = default ] [OUTPUT ] ] [ ,…n ]
[ WITH { RECOMPILE |ENCRYPTION } ]
AS sql_statament [ ,…n ]
注意：存储过程和视图不同，视图只能跟随一个select内容，存储过程可以有多个sql语句。
执行存储过程：
利用EXECUTE 语句可以执行存储过程。对于存储过程的所有者或任何一名对此过程拥有EXECUTE特权的用户，都可以执行此存储过程。
EXECUTE语句的语法格式如下：
[ EXEC[UTE]][@return_status =]
procedure_name [;number]
{[ [ @parameter1 = ] value
| [@parameter1]=@variable [ OUTPUT ] ] }..
[ WITH RECOMPILE ]
举例：
【例9.1】创建一个存储过程，输出所有学生的姓名、课程名称和期末成绩信息。
-- 创建
CREATE PROCEDURE Pstu_sc0
AS
SELECT sname, cname, final
FROM student s ,course c ,score sc
WHERE s.studentno=sc. studentno and c.courseno=sc.courseno
-- 执行
exec Pstu_sc0
【例9.2】创建一个存储过程，输出指定学生（有参） 的姓名及课程名称、期末成绩信息。
-- 创建
CREATE PROCEDURE Pstu_sc1
@student_name nchar(8)
AS
SELECT sname, cname, final
FROM student s ,course c ,score sc
WHERE s.studentno=sc. studentno and c.courseno=sc.courseno
and s.sname=@student_name
-- 执行
exec Pstu_sc1 @student_name='XXXXX'
【例9.3】创建一个存储过程，用输出参数（指针变量） 返回指定学生的所有课程的期末成绩的平均值。
-- 创建
CREATE PROCEDURE Pstu_sc2
@student_name nchar(8), @average numeric(6,2) OUTPUT
AS
SELECT @average=AVG(final)
FROM student s ,course c ,score sc
WHERE s.studentno=sc. studentno and c.courseno=sc.courseno
and s.sname=@student_name
-- 执行
declare @ave numeric(6,2)
EXEC Pstu_sc2
@student_name='赵望舒 ',
@average =@ave OUTPUT -- 利用output将average赋值给ave
select @ave --输出ave
【例9.4】创建一个存储过程，用输出参数返回指定学生的所有课程的期末成绩的平均值，若不指定学生姓名，则返回所有学生的所有课程的期末成绩的平均值。并查看期末考试低于70分的学生名单。
-- 创建
CREATE PROCEDURE Pstu_sc3
@student_name nchar(8)=NULL, @average numeric(6,2) OUTPUT
AS
SELECT @average =AVG(final)
FROM student s ,course c ,score sc
WHERE s.studentno=sc. studentno and c.courseno=sc.courseno
and (s.sname= @student_name or @student_name IS NULL)
--查看期末考试低于70分的学生名单
SELECT student.studentno,student.sname,score.courseno ,score.final
from student inner join score
ON student.studentno=score.studentno
WHERE score.final<70
go
-- 执行（使用默认的空值）
declare @ave numeric(6,2)
EXEC Pstu_sc3 @average =@ave OUTPUT
SELECT @ave
修改存储过程
在创建存储过程之后，用户可以使用SSMS图形工具或ALTER PROCEDURE语句来对其进行修改，一般使用alter procedure语句。
使用ALTER PROCEDURE语句修改存储过程
使用ALTER PROCEDURE语句可以修改用CREATE PROCEDURE语句创建的存储过程，但不会影响权限，也不影响相关的存储过程或触发器。
【例9.5】修改存储过程Pstu_sc0，使其以加密方式存储在系统表syscomments中。
ALTER PROCEDURE Pstu_sc0
WITH ENCRYPTION
AS
SELECT sname, cname, final
FROM student s ,course c ,score sc
WHERE s.studentno=sc. Studentno and c.courseno=sc.courseno
--使用系统存储过程sp_helptext显示存储过程的定义，其命令如下：
EXECUTE sp_helptext Pstu_sc0
触发器
触发器（Trigger）是一种响应数据操作语言（DML）事件或数据定义语言（DDL）事件而执行的特殊类型的存储过程，是在用户对某一表中的数据进行UPDATE、INSERT和DELETE操作时被触发执行的一段程序。
触发器有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。
分类
SQL Server 2016提供了两种类型的触发器：
- DML触发器
- DDL触发器。
DML触发器
DML触发器是在执行INSERT、UPDATE或DELETE语句时被激活的触发器。DML触发器又分为AFTER触发器、INSTEAD OF触发器和CLR触发器。
DML触发器
CLR触发器将执行在托管代码（在.NET Framework中创建并在SQL SERVER中加载的程序集的成员）中编写的方法，而不用执行Transact-SQL存储过程。 AFTER触发器是在激活它的语句成功执行完以后才被激活。而INSTEAD OF触发器的激活将替代相应的触发语句。
当数据库中发生数据操作语言（DML）事件时将调用 DML 触发器。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。
DML 触发器通常用于以下场合：
（1）DML 触发器可通过数据库中的相关表实现级联更改。
（2）DML 触发器可以防止恶意或错误的INSERT、UPDATE以及DELETE操作，并强制执行比CHECK约束定义的限制更为复杂的其他限制。
（3）与CHECK约束不同，DML 触发器可以引用其他表中的列。
（4）DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。
（5）一个表中的多个同类DML触发器（INSERT、UPDATE或DELETE）允许采取多个不同的操作来响应同一个修改语句。
DDL触发器
DDL触发器是在执行CREATE、ALTER和DROP语句时被激活的触发器，是由数据定义语言引起的。
注意：
仅在运行触发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。
适用场景：
（1）要防止对数据库架构进行某些更改。
（2）希望数据库中发生某种情况以响应数据库架构中的更改。
（3）要记录数据库架构中的更改或事件。
主要作用和工作原理
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。
能够对数据库中的相关表进行级联修改，强制比CHECK约束更复杂的数据完整性，并自定义错误消息，维护非规范化数据以及比较数据修改前后的状态。
与CHECK约束不同，触发器可以引用其他表中的列。
工作原理
创建触发器时：
在DML触发器的执行过程中，SQL Server为每个触发器创建和管理两个特殊的表，一个是插入表inserted表，一个是删除表deleted表。
这两个表建在数据库服务器的内存中，与触发器所在数据表的结构是完全一致。 对于这两个表，用户只有读取的权限，没有修改的权限。
激活相应触发器时：
- 当由INSERT或UPDATE语句激活相应触发器之后，所有被添加或被更新的记录都被存储到inserted表。
- 当由DELETE或UPDATE语句激活相应触发器之后，所有被删除的记录都被送到deleted表。
在触发器的执行过程中，可以读取这两个表中的内容，但不能修改它们。
当触发器的工作完成之后，这两个表也将从内存中删除。
创建和管理触发器
创建
创建触发器前应注意的问题
（1）CREATE TRIGGER 语句必须是批处理中的第一个语句，且只能用于一个表或视图。
（2）创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。
（3）触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
（4）不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。
（5）在含有用 DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF和 INSTEAD OF UPDATE触发器。
TRUNCATE TABLE 语句虽然类似于没有 WHERE 子句（用于删除行）的 DELETE 语句，但它并不会引发 DELETE 触发器，因为TRUNCATE TABLE语句没有日志记录。
与创建存储过程一样，触发器也可以通过SQL Server Management Studio和CREATE TRIGGER语句两种方法创建，一般使用create trigger语句。
创建DML触发器
使用CREATE TRIGGER语句创建DML触发器的语法格式如下：
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{{ { FOR | AFTER | INSTEAD OF } {[INSERT][ ,][UPDATE ] [,] [ DELETE] }
AS sql_statament [ ,…n ]
举例：
【例9.8】为student表创建一个触发器，用来禁止更新学号字段的值。
CREATE TRIGGER tri_stu
ON student
AFTER UPDATE
AS
IF UPDATE(studentno)
BEGIN
RAISERROR('不能修改学号',16,2) -- raiserror引发错误，提示用户
ROLLBACK
END
--若执行如下更新语句：
UPDATE student
SET studentno='17137221508'
WHERE studentno='18137221508'
--提示“不能修改学号”， 更新语句得不到执行。
【例9.9】为course表创建一个触发器，用来防止用户删除任何必修课程的课程记录。
CREATE TRIGGER tri_cour
ON course
INSTEAD OF DELETE
AS
IF EXISTS(SELECT * FROM course )
BEGIN
RAISERROR('不能删除必修课程',16,2)
ROLLBACK
END
--若执行如下删除语句：
DELETE FROM course WHERE type='必修'
--提示“不能删除必修课程”， 删除语句得不到执行。
【例9.10】为score表创建一个触发器，用来防止用户对score表中的数据进行任何修改。
CREATE TRIGGER Tri_sc
ON score
INSTEAD OF UPDATE
AS
RAISERROR('不能修改成绩表中的数据',16,2)
GO
--若执行如下更新语句：
UPDATE score SET final=60
--提示“不能修改成绩表中的数据”，更新语句得不到执行。
创建DDL触发器
DDL触发器是面向整个服务器或某个数据库的触发器，DDL触发器的触发不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，相反，它们将为了响应各种数据定义语言(DDL)事件而激发。
这些事件主要与以关键字CREATE、ALTER和DROP开头的Transact-SQL语句对应。 执行DDL式操作的系统存储过程也可以激发DDL触发器。
DDL触发器不能进行可视化操作，可以执行如图9-5所示的“新建数据库触发器（N）…”命令，在数据库触发器模板进行创建，也可以直接通过命令进行创建。如果展开“数据库触发器”子目录，就可以看到当前数据库中的已经创建的触发器。
【例9.11】为teaching数据库创建一个触发器，用来防止用户对数据库中的表进行任何删除。
TEACHING
GO
CREATE TRIGGER Soft_tables
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
PRINT '当前数据库禁止删除操作'
ROLLBACK TRANSACTION
END
DROP TABLE stu_course
修改
在创建触发器之后，用户可以使用SSMS或ALTER TRIGGER语句进行修改。
使用SQL Server Management Studio修改触发器：
操作步骤如下。
（1）在“对象资源管理器”中展开“数据库”子目录。
（2）选择触发器所在的数据库，如teaching数据库，并展开该数据库的“表”子目录。
（3）选择触发器所在的表score，展开表中的“触发器”子目录。
（4）右击要修改的触发器，在弹出的快捷菜单中选择“修改”命令。
（5）在弹出的触发器编辑窗口，用户可以直接进行修改。修改完毕，单击工具栏中的“！”按钮执行该触发器，将修改后的触发器保存到数据库中。
使用ALTER TRIGGER语句修改触发器
ALTER TRIGGER语句的语法格式的各参数的含义和CREATE TRIGGER语句中参数的含义相同。
【例9.12】使用ALTER TRIGGER语句修改触发器Tri_stu，用来禁止更新学号字段和姓名字段的值。
ALTER TRIGGER Tri_stu
ON student
AFTER UPDATE
AS
IF UPDATE(studentno) OR UPDATE(sname)
BEGIN
RAISERROR('不能修改学号或姓名',16,2)
ROLLBACK
END
GO
查看和删除
SQL Server 2016中查看触发器的方法有使用对象资源管理器查看触发器和使用使用系统存储过程查看触发器两种。
利用对象资源管理器查看触发器只要展开表、数据库或服务器对象下的触发器子目录即可。还可以右击具体的触发器名称，执行“修改”命令，实现对触发器的修改。
使用系统存储过程Sp_help或Sp_helptext可以查看触发器的命令：
Sp_helptext tri_stu_score
使用SSMS删除触发器
使用SQL Server Management Studio删除触发器的操作步骤与修改相近。只是在右击触发器时，在弹出的快捷菜单中选择“删除”命令，单击“确定”按钮，即可删除该触发器。
使用DROP TRIGGER语句删除触发器
删除触发器Tri_stu。
DROP TRIGGER Tri_stu
禁用和启用
可以使用SQL Server Management Studio或ALTER TABLE语句来禁用触发器。
使用SQL Server Management Studio禁用触发器：
参考步骤如下。
（1）在“对象资源管理器”中展开“数据库”子目录。
（2）选择触发器所在的数据库，如teaching数据库，并展开该数据库的“表”子目录。
（3）选择触发器所在的表score，展开“触发器”子目录。
（4）右击要禁用的触发器，在弹出的快捷菜单中选择“禁用”命令，弹出如图9-9所示的“禁用触发器”对话框。单击“关闭”按钮即可完成操作。
使用ALTER TABLE语句禁用触发器
使用ALTER TABLE语句也可以禁用触发器。其语法格式如下：
ALTER TABLE 表名
DISABLE TRIGGER trigger_name
例如禁用course表中的触发器Tri_stu 的命令如下。
ALTER TABLE course
DISABLE TRIGGER Tri_stu
启用触发器
可以使用SSMS或ALTER TABLE语句来启用触发器。使用SSMS启用触发器的操作步骤与禁用相近。在此不再赘述。
使用ALTER TABLE语句启用触发器语法格式如下：
ALTER TABLE 表名
ENABLE TRIGGER trigger_name
启用course表中的触发器delete_c_tr的命令如下：
ALTER TABLE course
ENABLE TRIGGER delete_c_tr
存储过程可以使用户对数据库的管理以及显示关于数据库及其用户信息的工作变得更容易。
触发器的常见应用
在SQL Server 2016中的触发器应用包括：
- 限制用户登录、用户访问时间
- 保护现有数据
- 实现较为复杂的级联操作等。
限制数据更改
前面的例题就是利用触发器限制表的数据更改，对表数据进行保护。
【例9】使用ALTER TRIGGER语句修改触发器Tri_stu，用来禁止更新学号字段和姓名字段的值。
ALTER TRIGGER Tri_stu
ON student
AFTER UPDATE
AS
IF UPDATE(studentno) OR UPDATE(sname)
BEGIN
RAISERROR('不能修改学号或姓名',16,2)
ROLLBACK
END
GO
限制工作时间
利用触发器限制用户的登录时间，可以实现对企业员工的非工作时间进行限制访问。
【例9.13】创建触发器Time_out，当用户rose登录时，只能在7:30~18:30的时间段内登录。
CREATE TRIGGER Time_out
ON ALL SERVER WITH EXECUTE AS 'rose'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'rose' AND CONVERT(CHAR(10),GETDATE(),108) BETWEEN '7:30:00' AND '18:30:00'
ROLLBACK
END
级联操作
利用触发器实现级联操作
在SQL Server 2016中可以通过触发器对关联表实现行级操作，实现触发器对表数据进行级联操作。
【例9.14】创建触发器tri_stu_score，对student表中学号进行删除时，同时删除score表中的相关成绩信息。
CREATE TRIGGER tri_stu_score
ON student
AFTER DELETE
AS
BEGIN
DELETE score WHERE studentno=(select studentno FROM deleted)
END
DELETE FROM student WHERE studentno='17122203567'
触发器是一种功能强大的工具，可以扩展SQL Server约束、默认值对象和规则的完整性检查逻辑，实施更为复杂的数据完整性约束。
学习本章过程中，要求掌握如下内容：
存储过程和触发器的基本概念
存储过程的创建和调用
能运用存储过程简化部分Transact-SQL语句
触发器的创建、修改和管理。
