Oracle超详细笔记10--触发器

一、触发器概述

1.触发器的概念与作用

触发器是一种特殊类型的存储过程，编译后存储在数据库服务器中。

当特定事件发生时，由系统自动调用执行，而不能由应用程序显式地调用执行。

触发器不接受任何参数。

触发器主要用于维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束，并对数据库中特定事件进行监控和响应。

 

2.触发器的类型

DML触发器

建立在基本表上的触发器，响应基本表的INSERT，UPDATE，DELETE操作。

INSTEAD OF触发器

建立在视图上的触发器，响应视图上的INSERT，UPDATE，DELETE操作。

系统触发器

建立在系统或模式上的触发器，响应系统事件和DDL（CREATE，ALTER，DROP）操作。

 

3.触发器组成

触发器由触发器头部和触发器体两个部分组成，主要包括：

作用对象：触发器作用的对象包括表、视图、数据库和模式。

触发事件：激发触发器执行的事件。如DML、DDL、数据库系统事件等。

触发时间：用于指定触发器在触发事件完成之前还是之后执行。如果指定为AFTER，则表示先执行触发事件，然后再执行触发器；如果指定为BEFORE，则表示先执行触发器，然后再执行触发事件。

触发级别：触发级别用于指定触发器响应触发事件的方式。默认为语句级触发器，即触发事件发生后，触发器只执行一次。如果指定为FOR EACH ROW，即为行级触发器，则触发事件每作用于一个记录，触发器就会执行一次。

触发条件：由WHEN子句指定一个逻辑表达式，当触发事件发生，而且WHEN条件为TRUE时，触发器才会执行。

触发操作：触发器执行时所进行的操作。

 

二、DML触发器

1.DML触发器的种类及执行顺序

DML触发器的种类

语句级前触发器

语句级后触发器

行级前触发器

行级后触发器

2.DML触发器的执行顺序

如果存在，则执行语句级前触发器。

对于受触发事件影响的每一个记录：

如果存在，则执行行级前触发器；

执行当前记录的DML操作（触发事件）；

如果存在，则执行行级后触发器。

如果存在，则执行语句级后触发器。

 

3.创建DML触发器

语法

CREATE [OR REPLACE] TRIGGER trigger_name

BEFORE|AFTER triggering_event [OF column_name]

ON table_name]

[FOR EACH ROW]

[WHEN trigger_condition]

DECLARE

     /*Declarative section is here */

BEGIN

     /*Exccutable section si here*/

EXCEPTION

     /*Exception section is here*/

END [trigger_name];

 

4.语句级触发器

在默认情况下创建的DML触发器为语句级触发器，即触发事件发生后，触发器只执行一次。

 

例：

创建一个触发器，禁止在休息日改变雇员信息

CREATE OR REPLACE TRIGGER trg_emp_weekend

BEFORE INSERT OR UPDATE OR DELETE

ON emp

BEGIN

   IF TO_CHAR(SYSDATE, 'DY', 'nls_date_language=

                         american') IN('SAT', 'SUN')

  THEN

      raise_application_error(-20000, 'Can''t operate

                                               in weekend. ');

  END IF;

END trg_emp_weekend;

 

例：

为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数。

CREATE OR REPLACE TRIGGER trg_emp_dml

AFTER INSERT OR UPDATE OR DELETE ON emp

DECLARE

  v_count NUMBER;

  v_sal   NUMBER(6,2);

BEGIN

  IF INSERTING THEN

    SELECT count(*) INTO v_count FROM emp;

    DBMS_OUTPUT.PUT_LINE(v_count);

  ELSIF UPDATING THEN

    SELECT avg(sal) INTO v_sal FROM emp;

    DBMS_OUTPUT.PUT_LINE(v_sal);

  ELSE

    FOR v_dept IN (SELECT deptno,count(*) num FROM emp

                                                   GROUP BY deptno) LOOP

      DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num);

    END LOOP;

  END IF;

END trg_emp_dml;

 

5.行级触发器

行级触发器是指执行DML操作时，每操作一个记录，触发器就执行一次，一个DML操作涉及多少个记录，触发器就执行多少次。(书本P211,例12-2.)

在行级触发器中可以使用WHEN条件，进一步控制触发器的执行。

在行级触发器中引入了:old和:new 两个标识符，来访问和操作当前被处理记录中的数据。

标识符

:old和:new作为triggering_table%ROWTYPE类型的两个变量

在不同触发事件中，:old和:new的意义不同

触发事件	:old	:new
INSERT	未定义，所有字段都为NULL	当语句完成时，被插入的记录
UPDATE	更新前原始记录	当语句完成时，更新后的记录
DELETE	记录被删除前的原始值	未定义，所有字段都为NULL

引用方式：

      :old.field和:new.field （执行部分）

       old.field 和new.field   (WHEN条件中)

注意事项：

是伪记录，不能作为整个记录进行赋值或引用

不能传递给带triggering_table%ROWTYPE参数的过程和函数

如果触发器是建立在嵌套表上，:old和:new都执行嵌套表的行，:parent指向父表中的当前行。

例：

为emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。

CREATE OR REPLACE TRIGGER trg_emp_dml_row

BEFORE INSERT OR UPDATE OR DELETE ON emp

FOR EACH ROW

BEGIN

   IF INSERTING THEN

         DBMS_OUTPUT.PUT_LINE(:new.empno||' '||

                                                :new.ename);

   ELSIF UPDATING THEN

         DBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);

   ELSE

         DBMS_OUTPUT.PUT_LINE(:old.empno||' '||

                                                :old.ename);

   END IF;

END trg_emp_dml_row;

 

在行级触发器中，可以使用WHEN子句进一步控制触发器的执行。

例：

修改员工工资时，保证修改后的工资高于修改前的工资。

CREATE OR REPLACE TRIGGER trg_emp_update_row

BEFORE UPDATE OF sal ON emp

FOR EACH ROW

WHEN(new.sal<=old.sal)

BEGIN

    RAISE_APPLICATION_ERROR(

                    -20001,'The salary is lower!');

END trg_emp_update_row;

 

三、INSTEAD OF触发器

特点

只能定义在视图上

Instead-of触发器只能是行级触发器

Instead-of 触发器由DML操作激发，而DML操作本身并不执行

作用

修改一个本来不可以修改的视图

修改视图中某嵌套表的列

如果视图中包含下列任何一项，则该视图不可修改

集合操作符（UNION，UNION ALL，MINUS，INTERSECT）；

聚集函数（SUM，AVG等）；

GROUP BY，CONNECT BY或START WITH子句；

DISTINCT操作符；

涉及多个表的连接操作。

创建INSTEAD OF触发器的基本语法

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF triggering_event [OF column_name]

ON view_name

FOR EACH ROW

[WHEN trigger_condition]

DECLARE

      /*Declarative section is here */

BEGIN

      /*Exccutable section si here*/

EXCEPTION

      /*Exception section is here*/

END [trigger_name];

 

例：

创建一个包括员工及其所在部门信息的视图empdept，然后向视图中插入一条记录（2345,’TOM’,3000,’SALES’）。

 

CREATE OR REPLACE VIEW empdept

AS

SELECT empno,ename,sal,dname

FROM emp,dept WHERE emp.deptno=dept.deptno  

WITH CHECK OPTION;

 

INSERT INTO empdept

VALUES(2345, 'TOM',3000, 'SALES');

*

ERROR 位于第 1 行:

ORA-01733: 此处不允许虚拟列

CREATE OR REPLACE TRIGGER trig_view

INSTEAD OF INSERT ON empdept

FOR EACH ROW

DECLARE

    v_deptno dept.deptno%type;

BEGIN

    SELECT deptno INTO v_deptno

    FROM dept WHERE dname=:new.dname;

    INSERT INTO emp(empno,ename,sal,deptno)   

    VALUES(:new.empno,:new.ename,v_deptno,:new.sal);

END trig_view;

再向视图中插入一条记录（2345,’TOM’,3000,’SALES’）。

INSERT INTO empdept

VALUES(2345, 'TOM',3000, 'SALES');

已创建1行。

 

四、系统触发器

1.触发事件

DDL（模式）事件

CREATE，ALTER，DROP，RENAME，GRANT，REVOKE，COMMENT，TRUNCATE等。

触发时间可以是BEFORE，也可以是AFTER。

数据库事件

STARTUP，SHUTDOWN，SERVERERROR，LOGON，LOGOFF等。

 

2.创建系统触发器

语法

CREATE [OR REPLACE] TRIGGER trigger_name

BEFORE|AFTER ddl_event_list|database_event_list

ON DATABASE|SCHEMA

[WHEN trigger_condition]

DECLARE

     /*Declarative section is here */

BEGIN

    /*Executable section is here*/

EXCEPTION

    /*Exception section is here*/

END [trigger_name];

说明

基于数据库（DATABASE）的触发器，只要系统中该触发事件发生，且满足触发条件，则触发器执行；

对于基于模式（SCHEMAN）的触发器，只有当特定模式中的触发事件发生时，触发器才执行。

注意

STARTUP和SHUTDOWN事件只能激发基于数据库的触发器。

 

例：

将每个用户的登录信息写入temp_table表中。

CREATE OR REPLACE TRIGGER log_user_connection

AFTER LOGON ON DATABASE

BEGIN

  INSERT INTO scott.temp_table

  VALUES (user,sysdate);

END log_user_connection;

 

例：

当数据库中执行CREATE操作时，将创建的对象信息记录到ddl_creations表中。

CREATE TABLE ddl_creations (

     user_id       VARCHAR2(30),

     object_type    VARCHAR2(20),

     object_name   VARCHAR2(30),

     object_owner  VARCHAR2(30),

     creation_date  DATE);

 

CREATE OR REPLACE TRIGGER log_creations

AFTER CREATE ON DATABASE

BEGIN

    INSERT INTO ddl_creations

    VALUES(ora_login_user, ora_dict_obj_type,

         ora_dict_obj_name, ora_dict_obj_owner, sysdate);

END log_creations;

 

五、变异表触发器

概念

变异表是指激发触发器的DML语句所操作的表，即触发器为之定义的表，或者由于DELETE CASCADE操作而需要修改的表，即当前表的子表。

约束表是指由于引用完整性约束而需要从中读取或修改数据的表，即当前表的父表。

 

六、触发器的管理

触发器名称

触发器存在于单独的名字空间中，在一个模式中可以与其他对象同名

 

触发器的限制

不能出现任何事务控制语句。因为触发器作为触发语句执行的一部分，处于同一个事务中。

一个事件最多创建12个触发器。

触发器的大小不能超过32K。

 

激活或禁用触发器

可以激活或禁用某个触发器。语法为

ALTER TRIGGER triggername ENABLE|DISABLE;

 

激活或禁用某个表对象上的所有触发器。语法为

ALTER TABLE table_name ENABLE|DISABLE ALL  TRIGGERS;

 

修改触发器

CREATE OR REPLACE TRIGGER trigger_name

 

重新编译触发器

ALTER TRIGGER trigger_name COMPILE;

 

查看触发器及其源代码

查询数据字典视图USER_TRIGGERS

SELECT trigger_name,trigger_type,

table_name,trigger_body FROM user_triggers;

 

删除触发器

当触发器不再需要时，可以使用DROP TRIGGER语句删除触发器。

DROP TRIGGER trigger_name;