【Oracle】触发器的相关知识

目录

触发器

（一）概念

（二）触发前提：在建立触发器之前，要对以下内容进行分析

（三）创建

（四）DML 触发器

（五）替代触发器

（六）DDL触发器

---

触发器

（一）概念

触发器是在特定事件出现的时候，自动执行的代码块。

触发器是自动隐式运行

触发器不能有参数

（二）触发前提：在建立触发器之前，要对以下内容进行分析

1. 触发事件：DML操作、DDL操作和系统事件等

2. 触发时间：before after

3. 触发对象：表、视图、用户和数据库

4. 触发级别：

语句级触发（表级触发）：无论操作影响了多少行，语句触发器只被调用一次

行级触发器：操作影响到的每一行，都会执行触发器。

5. 触发限制：一个逻辑表达式，当该表达式为真时，触发器被触发执行

（三）创建

语法：

• • Create or replace trigger <触发器名>
  • [before|after]
  • <触发事件> on <表名>
  • [for each row]
  • [when <条件表达式>]

<PL/SQL程序体>

（四）DML 触发器

根据触发器的四个应用场景确定的案例

1. 实施复杂的安全性检查：

例：禁止在非工作时间操作emp表

知识点：三个谓词：instering deleting updating

create or replace

trigger emptime2

before insert or delete or update on emp

declare

v_riqi varchar2(10);

v_hh number(2);

begin

  v_riqi:=to_char(sysdate,'day');

  v_hh:=to_number(to_char(sysdate,'hh24'));

  if(v_riqi in('星期六','星期日') or (v_hh>17 or v_hh<8)) THEN

    if inserting then

      raise_application_error(-20013,'非工作时间禁止插入');

    elsif deleting then

      raise_application_error(-20014,'非工作时间禁止删除');

    elsif updating then

      raise_application_error(-20015,'非工作时间禁止更新');

    else

      raise_application_error(-20016,'非工作时间禁止任何操作');

    end if;

  end if;

end;

验证：在emp表上做增删改操作，都会触发。

2. 数据完整性检查

例：修改后的工资不能比原工资低

知识点：两个伪记录变量 :old :new

这两个变量只在触发器内部有效，并且只有在DML触发表中字段时才有效。使用形式：:old.字段名和:new.字段名

:old和 :new  表示的是同一条记录。两个都是记录变量

:old 是这条记录被操作之前；:new 是这条记录被操作之后。

create or replace trigger checksal

before update

on emp

for each row

begin

  if :old.sal>:new.sal then

    dbms_output.put_line('涨前：'||:old.sal||'，涨后：'||:new.sal);

    RAISE_application_error(-20002,'涨后的工资不能小于涨前的工资');

   end if;

end;

验证：修改某员工的工资，使其工资值减少。

3. 数据库的审计

例：当工资超过6000元时，查看该员工的信息

先要创建表：Create table  audit_info(info varchar(200));

再创建触发器：

create or replace trigger shji_info

after update on emp

for each row

begin

  if :new.sal>6000 then

    insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);

  end if;

end;

验证：修改员工的工资为大于6000

4. 数据的备份和同步

例：员工涨工资后，会同步备份到备份表中 

先建立备份表：create table emp_back as select * from emp;

再建立触发器：

create or replace trigger copysal

after update on emp

for each row

begin

  update emp_back set sal=:new.sal where empno=:new.empno;

end;

验证：修改emp表中某员工的工资，再查看备份表中的数据

（五）替代触发器

解决多表视图的更新问题，替代视图完成对多个表中数据的修改。

替代触发器都是行级触发。因为触发操作不会执行，所以没有before和after

例：当向视图中插入新记录时，被认定为非法。试用触发器完成插入操作 

create or replace trigger view_insert_tigger

  instead of insert on v_emp20  

  for each row

declare

    v_empCount       NUMBER;

    v_deptCount      NUMBER;

begin

    --判断要增加的员工是否存在

    SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:NEW.empno;

        --判断要部门是否存在

    SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno;

--如果部门不存在

    IF v_deptCount=0 THEN

      INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);

    END IF;

  --如果员工不存在

  IF v_empCount=0 THEN

      INSERT INTO emp(empno,ename,job,sal,deptno)

      VALUES(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);

    END IF;

end ;

（六）DDL触发器

例：记录用户所有的DDL操作 

先建立日志表：

create table ddl_log(lname varchar2(30),obj_type varchar2(20),eventname rchar2(20),ddl_date date);

再创建触发器

create or replace trigger trig_ddl

after ddl on schema

begin

  insert into ddl_log values(ora_dict_obj_name,ora_dict_obj_type,sysdate,ora_sysevent);

end;