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;  --但我更新工资时 就会报错
(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐