触发器与存储过程的区别

 触发器原理：
          触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。
触发器的作用：
触发器的主要作用是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。它能够对数据库中的相关表进行级联修改，强制比CHECK约束更复杂的数据完整性，并自定义操作消息，维护非规范化数据以及比较数据修改前后的状态。与CHECK约束不同，触发器可以引用其它表中的列。在下列情况下使用触发器实现复杂的引用完整性；强制数据间的完整性。创建多行触发器，当插入，更新、删除多行数据时，必须编写一个处理多行数据的触发器。执行级联更新或级联删除这样的动作。级联修改数据库中所有相关表。撤销或者回滚违反引用完整性的操作，防止非法修改数据。
触发器与存储过程的区别：
         触发器与存储过程的主要区别在于触发器的运行方式。存储过程必须有用户、应用程序或者触发器来显示的调用并执行，而触发器是当特定时间出现的时候，自动执行或者激活的，与连接用数据库中的用户、或者应用程序无关。当一行被插入、更新或者删除时触发器才执行，同时还取决于触发器是怎样创建的，当UPDATE发生时使用一个更新触发器，当INSERT发生时使用一个插入触发器，当DELETE发生时使用一个删除触发器。

§1  触发器类型

§1.1 DML触发器

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

§1.2替代触发器

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

§1.3系统触发器 

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

§2  创建触发器

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

 

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!

        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

 

 

例

 

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

 

REM 选自:RSchange.sql

REM 作者: Scott Urman.

REM 中文注释：赵元杰

 

CREATE OR REPLACE TRIGGER LogRSChanges

  BEFORE INSERT OR DELETE OR UPDATE ON registered_students

  FOR EACH ROW

DECLARE

  v_ChangeType CHAR(1);

BEGIN

  /*  INSERT 用’I’, DELETE用’D’, UPDATE 用’U’ */

  IF INSERTING THEN

    v_ChangeType := 'I';

  ELSIF UPDATING THEN

    v_ChangeType := 'U';

  ELSE

    v_ChangeType := 'D';

  END IF;

 

  /* 在RS_audit 记录所有的改变，使用sysdate 来产生系统时间邮戳，

    使用 user 返回当前用户的标识  */

 

  INSERT INTO RS_audit

    (change_type, changed_by, timestamp,

     old_student_id, old_department, old_course, old_grade,

     new_student_id, new_department, new_course, new_grade)

  VALUES

    (v_ChangeType, USER, SYSDATE,

     :old.student_id, :old.department, :old.course, :old.grade,

     :new.student_id, :new.department, :new.course, :new.grade);

END LogRSChanges;

/

§8 删除和使能触发器

当触发器创建完成后，程序员和DBA管理员要经常关心数据库实例中的触发器的情况。对于不必需的触发器，要进行删除或使触发器无效，从而使系统的性能有所提高。

 

删除触发器的命令语法如下：

DROP TRIGGER  trigger_name;

 

例：从数据子字典中删除某个触发器：

SQL> select trigger_name from user_triggers;

 

TRIGGER_NAME

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

SET_NLS

 

SQL> drop trigger set_nls;

 

触发器已丢弃

 

使触发器无效的命令是ALTER TRIGGER，它的语法如下：

 

ALTER TRIGGER  triiger_name [DISABLE | ENABLE ];

如：

 

SQL> ALTER TRIGGER  updatemajorstats  DISABLE;

 

SQL> alter  table  students  disable  all  triggers;

 

 

§9创建触发器的限制

编写触发器程序时有些限制，希望程序人员注意下面的一些情况：

 

1.代码大小：

 

一般的触发器的代码大小必须小于32K；如果大于这个限制，可以将其拆成几个部分来写。

 

2.触发器中有效的语句：

 

可以包括DML SQL语句，但不能包括DDL 语句。ROLLBACK, COMMIT, and SAVEPOINT也不能使用。但是，对于“系统触发器（system triggers）”可以使用CREATE/ALTER/DROP  TABLE和Alter … COMPILE语句。

 

3. LONG, LONG RAW和LOB的限制：

 

 

 

l  不能插入数据到LONG或LONG RAW;

l  来自LONG或LONG RAW的数据可以转换成字符型（如CHAR和VARCHAR2），但是只允许32KB；

l  使用LONG或LONG RAW不能声明变量；

l  在LONG或LONG RAW列中不能用:NEW 和 :PARENT；

l  LOB中的:NEW变量不能修改，例如：

:NEW.Column := ...

 

4. 引用包变量的限制：

 

如果UPDATE或DELETE语句测到与当前的UPADTE冲突，则Oracle执行ROLLBACK到SAVEPOINT上并重新启动更新。这样可以要出现多次才能成功。