Oracle触发器的作用、应用场景、语法和实例讲解

触发器

概述：数据库触发器是一个与表相关联的、存储的PL/SQL程序。触发时机--每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时(事件启动)，Oracle自动地执行触发器中定义的语句序列。

触发器作用(应用场景)

（1）数据确认

（2）实施复杂的安全性检查

（3）做日志记录，跟踪表上所做的数据操作等

（4）数据的备份和同步；

语法：

CREATE  [or REPLACE] TRIGGER  触发器名
   {BEFORE | AFTER}
   {DELETE | INSERT | UPDATE [OF 列名]}
   ON  表名 -- 对哪个表进行操作
   [FOR EACH ROW [WHEN(条件) ] ]
declare
    -- 定义语句
begin
    -- PLSQL 块 
end ;

触发器的类型(常见)

语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。

行级触发器(for each row) ：触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。

需求1：当我往emp表中插入一条数据后，打印语一句话

创建触发器(测试窗口)

-- 说明：创建一个简单的触发器,绑定哪个表的哪些操作
create OR REPLACE trigger mytri after insert on mytable
DECLARE
-- 可以省略
BEGIN
  --注意：如果没输出 记得打开屏幕输出开关set serveroutput on; 
  --监听：插入的操作(给予提示)
  dbms_output.put_line('你插入了一个员工');   
end mytri; --表示触发器创建完毕(一定要带上表名)

执行(sql窗口)

--(1)创建一个表 
CREATE TABLE mytable(nb NUMBER,st VARCHAR(20));
-- DROP TABLE mytable;
--说明：在触发器创建以后,再插入数据
insert into mytable values(1000,'abc3');  --当我插入数据后,就会触发,触发器
SELECT *FROM mytable;

需求2：公司禁止在非工工作时间，往数据库里面插入数据

分析：首先我们得判断出什么是非工作时间，举例如下：

 to_char(sysdate,'day') in ('星期六','星期日');              --这个是非工作时间
 to_number(to_char(sysdate,'hh24')) not between 9 and 17;  --不在9到17点之间都是非上班时间,not between and 不在什么之间

说明：按照某种格式转换成字符串。

然后下面我们写触发器，因为我们不用监听每插入一行数据就触发一次，只要对表进行操作，就触发那我们就用语句级触发器。

触发器

create or replace TRIGGER mytesttri before insert on mytable     
declare
 begin
      -- 判断如果是非工作时间对表操作,就抛出异常,给出提示
    if  to_char(sysdate,'day') in ('星期六','星期日') OR 
      -- 周天24h的格式
         to_number(to_char(sysdate,'hh24')) not between 9 and 17
            then
      -- 禁止对表操做,我们可以调用一个系统提供的函数,进行提示
      -- 参1 固定的错误码 参数2 提示信息
          Raise_application_error(-20001,'非工作时间禁止操作');  
       end if;     
 end;

执行

--创建一个表 
CREATE TABLE mytable(nb NUMBER,st VARCHAR(20));
DROP TABLE mytable;
--准备插入数据
insert into mytable values(1000,'abc3');  --当我插入数据后,就会触发,触发器
SELECT *FROM mytable;

结果：数据没有插入进去。

第 1 行出现错误:
ORA-20000: 非工作时间禁止操作
ORA-06512: 在 "SCOTT.MYTRIGGER", line 3
ORA-04088: 触发器 'SCOTT.MYTRIGGER' 执行过程中出错

需求3：给员工涨后的工资

分析：涨工资不能小于涨前的工资,，也就是说在update工资的时候只能加不能减；同时因为涨工资是对每一个人都要操作，所以也就是每一行记录都有影响，所以我们就要用行级触发器（FOR EACH ROW）， 每操作一行就触发一次。

触发器

create or replace trigger mytrigger2 BEFORE update on emp for each row
begin
    --if 涨后的薪水 < 涨前的薪水 then
    -- :new表示影响后的新数据,:old表示影响之前的旧数据
  if :new.sal < :old.sal then   
    -- 注意错误码不要跟上面一个触发器例子的错误码重复了
    raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水.涨前:'||:old.sal||'   涨后:'||:new.sal);    
  end if; 
end;

测试

-- 测试一下 我们减一下工资，就会触发 我们写的触发器
update emp set sal=sal-1;  --但我更新工资时 就会报错