SQLServer数据库之存储过程和触发器

1. 存储过程

存储过程是存储在 SQL Server 中的一种编译对象，它是一组用于完成特定功能的SQL 语句集，这些 SQL 语句经过编译后存储在数据库中，可被客户机或应用程序调用；
使用存储过程能够将经常使用的SQL语句封装起来，既保证安全性又避免输入重复代码；

1.1 存储过程的分类

存储过程根据定义的主体可分为以下3类：

1. 系统存储过程是由系统默认提供的存储过程，主要存储在 master 数据中并以 “sp_” 为前缀命名，主要功能是从系统表中获取信息；
2. 用户自定义存储过程是程序开发人员或数据库管理员为了完成某些特定功能而编写的存储过程，它能输入参数，向客户端返回结果等；
3. 扩展存储过程是用于扩展 SQL Server 服务器功能的存储过程，常以“xp_”为前缀命名；

1.2 创建存储过程

CREATE PROCEDURE就是创建存储的 SQL 命令，语法格式如下：

CREATE PROCEDURE <存储过程名>[;number]
[{@parameter <数据类型>} [=DEFAULT] [OUTPUT][,...N]]
[设定选项]
AS <SQL语句> 

命令说明：

1. <存储过程名> 为创建的存储过程的名称，命名规则和数据表名类似，可接一个整数 number，用来区分同名的存储过程；
2. @parameter表示存储过程的参数，在创建存储过程的语句中可声明一个或多个参数，当声明多个参数时用 “,”隔开；
3. <数据类型>指明参数的数据类型；
4. DEFAULT表示参数的默认值；
5. OUTPUT 表示参数时输出参数，设置此项能向调用者返回信息；
6. [设定选项]中还能表示其他设定选项；
7. AS 关键字指明改存储过程将要执行的操作；
8. <SQL语句>指明包含在该存储过程中的SQL语句；

例子 1：创建一个存储过程BR_B，用来查询数据表B中年龄大于20岁的人员信息

create procedure BR_B as
select * from B where Age>20

例子 2：创建一个过程 BR_CI ，用来向数据表 C 中插入一条数据，数据的内容由参数提供

create procedure BR_CI
(@CNo varchar(20),
@CN nvarchar(12),
@Credit nvarchar(12))as insert into C values(@CNo,@CN,@Credit)

1.3 查看存储过程

存储过程创建后，存储过程名将保存在系统表 sysobjects 中，源码存储在系统表 syscomments中；
可通过存储过程 sp_helptext来查看用户创建的存储过程信息，语法格式如下：

EXEC sp_helptext <存储过程名>

例子 3：查询数据库 test 中存储过程 BR_CI 的源代码

exec sp_helptext BR_CI

知识点： 在 SSMS 中查看存储过程：数据库 → test → 可编程性 → 存储过程，在展开的存储过程，右击dbo.BR_CI，选择修改，打开SQLQuery.sql 窗口，能看到创建改存储过程时的源代码；

1.4 删除存储过程

DROP PROCEDURE是删除过程的SQL命令，语法格式如下：

DROP PROCEDURE <存储过程名>

知识点： 在 SSMS 中删除存储过程：右击 dbo.BR_CI → 删除，删除对象对话框，确定；

2. 触发器

2.1 概述

触发器是一个特殊的存储过程，包含的SQL语句不需要手动调动，而是在满足一定条件后自动激活执行；
SQL Server 2016 支持3种触发器：DMI 触发器、DDL 触发器、LOGIN（登录）触发器；
DMI 触发器 能响应数据表上发生的 INSERT，UPDATE和DELETE操作，DDL 触发器能响应 CREATE，ALTER和DROP操作，LOGIN（登录）触发器 能响应用户在登录时的一些操作；

2.2 创建触发器

CREATE TRLGGER是创建触发器SQL 命令；

2.2.1 创建 DML 触发器的语法格式如下：

CREATE TRIGGER <触发器名>
ON {表名|视图名}
[WITH ENCRYPTION]
{FOR|AFTER|INSTEAD OF}
{[INSERT] [,] [UPDATE] [,] [DELETE]}
AS <SQL 语句>

命名说明：

1. <触发器名>表示创建触发器的名称，命名规则与数据表相似；
2. {表名|视图名}指定执行触发器的数据表或视图；
3. WITH ENCRYPTION：设置此项表示对触发器进行加密出来；
4. AFTER 表示触发器只有在 SQL 语句中指定的所有操作都执行成功后才触发； FOR 表示默认为 ALTER；
5. INSTEAD OF 指定执行的是触发器的内容而不是所触发的 SQL 语句，在使用了 WITH CHECK OPTION语句的视图上不能定义该项触发器；
6. [INSERT] [,] [UPDATE] [,] [DELETE] 指定能够激活触发器的操作，必须至少指定一个操作；
7. <SQL 语句>指明包含在该触发器中的 SQL 与，一般不向应用程序返回结果；

例子 4：设计一个触发器 BR_Q，当数据表 B 中删除一个学生的记录后，删除该学生在数据表 O 中的记录

create trigger BR_Q on B
after delete
as delete from O
where O.ID in(select ID from deleted)

注意： 例子 3 的DELETED表示删除记录的数据表，INSERED和 UPDATED 分别表示插入记录和修改记录的数据表；

2.2.2 创建 DDL 触发器的语法格式如下：

CREATE TRIGGER <触发器名>
ON {ALL SERVER|DATABASE}
[WITH ENCRYPTION]
{FOR|AFTER} {event_type}[,...N]
AS <SQL 语句>

命令说明：

• ALL SERVER 表示指定触发器的作用域为当前服务器；
• DATABASE表示指定触发器的作用域为当前数据库；
• event_type指定激活触发器的事件类型；

例子 5：创建一个触发器 BR_Stop，禁止修改和删除当前数据库中任何数据表

create trigger BR_stop
on database
for drop_table,alter_table
as print '不能修改或删除当前数据库中的数据表！'
rollback

注意： “PRINT ‘显示内容’” 表示输出一条文本信息，当执行修改或删除操作时出现在 “结果” 窗口中；ROLLBACK表示回滚，即将数据恢复到执行此项 SQL 语句之前；

2.3 查询触发器

可使用存储过程中查看触发器，语法格式如下：

EXEC sp_helptrigger '表名'[,'操纵类型']

表名是指触发器所在的数据表名称，操作类型指明某一类操作的触发器，若不指定，则列出所有触发器；
例子 6：查看数据表 B 中已创建的所有类型的触发器

exec sp_helptrigger 'B'

注意： 查看触发器文本的存储过程为 sp_helptrigger，查看触发器的所有者的存储过程为 sp_help；

2.4 修改触发器

1. ALTER TRIGGER为修改触发器基本信息的 SQL 命令，语法格式跟 CREATE TRIGER语句相同；
2. DISABLE TRIGGER为禁用触发器的 SQL 命令，语法格式如下：

DISABLE TRIGGER {<触发器名>[,...N]|ALL}
ON <作用域>

ALL指作用于全部触发器，<触发器名>是指该触发器发生作用的对象，如DATABASE；
例子 7：禁用触发器 BR_B

disable trigger BR_B

3. ENABL TRIGGER为启用触发器的 SQL 命令，语法格式如下：

ENABLE TRIGGER {<>[,...N]|ALL}
ON <作用域>

2.5 删除触发器

DROP TRIGGER为删除触发器的 SQL 命令，语法格式如下：

DROP TRIGGER <触发器名>

例子 8：删除触发器 BR_B

drop trigger BR_B

删除数据库触发器时，可能会出现因为不具备相关权限而无法使用 DROP命令删除触发器的情况；这时，可使用 SSMS 进行删除：数据库 → test → 可编程性 → 数据库触发器，右击 BR_stop → 删除，在打开删除对象的窗口，确认删除对象；

删除数据库表触发器的方法与上类似：数据库 → test → 表 →** db…B → 触发器 → BR_Q；