Oracle中的触发器(trigger)用法及解读_Oracle

1、触发器的定义

数据库触发器是一个与表相关联、存储pl/sql语句的“东西”。

每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，oracle自动执行触发器中定义的语句序列。

例如：当员工信息插入后，自动输出“插入成功”的信息。

create or replace trigger emptrigger
 after insert on emp 
 for each row
declare
 -- 这里存放本地变量
begin
 dbms_output.put_line('插入成功！');
end emptrigger;

2、触发器的语法

上面是一个触发器简单的例子，我们接下来看下触发器的语法：

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;

其中：

（1）before和after指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

（2）for each row选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个dml语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略for each row 选项时，before和after触发器为语句触发器，而instead of触发器则只能为行触发器。

（3）referencing子句说明相关名称，在行触发器的pl/sql块和when子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为old和new。触发器的pl/sql块中应用相关名称时，必须在它们之前加冒号(:)，但在when子句中则不能加冒号。

（4）when子句说明触发约束条件。condition为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用pl/sql函数。when 子句指定的触发约束条件只能用在before和after行触发器中，不能用在instead of行触发器和其它类型的触发器中。

（5）当一个基表被修改（insert、 update、delete）时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

行触发器要求当一个dml语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；在行级触发器中，使用:old和:new伪记录变量，识别值的状态。语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。

3、触发器的其他注意事项

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

dml触发器的限制：

（1）create trigger语句文本的字符长度不能超过32kb。

（2）触发器体内的select语句只能为select … into结构，或者为定义游标所使用的select语句。

（3）触发器中不能使用数据库事务控制语句commit、rollback语句。

（4）由触发器所调用的过程或函数也不能使用数据库事务控制语句。

（5）触发器中不能使用long、long raw类型。

（6）触发器内可以参照lob类型列的列值，但不能通过 :new 修改lob列中的数据。

4、dml触发器基本要点

（1）触发时机：指定触发器的触发时间。如果指定为before，则表示在执行dml操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为after，则表示在执行dml操作之后触发，以便记录该操作或做某些事后处理。

（2）触发事件：引起触发器被触发的事件，即dml操作（insert、update、delete）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用or逻辑组合，不能使用and逻辑组合）。

（3）条件谓词：当在触发器中包含多个触发事件（insert、update、delete）的组合时，为了分别针对不同的事件进行不同的处理，需要使用oracle提供的如下条件谓词。

inserting：当触发事件是insert时，取值为true，否则为false。
updating [（column_1,column_2,…,column_x）]：当触发事件是update时，如果修改了column_x列，则取值为true，否则为false。其中column_x是可选的。
deleting：当触发事件是delete时，则取值为true，否则为false。

（4）解发对象：指定触发器是创建在哪个表、视图上。

（5）触发类型：是语句级还是行级触发器。

（6）触发条件：由when子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定updating后面的列的列表。

5、示例

（1）禁止在非工作时间插入数据。

create or replace trigger addempinfocheck
 before insert on emp_info 
declare
begin

 if to_char(sysdate, 'day') in ('星期六', '星期日') or
 to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then
 --禁止insert 
 raise_application_error(-20001,'非工作时间禁止插入数据！');
 end if;
end addempinfocheck;

raise_application_error用于在plsql使用程序中自定义不正确消息。

该异常只在数据库端的子程序（流程、函数、包、触发器）中运用，而无法在匿名块和客户端的子程序中运用。

语法为raise_application_error(error_number,message[,[truefalse]])。

其中error_number用于定义不正确号，该不正确号必须在-20000到-20999之间的负整数；message用于指定不正确消息，并且该消息的长度无法超过2048字节。

（2）涨薪后的工资应该大于涨薪前的工资。

create or replace trigger checksalary
 before update 
 on salary_info 
 for each row
declare
 --没有变量声明的话，declare可以省略
begin

 if :new.sal < :old.sal then
 raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水：'||:old.sal);
 end if;
end checksalary;

（3）创建基于值的触发器

create table xzw_test(info varchar2(256));

create or replace trigger adddata
 after update
 on xzw_test 
 for each row
declare
begin

 if :new.sal > 6000 then 
 insert into xzw_test values(:new.sal ||'-'|| :new.username ||'-'|| :new.job);
 end if;

end adddata;