触发器与存储过程的区别-白红宇

触发器与存储过程的区别

阅读量：5965 次

发布时间：2019-06-19

本文共 6948 字，大约阅读时间需要 23 分钟。

触发器原理：

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

触发器的作用：

触发器的主要作用是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。它能够对数据库中的相关表进行级联修改，强制比CHECK约束更复杂的数据完整性，并自定义操作消息，维护非规范化数据以及比较数据修改前后的状态。与CHECK约束不同，触发器可以引用其它表中的列。在下列情况下使用触发器实现复杂的引用完整性；强制数据间的完整性。创建多行触发器，当插入，更新、删除多行数据时，必须编写一个处理多行数据的触发器。执行级联更新或级联删除这样的动作。级联修改数据库中所有相关表。撤销或者回滚违反引用完整性的操作，防止非法修改数据。

触发器与存储过程的区别：

触发器与存储过程的主要区别在于触发器的运行方式。存储过程必须有用户、应用程序或者触发器来显示的调用并执行，而触发器是当特定时间出现的时候，自动执行或者激活的，与连接用数据库中的用户、或者应用程序无关。当一行被插入、更新或者删除时触发器才执行，同时还取决于触发器是怎样创建的，当UPDATE发生时使用一个更新触发器，当INSERT发生时使用一个插入触发器，当DELETE发生时使用一个删除触发器。

触发器类型

1.1 DML触发器

Oracle可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

1.2替代触发器

由于在Oracle里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是Oracle8专门为进行视图操作的一种处理方法。

1.3系统触发器

Oracle8i 提供了第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的事件中进行触发，如Oracle系统的启动与关闭等。

创建触发器

创建触发器的一般语法是:

CREATE [ OR REPLACE]TRIGGER trigger_name

[ BEFORE|AFTER ]trigger_event ON table_reference

[ FOR EACH ROW [WHEN trigger_condition] ]

trigger_body;

当一个基表被修改( insert,update,delete)时要执行的内嵌过程。执行时根据其所依附的基表改动而自动触发,因此与应用程序无关,用数据库触发器可以保证数据的一致性和完整性.

每张表最多可建立 12 个触发器,它们是:

before insert

before insert for each row

after insert

after insert for each row

before update

before update for each row

after update

after update for each row

before delete

before delete for each row

after delete

after delete for each row

3 创建DML触发器

触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。

触发器的限制

触发器有下面一些限制：

。触发器中不能使用控制语句 COMMIT,ROLLBACK, SVAEPOINT 语句；

。由触发器所调用的过程或函数也不能使用控制语句；

。触发器中不能使用LONG,LONG RAW 类型；

。触发器所访问的表受到远表的约束限制，即后面的“变化表”。

问题:当触发器被触发时,要使用被插入,更新或删除的记录中的列值,有时要使用操作前,

后列的值.

实现: :new 修饰符访问操作完成后列的值

:old 修饰符访问操作完成前列的值

例1: 建立一个触发器,当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去.

/**********************************************************************/

/* 功能 : 当员工表数据被删除时,记录被删除的记录。 */

/* 文件名 : del_emp.sql */

/* 作者 : 赵元杰 2001.5.31 */

/**********************************************************************/

create or replace trigger scott.del_emp

before delete on scott.emp for each row

begin

-- 将修改前数据插入到日志记录表 del_emp, 以供监督使用。

insert into emp_his( deptno , empno, ename , job ,mgr , sal , comm , hiredate )

values( :old.deptno, :old.empno, :old.ename , :old.job,

:old.mgr, :old.sal, :old.comm, :old.hiredate );

end;

show errors

4 创建替代(Instead_of)触发器

Instead_of 用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：

--节选自在线代码 instead.sql

CREATE VIEW room_summary AS

SELECT building,sum(number_seats) total_seats

FROM rooms GROUP BY building;

在此视图中直接删除是非法的：

SQL>DELETE FROM rooms_summary WHERE building=’Building 7’;

DELETE FROM rooms_summary WHERE building=’Building 7’;

ERROR at line 1：

ORA-01732：data manipulation operation not legal on this view

但是我们可以创建Instead_of 触发器来为 DELETE 操作执行所需的处理，即删除rooms 表中所有基准行：

--节选自在线代码 instead.sql

CREATE TRIGGER room_summary_delete

INSTEAD OF DELETE ON room_summary

FOR EACH ROW

BEGIN

-- 删除表 room 中行，这些行构成单个视图行。

DELETE FROM rooms WHERE building = :old.building;

END room_summary_delete;

5 创建系统触发器

Oracle8i提供的系统触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE ,ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：

CREATE OR REPLACE TRIGGER [sachema.] trigger_name

{BEFORE|AFTER}

{ddl_event_list|database_event_list}

ON { DATABASE | [schema.] SCHEMA }

[ when_clause] trigger_body;

ddl_event_list: 一个或多个DDL 事件，事件间用 OR 分开；

database_event_list: 一个或多个数据库事件，事件间用 OR 分开；

下面给出系统触发器的种类和事件出现的时机（前或后）：

事件	允许的时机	说明
启动	之后	实例启动时激活
关闭	之前	实例正常关闭时激活
服务器错误	之后	只要有错误就激活
登录	之后	成功登录后激活
注销	之前	开始注销时激活
创建	之前，之后	在创建之前或之后激活
撤消	之前，之后	在撤消之前或之后激活
变更	之前，之后	在变更之前或之后激活

系统触发器可以在数据库级（database）或模式（schema）级进行定义。数据库级触发器在任何事件都激活触发器，而模式触发器只有在指定的模式的触发事件发生时才触发。

例：建立一个当用户USERA登录时，自动记录一些信息的触发器：

CREATE OR REPLACE TRIGGER loguserAconnects

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO example.temp_table

VALUES(1,’LogUserAConnects fired!’);

END loguserAconnects;

例：建立一个当用户USERB登录时，自动记录一些信息的触发器：

CREATE OR REPLACE TRIGGER loguserAconnects

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO example.temp_table

VALUES(2,’LogUserAConnects fired!’);

END loguserBconnects;

例：建立一个当所有用户登录时，自动记录一些信息的触发器：

CREATE OR REPLACE TRIGGER logALLconnects

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO example.temp_table

VALUES(3,’LogUserAConnects fired!’);

END logALLconnects;

SQL>connect usera/usera

Connected.

SQL>connect userb/userb

Connected.

SQL>connect scott/tiger

Connected.

SQL>select * from temp_table;

Num_COL CHAR_COL

-------------- --------------------------------

3 LogALLConnects fired!

2 LoguserBConnects fired!

3 LogALLConnects fired!

1 LoguserAConnects fired!

6 触发器触发次序

Oracle 对事件的触发共有16种，但是它们的触发是有次序的，基本触发次序如下：

1) 执行 BEFORE语句级触发器;

2) 对与受语句影响的每一行：

a) 执行 BEFORE语句行级触发器

b) 执行 DML语句

c) 执行 AFTER行级触发器

3）执行 AFTER语句级触发器

7 使用触发器谓词

ORACLE 提供三个参数 INSERTING，UPDATEING，DELETING 用于判断触发了哪些操作。谓词的行为如下：

谓词	行为
INSERTING	如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE
UPDATING	如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE
DELETING	如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE