Oracle触发器-imooc

一、触发器相关概念及语法

1-1概述

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

1-2触发器的概念和第一个触发器

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

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

命令窗口中实现个简单的例子：

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

create or replace trigger saynewemp   after insert on emp     for each row declare   -- local variables here begin

     dbms_output.put_line('新增员工成功');

end ;

/

命令窗口实现效果：

SQL> set serveroutput on; SQL> insert into emp(empno,ename,sal,deptno) values(8889,'chenyy',1.2,10); 新增员工成功 1 row inserted

SQL> commit; Commit complete

1-3触发器的应用场景

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

1-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选项说明触发器为行触发器。当省略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)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

触发器的类型

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

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

每张表最多可建立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 

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

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

二、触发器应用

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

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

在命令行查询时间

SQL> select sysdate from dual; SYSDATE ----------- 2019/4/26 1

SQL> select to_char(sysdate,'day') from dual; TO_CHAR(SYSDATE,'DAY') ---------------------- 星期五

SQL> select to_number(to_char(sysdate,'hh24')) from dual; TO_NUMBER(TO_CHAR(SYSDATE,'HH2 ------------------------------                             14

在程序/命令窗口编译触发器

/** 非工作时间（星球六 星期日， 非9点~18点的区间） 禁止写入数据

语句级触发器：不管插入了多少条数据，没有必要对每一行数据都进行校验，只要不在这个时间段内，都不让插入。 */

create or replace trigger addStaffCheck before insert on emp begin     --判断当前时间是否是双休或者是上午九点到下午6点     if to_char(sysdate,'day') in ('星期六','星期天') or     to_number(to_char(sysdate,'hh24')) not between 9 and 18 then     --禁止插入,抛出错误码和错误信息。     raise_application_error(-20001,'禁止在非工作时间插入新员工！');     end if; end;

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

触发器应用二 数据确认

工资不能越涨越少，涨后的薪水不能低于涨前的薪水

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

create or replace trigger salary_check before update on emp for each row begin  --:new 表示更新后的列 :old 表示更前的列   if :new.sal<:old.sal then      raise_application_error(-20001,'涨后的薪水不能低于涨前的薪水！涨前的薪水为'||:old.sal||'涨后的薪水为'||:new.sal);   end if; end;

/

触发器应用三 数据库审计 创建基于值的触发器:给员工涨工资,当加薪超过1000块钱时审计该员工的信息

第一步：创建审计表信息，用于保存审计信息 create table audit_info(infomation varchar(200)); 第二步创建审计信息触发器 create or replace trigger salary_audit  after update  on emp for each row begin    --涨薪超过1000元进行审计     if :new.sal - :old.sal > 1000 then        dbms_output.put_line('工资增加超过1000，进入审计');         insert into audit_info(infomation) values('员工号为:'||:new.empno||'工资涨到'||:new.sal);     end if; end;

2-4触发器应用四 数据的备份和同步

使用触发器实现对emp的备份(给员工涨完工资后自动更新新的数据到备份表中)

第一步 创建emp的备份表 create table emp_bak as select * from emp; 第二步 创建备份使用到触发器

create or replace trigger salary_sync after update on emp for each row --给员工涨完工资后，自动备份到备份表中 begin     update emp_back set sal=:new.sal where empno=:new.empno;     dbms_output.put_line('已同步更新'); end;

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

其他

建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去 CREATE TABLE emp_delinfo AS SELECT * FROM EMP WHERE 1=2; CREATE OR REPLACE TRIGGER emp_del    AFTER DELETE     ON scott.emp    FOR EACH ROW    BEGIN    --将修改前数据插入到日志记录表 ,以供监督使用。    INSERT INTO emp_delinfo(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 );     dbms_output.put_line('删除成功'); END;

DELETE emp WHERE empno=7788; DROP TABLE emp_delinfo; DROP TRIGGER emp_del;

限定只对部门号为80的记录进行行触发器操作。 CREATE OR REPLACE TRIGGER tr_emp_sal_comm BEFORE UPDATE OF salary, commission_pct        OR DELETE ON HR.employees FOR EACH ROW WHEN (old.department_id = 80) BEGIN  CASE      WHEN UPDATING ('salary') THEN         IF :NEW.salary < :old.salary THEN

           RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');         END IF;      WHEN UPDATING ('commission_pct') THEN

        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;

更多详细介绍：http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html

来源：http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html,http://www.imooc.com/article/263650