触发器和存储过程

一、触发器

1.什么是触发器（trigger）

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。

触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。

触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。

此外触发器是逻辑电路的基本单元电路，具有记忆功能，可用于二进制数据储存，记忆信息等。

 

2.触发器4个基本组成：

(1)触发事件：即在什么事件发生时触发，常见事件如 insert  ,update,delete;

(2)触发时间：这里的时间是相对于事件发生前后来说的，可以定义在事件发生之前和事件发生之后 before | after

(3)触发器本身：这是关键，也就是我们为什么要创建这个触发器，要用它做什么，触发器的主体，一段pl/sql程序块

(4)触发器频率：说明触发器内，在事件发生时，触发的频率，常见有语句级触发器和行级触发器，语句级就是该条语句执行一次，触发器触发一次，而行级触发器则是每操作一行就执行一次触发器，定义时需要添加（for each row）

 

3.触发器的语法：

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE| AFTER}

{INSERT| UPDATE| DELETE}

ON table_name

[FOR EACH ROW]

[WHERE CONDITION]

BEGIN

  执行体;

END;

4.触发器的应用

第一步：创建操作目标表t_target_table

Create table t_target_table(

id varchar(255),

account varchar(255),

pwd varchar(255),

name varchar(255));

第二步：创建操作记录日志表t_record_log

Create tablet_record_log(

id varchar(255),

Target_idvarchar(255),

Target_pwd varchar(255),

Opration varchar(255),

Date date);

第三步：创建触发器t_trigger

注意：声明的s变量将会被赋值为if语句中的s的值并插入记录表中

触发器实例一：

create or replace TRIGGER t_trigger after insert or update or delete on t_target_table

 DECLARE s t_record_log.opration %type;

 begin

  if inserting then

 s:= '添加操作';

  elseif inserting then

 s:= '删除操作';

elseif inserting then

 s:= '修改操作';

  end if ;

insert into t_record_log values(user,s,to_char(SYSDATE(),'yyyy-mm-dd hh mm ss'));

end;

触发器实例二：

二、存储过程

1. 什么是存储过程

  存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的sql语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程时数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

2. 存储过程的分类

2.1 系统存储过程

　　以sp_开头，用来进行系统的各项设定取得信息相关管理工作。

2.2 本地存储过程

　　用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

2.3 临时存储过程

　　分为两种存储过程：

1. 本地临时存储过程。以＃字号作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它
2. 全局临时存储过程，以两个#字号（##）号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限

2.4 远程存储过程

　　在sql server2005中，远程存储过程Remote Stored Procedured是位于远程服务器上的存储过程。通常可以使用分布式查询和Execute命令执行一个远程存储过程。

2.5 扩展存储过程

基本语法：

1.创建存储过程

Create procedure 存储过程名

@[参数名][类型],@[参数名][类型]

As

Begin

Select avg(grade) from course where cno=’c1’

End;

2. 执行存储过程

Exec 存储过程名；

Excute 存储过程名；

3. 调用存储过程：

存储过程可以在三种环境下被调用：

1. command命令下，基本语法为：exec sp_name[参数名]
2. Sql环境下，基本语法为：call sp_name[参数名]；
3. Plsql环境下,基本语法为：begin sp_name[参数名]end；

4.删除存储过程：

1. 基本语法：drop procedure 存储过程名；
2. 注意事项：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。