Oracle触发器trigger详解

触发器相关概念及语法

概述

本篇博文中主要探讨以下内容：

什么是触发器
触发器的应用场景
触发器的语法
触发器的类型
案例

数据：

触发器的概念和第一个触发器

数据库触发器是一个与表相关联的，存储的PL/SQL 语句。

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

举个简单的例子：

当员工表中新增一条记录后，自动打印“成功插入新员工”

end insertStaffHint;

触发器的应用场景

复杂的安全性检查
数据的确认
数据库审计
数据的备份和审计

触发器的语法

其中：

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

FOR EACH ROW选项说明触发器为行触发器。

行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。
当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器
。

REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

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

每张表最多可建立12 种类型的触发器：

BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW

触发器的类型

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

语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。

当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，
而INSTEAD OF 触发器则只能为行触发器。

其他规则

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

DML触发器的限制：

CREATE TRIGGER语句文本的字符长度不能超过32KB；
触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句。
触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK,SVAEPOINT 语句；
由触发器所调用的过程或函数也不能使用数据库事务控制语句；
触发器中不能使用LONG,LONG RAW 类型；
触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据；

DML触发器基本要点：

触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。
触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。
条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。
1）.INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
2）.UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE 时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。
3）.DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。
解发对象：指定触发器是创建在哪个表、视图上。
触发类型：是语句级还是行级触发器
触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。

触发器应用

触发器应用一 复杂的安全性检查

禁止在非工作时间插入数据

declare
-- local variables here
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 addStafffCheck;

用于在plsql使用程序中自定义不正确消息。
该异常只好在数据库端的子程序（流程、函数、包、触发器）中运用，而无法在匿名块和客户端的子程序中运用。
语法为raise_application_error(error_number,message[,[truefalse]]); 其中 error_number用于定义不正确号，
该不正确号必须在-20000到-20999之间的负整数； message用于指定不正确消息，并且该消息的长度无法超过2048字节；

触发器应用二

工资不能越涨越少

分析： 首先确认下触发器的类型， 因为需要对每一条数据进行确认，所以是个行级触发器。 需要for each row

create or replace trigger checkSalary
before update
on xgj_test
for each row
declare
-- local variables here 没有变量声明的话，declare可以省略
begin

--- if 涨后的薪水 < 涨前的薪水 then 如何表示呢 ？
if :new.sal < :old.sal then

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

触发器应用三

创建基于值的触发器

end addrecord;

触发器应用四

当给员工涨完工资后，自动备份到备份表中

begin

update xgj_test_bak set sal = :new.sal where username = :new.username ;

end databack;

oracle中还有个利用快照备份，是异步的。 而利用触发器，是同步的。

其他

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

限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

限定只对部门号为80的记录进行行触发器操作。

IF :NEW.commission_pct < :old.commission_pct THEN
RAISE_APPLICATION_ERROR(-20002,'部门80的人员的奖金不能降');
END IF;
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20003,'不能删除部门80的人员记录');
END CASE;
END;

/
实例：
UPDATE employees SET salary = 8000 WHERE employee_id = 177;
DELETE FROM employees WHERE employee_id in (177,170);
/

利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。

在触发器中调用过程。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持编程之家。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!