oracle触发器

目录
1.触发器简介
2.触发器类型
   2.1语句触发器
   2.2行触发器
   2.3instead of触发器
   2.4系统事件与用户事件触发器
3.触发器组成与注意事项
   3.1触发器组成
   3.2触发器注意事项
4.创建触发器
   4.1触发器触发次序
   4.2语句触发器和行触发器基本要点
   4.3创建语句触发器
   4.4创建行触发器
   4.5创建instead of触发器
   4.6创建系统事件触发器
   4.7创建用户事件触发器
5.触发器谓词
6.触发器的重新编译
7.启用/禁用触发器
8.删除触发器
9.触发器信息

1.触发器简介 

触发器是数据库常用对象之一，触发器的主要部分是代码块，一旦创建了触发器，在条件成立时，代码块将自动执行。触发器的好处在于，用户只需建立触发器，而无需对其进行任何人为控制，数据库将会精确地完成触发器任务。在结构上，触发器非常类似于存储过程，都是为实现特殊的功能而执行的代码块。不过，触发器不允许用户显示传递参数，不能够返回参数值，也不允许用户调用触发器。触发器只能由oracle在合适的时机自动调用。

2.触发器类型 

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行，即触发器是当某个事件发生时自动地隐式运行。触发器按照触发事件类型和对象不同，可以分为以下几类：语句触发器、行触发器、instead of触发器、系统事件触发器和用户事件触发器。

 2.1语句（表级）触发器

语句触发器是指建立在表或视图上的、由表的特定操作触发的触发器。这些操作可以是insert、update或者delete。语句触发器是表级触发器，即无论操作影响了多少行数据，语句触发器只被调用一次。

 2.2行触发器 

与语句触发器不同，行触发器在表的每行上进行操作时，都会激活并执行一次代码。行触发器必须包含for each row字句，并且可以引用每一行上的数据。因此，行触发器可以实现的功能更加精细和强大。

 2.3替代（instead of）触发器 

语句触发器和行触发器与触发动作（如insert、update、delete)之间是一种附属关系。触发器依赖于触发动作，但触发动作本身所执行的操作仍然被执行。二者的效果是叠加起来作用于数据表的。而instead of触发器则用于代替触发动作，例如，insert动作的触发器不再进行insert操作，而是转而执行触发器动作。instead of触发器可以看做行级触发器，而不必使用for each row进行限制。另外instead of触发器虽然可以访问new引用，但是不能修改引用的值。

 2.4系统事件与用户事件触发器 

系统事件是指oracle数据库本身的动作所触发的动作。这些事件主要包括：数据库启动、数据库关闭、系统错误等。用户事件是指相对于用户所执行的表（视图）等DML操作而言的。常见的用户事件包括：create事件、truncate事件、drop事件、alter事件、commit事件和rollback事件。系统事件与用户事件触发器并非常用触发器。

3.触发器组成与注意事项 

 3.1触发器组成：

（1）触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、
                              DDL语句（CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统
                              启动或退出、异常错误）、用户事件（如登录或退出数据库）。
（2）触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。
（3）触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。
（4）触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。
（5）触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，
                              使其执行触发操作。
（6）触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
                      语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；
                      行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

 3.2触发器注意事项

  编写触发器时，需要注意以下几点：
（1）触发器不接受参数。
（2）一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
（3）在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。
（4）触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。
（5）在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。
（6） 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，
          触发器也被提交、回退了。
（7） 在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。
（8）在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。
（9）不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

4.创建触发器 

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

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

说明：
   -- 触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能
     相同。
  -- BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是
     在执行触发事件之后触发当前所创建的触发器。
  -- FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操作影响数据库中的
     多行数据 时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，
     当它符合约束条件时，激活一次触发器。 当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 
     触发器则只能为行触发器。
  -- REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称
     分别为NEW和OLD。 触发器的操作（PL/SQL）块中应用相关名称时，必须在它们之前加冒号(:)，但在触发器描述语句（如WHEN子句）
     中则不能加冒号。
  -- WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。
     WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。
  -- 当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，
     用数据库触发器可以保证数据的一致性和完整性。

 4.1触发器触发次序

    1. 执行 BEFORE语句级触发器;
    2. 对与受语句影响的每一行：
          -- 执行 BEFORE行级触发器
          -- 执行 DML语句
          -- 执行 AFTER行级触发器 
    3. 执行 AFTER语句级触发器

 4.2语句触发器和行触发器基本要点

 （1）触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务
                               规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。
（2）触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的
                              组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。
（3）条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要
                              使用ORACLE提供的如下条件谓词：INSERTING、UPDATING、DELETING。
（4）触发对象：指定触发器是创建在哪个表、视图上。
（5）触发类型：是语句级还是行级触发器。
（6）触发条件： 由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。
（7）当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值。
                   实现:  :NEW 修饰符访问操作完成后列的值
                              :OLD 修饰符访问操作完成前列的值

 4.3创建语句触发器

 --利用触发器记录修改表的日志
create or replace trigger tr_employee_log
before insert or update or delete
on t_employees
begin
  insert into employee_log values(user,sysdate);
end;

 4.4创建行触发器

--利用触发器实现在表t_employees中插入新的数据时，employee_id的值保持顺序递增
create or replace trigger tr_before_insert_employee
before insert 
on t_employees
for each row    --说明创建的是行级触发器
begin
  select (max(employee_id)+1) into :new.employee_id from t_employees;
end;

 4.5创建instead of触发器

创建instead of触发器的一般语法是：

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

说明：
    -- INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式
      和数据库建立INSTEAD OF 触发器。
   -- INSTEAD OF 触发器则为行触发器。

创建INSTEAD OF触发器需要注意以下几点：
（1）只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
（2）不能指定BEFORE 或 AFTER选项。
（3）FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
（4）没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器（语句触发器或行触发器）就可以了。

/*利用instead of触发器实现修改视图vw_employee_salary中的列total来修改员工的实际工资，
工资的差额按月平均分摊到表t_salary中的salary列*/
create or replace trigger tr_update_employee_salary
instead of update
on vw_employee_salary
begin
  declare totalmonth number;
  begin
    select count(distinct month) into totalmonth 
    from t_salary
    where employee_id= :old.employee_id;
    
    update t_salary
    set salary=salary + (:new.total-:old.total)/totalmonth
    where employee_id= :old.employee_id;
  end;
end;

 4.6创建系统事件触发器

--创建登陆触发器
create or replace trigger tr_logon
after logon
on database  --表明触发器的作用对象为数据库
begin
  insert into log_event(user_name,address,logon_date)
  values ((ora_login_user, ora_client_ip_address, systimestamp);
end;

 4.7创建用户事件触发器

用户事件触发器的作用对象一般是user.schema,即将触发器建立在该用户及用户所拥有的所有对象之上。

--创建一个针对用户truncate操作的触发器
create or replace trigger tr_system_truncate
after truncate
on system.schema
begin
  insert into truncate_log 
  values (ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;

5.触发器谓词 

对于激活触发器的动作，oracle提供了谓词，来判断触发动作的类型。常用的谓词包括：inserting、updating和deleting。这三种谓词都会返回一个布尔值，以表示激活动作是否为insert（插入）、update（更新）和delete（删除）。某个触发器被激活时，oracle总是自动更新这三种谓词的值，需要注意的是，三种谓词只能有一个为真，因为每次触发器被激活总是由于某个特定动作。
       谓词                 行为
     INSERTING     如果触发语句是 INSERT语句，则为TRUE,否则为FALSE
     UPDATING      如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE

     DELETING      如果触发语句是 DELETE语句，则为TRUE,否则为FALSE

--用触发器来记录数据表的更新日志，以表示用户进行了怎样的更改数据库操作
create or replace trigger tr_employee_log
before insert or update or delete
on t_employees
begin
  if inserting then
    insert into employee_log values(user,sysdate,'插入数据');
  end if;
  
  if updating then 
    insert into employee_log values(user,sysdate,'更新数据');
  end if;
  
  if deleting then 
    insert into employee_log values(user,sysdate,'删除数据');
  end if;
end;

6.触发器的重新编译 

如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。

重新编译触发器：
ALTER TRIGGER [schema.] trigger_name COMPILE

7.启用/禁用触发器 

启用和禁用触发器是必要的，触发器一旦创建，默认有效，那么每个相应的条件都会激活触发器。但需要注意的是，触发器的执行也是会耗费大量系统资源的，尤其是针对大数据表的行级触发器。以建立在某个表上的before insert类型的行级触发器为例，触发器所执行的操作有可能超过了insert操作本身所耗费的资源。当执行大数据量的插入时，这些数据又被认为无须经过触发器操作，例如数据库迁移。此时需要用到禁用触发器，当数据库插入完毕，再次启用触发器即可。

禁用触发器：
ALTER TIGGER trigger_name DISABLE;

启用触发器：
ALTER TIGGER trigger_name ENABLE;

8.删除触发器 

删除触发器：
DROP TRIGGER trigger_name;
此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。

9.触发器信息 

为了获得当前触发器的信息，可以利用视图USER_OBJECTS和USER_TRIGGERS。
另，触发器相关数据字典：ALL_TRIGGERS、DBA_TRIGGERS