存储过程和触发器

 

谈到存储过程和触发器，没有开发经验的小鸟们，会认为存储过程和触发器是是很高深的内容。那么今天我就带着大家一起来认识SQL Server中的这两位重量级嘉宾。其实个人以为，尽量少用或者规避存储过程和触发器。

在学习存储过程之前，我们先来讨论下为什么需要存储过程。

 

为什么需要存储过程

    当今的软件大都应用于网络中，而一般应用程序所需数据都保存在数据库中。在没有使用存储过程的数据库应用程序中，用户所编写的应用程序都是从本地计算机(client)向服务器(server)端发送SQL代码来请求对数据库中数据的增删改差操作，服务器对接收到的SQL代码进行编译后执行，并将结果返回给client，再由客户端的软件处理后输出。如果开发者对服务器安全性考虑不周全，就会为黑客提供盗取数据的机会。其中SQL注入是一种常见的方式。

为了防止SQL注入过程泄露企业的商业机密，我们可以通过存储过程把对数据库操作的SQL代码预先编译好并保存在服务器端。这样既减少了网络传输量，又能保证应用程序的运行性能。

然后，我们来看下什么是存储过程

 

什么是存储过程

     存储过程(procedure)类似于C#语言中的方法，它是SQL语句和控制语句的预编译集合。存储过程保存在数据库里，可由应用程序调用执行。

说的简单一点，存储过程就像数据库中运行的方法

 

存储过程的优点：

1. 执行速度更快---因为存储过程是预编译过的
2. 模块化程序设计—类似方法的复用
3. 提高系统的安全性—防止SQL注入
4. 减少网络流量—只需传输存储过程的名称即可

 

存储过程分类：

   系统存储过程：

       系统存储过程提供了管理数据和更新表的机制，并充当从系统表中检索信息的快捷方式。

常用系统存储过程：

   

    用户自定义存储过程：

除了使用系统存储过程外，用户还可以创建自己的存储过程。

常见存储过程的语法：

CREATE  PROC[EDURE]  存储过程名

    @参数1  数据类型 = 默认值 OUTPUT,

    @参数n  数据类型 = 默认值 OUTPUT

    AS

      SQL语句

参数说明：

• 参数可选
• 参数分为输入参数、输出参数
• 输入参数允许有默认值

   如何执行存储过程

EXEC  过程名  [参数]

这里提示一点：如果存储过程存在输出参数，一定要写Exec

说了这么多，下面我们一起来写一个分页的存储过程

create proc GetPageList

@pageIndex int,--页码

@pageSize int,--页容量(每页显示几条记录)

@pageCount int output,--总页数

@rowCount float output--总行数

as

  select * from   

  (

     select ROW_NUMBER() over(order by studentNo) as num,* from Student

  ) as temp where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize

  select @rowCount=COUNT(*) from Student

  set @pageCount=ceiling(@rowCount/@pageSize)

 

  declare @pageCount int,@rowCount int

  exec GetpageList 2,5,@pageCount output,@rowCount output

select @pageCount

select @rowCount

 

怎么样，现在对存储过程有点感觉了吧！

接下来咱们简单看下触发器的相关知识

触发器

触发器的作用：自动化操作，减少了手动操作以及出错的几率

触发器是一种特殊类型的存储过程，它不同于前面介绍的一般的存储过程。一般的存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行

   触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。

一旦表  发生新增/修改/删除  操作，那么就来自动执行一行代码

常见的触发器

 

DML触发器：

Insert、delete、update(不支持select)  要执行的操作

After触发器(for)、instead of触发器(不支持before触发器)  操作的时间（什么时候操作）

After(for)：操作完成后才调用此触发器

Instead of ：操作完成前调用此触发器

可以对一张表创建多个触发器，但是一般不这样用

注意：instead of替换新增语句的操作，之后新增操作不再更新到数据表。

如果向数据库插入多条数据呢？

会触发多次

 

After触发器和instead of触发器的器区别

After触发器：

1. 在语句执行完毕之后触发
2. 按语句触发 ，而不是所影响的行数，无论影响多少行，只触发一次。，
3. 只能建立在常规表上，不能建立在视图和临时表上。
4. 可以递归触发，最高可达32级。

Instead of 触发器

01,用来替换原来的操作

02，不会递归触发

03，可以建在表和视图上

 指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。  

Inserted表与deleted表

Inserted表只能用在触发器中

 Inserted 表包含新数据

  Insert、update触发器会用到

 Deleted表包含旧数据

   Delete、update触发器会用到

常用语法

CREATE TRIGGER triggerName ON 表名

after(for)（for与after都表示after触发器）  |  instead of

 UPDATE|INSERT|DELETE（insert,update,delete）

AS

begin

…

end

接下来我们来看一个after触发器的例子

 --针对班级表的新增操作触发器

create TRIGGER tg_grade ON grade

after-- after(操作完成后才调用此触发器)

INSERT--UPDATE|INSERT|DELETE

AS

begin

--触发器代码

select * into studentbackup from inserted

 --select * from inserted--保存了引发新增触发器的新增数据,只能在触发器中访问

End

这个触发器的作用：当向grade表中新增一条数据时，会将刚插入的数据备份到studentbackup中，其中studentbackup表必须不存在！

好了，今天存储过程和触发器的知识就介绍到这里。相信有了本的基础，再去学习存储过程和触发器就异常简单了