数据库SQL Server 9-10 章（存储过程与触发器）

思维导图

一、存储过程

1.存储过程
存储过程：是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或者多个结果集以及返回值。

为什么要使用存储过程？
1）存储过程与其他应用程序共享应用程序逻辑
2）具有安全性和所有权连接，以及可以附加到他们的证书
3）提供安全机制
4）允许模块化程序设计
5）可以减少网络通信流量

存储过程的种类
用户自定义存储过程
系统存储过程
扩展存储过程

创建存储过程

CREATE PROC 存储过程名称
参数
Varying=default
With
Recompile
As
Sql_statement[…n]

修改存储过程

ALTER PROCEDURE procedure_name
参数
VARYING=default
With

删除存储过程

DROP PROCEDUER

设计存储过程的规则
1）可以引用在同一存储过程中创建的对象，只要引用时已创建了该对象即可 ；
2）可以在存储过程内引用临时表 ；
3）如果执行的存储过程将调用另一个存储过程，则被=调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内 ；
4）如果执行远程SQL实例进行更远的远程存储过程，则不但能回滚这些更改远程存储过程不参与事务处理 ；
5）存储过程中的参数的最大数目为2100 ；
6）存储过程中的局部变量的最大数目仅受可用内存的限制 ；
7）根据可用内存的不同，存储过程最大可达125mb。

二、触发器
触发器是一个在修改指定表中的数据时执行的存储过程。经常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性或一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以此确保数据的完整性。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。

为什么要使用触发器？
触发器自动执行，它们在表的数据作了任何修改（比如手工输入或者使用程序采集的操作）之后立即激活。
触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。
触发器可以强制限制，这些限制比用CKECK约束所定义的更复杂，与CHECK约束不同的是，触发器可以引用其他表中的列。

触发器种类
1）DDL触发器
DDL触发器当服务器或者数据库中发生数据定义语言（DDL事件时）将被调用。如果要执行以下操作，可以使用DDL触发器：
要防止对数据库架构进行某些更改；
希望数据库中发生某种情况以响应数据库架构中的更改
要记录数据库架构中的更改或者事件
2）DML触发器
DML触发器是当前数据库服务器中发生数据操作语言（DML）事件时要执行的操作，通常所说的DML触发器主要包括三种：INSERT触发器、UPDATE触发器、DELETE触发器。DML触发器可以查询其他表，还可以包含复杂的T-SQL语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误，则整个事务自动回滚。

创建INSERT触发器

CREATE TRIGGER trigger_name
On+表或者视图
{  
     For\after\instead of
     Delete,insert,update   
As
Sql_statement
}

创建DELETE触发器

Create trigger t_deleteteacher
On 教师信息
For delete
As 
Select .. as .. from deleted
触发：
Delete from .. where ..

创建update触发器

create trigger t_update 
on 教师信息
for update 
as
if(update(姓名)or update(性别))
begin     
print'事务不能被处理，基础数据不能修改！'
    rollback transaction
end
else    
print'数据修改成功！'

创建DDL触发器

Create trigger trigger_name
On  all server|detabase
With encryption(是否加密)
For |after{event_type}
As
Sql_statement

递归触发器
任何触发器都可以包含影响同一个表或另一个表的UPDATE、INSERT或DELETE语句。如果启用递归触发器选项，那么改变表中数据的触发器，通过递归执行就可以再次触发自己。在数据库创建时，默认情况下递归触发器选项是禁用的，但可以使用ALTER DATABASE语句来启用它。