• oracle数据库笔记1PL/SQL基础7触发器


    触发器是特定事件出现的时候,自动执行的代码块。
    类似于存储过程,但是用户不能直接调用他们
    
    功能:
    1、 允许/限制对表的修改
    2、 自动生成派生列,比如自增字段
    3、 强制数据一致性
    4、 提供审计和日志记录
    5、 防止无效的事务处理
    6、 启用复杂的业务逻辑
    
    
    
    create trigger biufer_empployees_department_id before
      insert or update of department_id 
      on employees
      referencing old as old_value new as new_value
      for each row
        when(new_value.department_id!=80)
    begin
        :new_value.commision_pct:=0;
    end;
    
    触发器的组成部分:
    1、 触发器名称
    2、 触发语句
    3、 触发器限制
    4、 触发操作
    
    
    1、 触发器名称
    create trigger biufer_employees_department_id
    命名习惯:biufer(before insert update for each row)
    employees 表名
    department_id 列名
    
    
    2、 触发语句
    表或视图上的dml语句
    ddl语句
    数据库关闭或启动,startup shutdown 等等
    
    before insert or
        update of department_id
      on employees
     referencing old as old_value
         new as new_value
     for each row
    
    
    1)规定了对employees表进行insert的时候  触发器会激活
    2)对employees表的department_id列进行update的时候  触发器会激活
    
    3、 触发器限制
    
    when (new_value.department_id<>80 )
    其中的new_value是代表更新之后的值
    
    限制不是必须的
    
    4、 触发操作
    
    是触发器的主体
    begin
     :new_value.commission_pct :=0;
    end;
    
    主体很简单,就是将更新后的commission_pct列置为0
    
    
    
    
    触发时机
    
    在dml触发器中,根据触发时机不同,可以分为before和after,但是在触发过程中其顺序不同。
    
    执行顺序为:
    1.before触发器 
    
    2.约束检查 
    
    3.更新表 
    
    4.after触发器
    
    Before触发器是在约束之前执行的
    通常用于:
    1.设置或修改更新或插入的列值
    2.检查复杂的安全规则,如限制时间
    3.增强商业应用规则
    4.可以通过触发器的逻辑潜在的引发一个异常来拒绝触发语句,则相对有效,因为是在约束前执行
    
    
    After触发器最后执行
    一般用于:
    1.用户信息的审计
    
    2.导出数据的生成
    3.远程数据的复制
    
    触发器类型:
    1、 语句触发器
    2、 行触发器
    3、 instead of 触发器
    4、 系统条件触发器
    5、 用户事件触发器
    
    1)语句触发器
    
    是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器
    
    能够与insert、update、delete或者组合上进行关联
    
    但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次
    
    比如,无论update多少行,也只会调用一次update语句触发器
    
    ey:需要对在表上进行dml操作的用户进行安全检查,看是否具有合适的特权
    create table foo(a number);
    create trigger biud_foo
      before insert or update or delete
       on foo
    begin
      if user not in (‘donny’) then
       raise_application_error(-20001, ‘you don’t have access to modify this table.’);
      end if;
    end;
    
    ey:对修改表的时间、人物进行日志记录
    1、 建立试验表
    create table employees_copy as select *from emp;
    2、 建立日志表
    create table employees_log(
      who varchar2(30),
      when date);
    
    3、 在employees_copy表上建立语句触发器,在触发器中填充employees_log 表
    create or replace trigger biud_employee_copy
      before insert or update or delete
      on employees_copy
    begin
      insert into employees_log(who,when)
      values( user, sysdate);
    end;
    
    4、 测试
    update employees_copy set salary= salary*1.1;
    
    select *from employess_log;
    
    5、 确定是哪个语句起作用?
    即是insert/update/delete中的哪一个触发了触发器?
    可以在触发器中使用inserting / updating / deleting 条件谓词,作判断:
    
    begin
      if inserting then
       -----
      elsif updating then
       -----
      elsif deleting then
       ------
      end if;
    end;
    
    
    if updating(‘col1’) or updating(‘col2’) then
      ------
    end if;
    
    
    a.修改日志表
    alter table employees_log add (action varchar2(20));
    
    
    b.修改触发器,以便记录语句类型
    create or replace trigger biud_employee_copy
      before insert or update or delete
      on employees_copy
     declare
      l_action employees_log.action%type;
     begin
      if inserting then
         l_action:=’insert’;
      elsif updating then
         l_action:=’update’;
      elsif deleting then
         l_action:=’delete’;
      else
        raise_application_error(-20001,’you should never ever get this error.’);
     
       end if;
    
      insert into employees_log(who,action,when)
      values( user, l_action,sysdate);
    end;
    
    c.测试
    update employees_copy set salary=50000 where employee_id = 12345;
    
    select *from employees_log
    ;
    
    2)行触发器
    
    是指为受到影响的各个行激活的触发器,定义与语句触发器类似,有以下两个例外:
    1、 定义语句中包含for each row子句
    2、 在for each row触发器中,用户可以引用受到影响的行值。
    
    create trigger biufer_empployees_department_id before
      insert or update of department_id 
      on employees
      referencing old as old_value new as new_value
      for each row
        when(new_value.department_id!=80)
    begin
        :new_value.commision_pct:=0;
    end;
    
    referencing 子句:
    执行dml语句之前的值的默认名称是 :old ,之后的值是 :new
    insert 操作只有:new
    delete 操作只有 :old
    update 操作两者都有
    
    referencing子句只是将new 和old重命名为new_value和old_value,目的是避免混淆。
    比如操作一个名为new的表时。
    
    ey:为主健生成自增序列号
    
    drop table foo;
    create table foo(id number, data varchar2(20));
    create sequence foo_seq;
    
    create or replace trigger bifer_foo_id_pk
     before insert on foo
     for each row
    begin
     select foo_seq.nextval into :new.id from dual;
    end;
    
    语句与行级触发器的区别?
    	行级触发器							语句级触发器
    被触发语句影响的每一行所触发					不管影响多少行,触发语句只执行一次
    若触发事件对行没有产生更改影响,则不触发			即使触发事件不影响任何一行,触发器也触发
    都可指定before,after						都可指定before,after
    
    触发器执行体可以读取行数据					触发器执行体与行数据无关
    只受当前触发事件影响						只受当前触发事件影响
    
    After行触发器锁定数据行						不锁定数据行
    
    同一表上触发器的优先级别
    同一表上可以定义多个触发器,最多可以创建12种类型的触发器,但是其触发是有固定顺序的,其先后顺序为:
    
    1.before语句级触发器
    
    2.before行级触发器
    
    3.after行级触发器
    
    4.after语句级触发器
    
    
    3)instead of 触发器更新视图
    
    create or replace view v_emp_dept as
     select empno, ename , hiredate, sal,deptno, dname from emp,dept
      where emp.deptno=dept.deptno;
    
    尝试往v_emp_dept表插入数据时只能通过替代触发器来完成
    
    create or replace trigger tr_v
    
      instead of insert on v_emp_dept
    
      for each row
    
    begin
      
    insert into dept values(:new.deptno,:new.dname);
      
    insert into emp (empno,ename,hiredate,sal) values(:new.empno,:new.ename,:new.hiredate,:new.sal);
    
    end;
    
    
    替代触发器的使用经验
    
    替代触发器可用来操纵对视图的插入,修改和删除。
    Before和after无法用于替代触发器
    
    视图上的check约束对替代触发器无效,因此需要在触发器语句内加强相关约束
    Dml触发器是在dml操作外运行的,而替代触发器则代替触发它的dml命令运行。本质上,替代触发器属于行级。
    
    4)系统事件触发器
    系统事件:数据库启动、关闭,服务器错误
    
    create trigger ad_startup
     after startup
      on database
    begin
     -- do some stuff
    end;
    
    5)用户事件触发器
    
    用户事件:用户登陆、注销,create / alter / drop / analyze / audit / grant / revoke / rename / truncate / logoff
    ey:记录删除对象
    a.日志表
    create table droped_objects(
    object_name varchar2(30),
    object_type varchar2(30),
    dropped_on date);
    
    b.触发器
    
    create or replace trigger log_drop_trigger
     before drop on donny.schema
    begin
     insert into droped_objects
      values(
          ora_dict_obj_name,  -- 与触发器相关的函数
        ora_dict_obj_type,
        sysdate);
    end;
    
    
    c.测试
    
    
    create table drop_me(a number);
    create view drop_me_view as select *from drop_me;
    drop view drop_me_view;
    drop table drop_me;
    
    
    select *from droped_objects
    
    
    
    禁用和启用触发器
    alter trigger <trigger_name> disable;
    alter trigger <trigger_name> enable;
    
    事务处理:
    在触发器中,不能使用commit / rollback
    因为ddl语句具有隐式的commit,所以也不允许使用
    
    触发器的限制
    触发器的使用存在一些限制条件,需要引起高度重视!
    
    1.create trigger语句文本字符长度不能超过32k,
    如果触发器的逻辑编码超过60行,则最好将主要代码保存到存储过程。
    使用时之需要从触发器中调用存储过程即可。
    
    2.不要创建递归式触发器,否则,系统会因为触发器与表之间的循环操作而导致内存耗尽,系统崩溃。
    
    3.触发器中不能使用数据库事务控制语句commit,rollback和savepoint
    
    4.由触发器所调用的过程或者函数也不能使用数据库事务控制语句。
    
    5.触发器中不能声明long,long raw类型变量,也不能在触发表的long 或long raw列上使用:new和:old.
    
    
    6.触发器体内可以读取Lob类型的列值,但不能通过:new修改lob列的数据。
    
    7.when条件子句只适用于行级触发器,一旦指定了when ,触发器只执行满足when子句条件的行
    
    问题:
    
    写一个ORACLE 触发器,主要是解决一个系统的管理员帐号经常被培训的人修改的问题.
    功能是在Update 用户表时如果是修改的管理员,
    则将管理员的密码改回到初始状态(假定初始密码是oldpassword)
    ,用户表是user(id ,password,job_title)
    
    
    CREATE OR REPLACE TRIGGER before_update_password 
      before update of password 
      on new_users
      for each row 
      when (new.jobtitle='admin')
      declare
        
    err exception;
     
    begin
    
        dbms_output.put_line('trigger active');
    
        if :old.jobtitle='admin'  then
    
          raise err;
        
    end if;
     
    exception 
        
    when err then
    
          raise_application_error(-20009,'pls do not change admin password');
        
    when others then
    
            dbms_output.put_line(sqlerrm);
    
     end;
    
    
    
    触发器与变异表
    
    变异表:当前正在被DML语句更新的表,对于触发器而言,变异表就是在其上定义该触发器的那张表。
    
    问题:将每一门课程的选修人数限制在2人,在students表上定义一个before insert or update行级触发器实现
    
    students(Id,major)
    create or replace trigger limitmajors
     
      before insert or update
     of major
      on students
     
      for each row
    
      declare
        
     v_maxstudents constant number:=2;
    
         v_currentstudents number;
    
      begin
    
         select count(*) into v_currentstudents
         from students where major=:new.major;
        
     if(v_currentstudents+1>v_maxstudents) then
     
            raise_application_error(-20001,‘too many students major in ‘||:new.major);
        
     end if;
     
      end;
    
    
    创建测试环境
    create table students(id number primary key,major varchar2(20));
    
    insert into students values(1,'math');
    insert into students values(2,'math');
    insert into students values(3,'math');
    
    用insert  ….select 子句测试
    create table students_copy as select * from students where 1=2;
    
    insert into  students_copy values(3,‘math’);
    insert into students
    select * from students_copy;
    
    用update测试
    insert into students values(3,'english');
    update students set major='math' where id=3;
    
    修改思路如下:
    将上面的触发器修改成2个触发器,行级与语句级触发器
    
    1.在行级触发器中,可以记录 :new.major 的值,但不查询students表
    2.查询在语句级触发器中实现
    
    需要 保存一些全局性变量以在两个触发器间传递变量,可以用包来实现:
    
    Create or replace package studentdata as
      Type t_majors is table OF students.major%type
      Index by BINARY_INTEGER;
      Type t_IDs is table OF students.ID%type
      Index by BINARY_INTEGER;
      V_studentmajors  t_majors;
      V_studentids     t_ids;
      V_numentries   binary_integer:=0;
    End studentdata;
    
    行级触发器
    Create or replace trigger rlimitmajors
      Before insert or update of major on students
      For each row
    Begin
      Studentdata.v_numentries:=studentdata.v_numentries+1;
      Studentdata.v_studentmajors(studentdata.v_numentries):=:new.major;  
      Studentdata.v_studentids(studentdata.v_numentries):=:new.id;
    End rlimitmajors;
    
    语句触发器
    Create or replace trigger slimitmajors
      after insert or update of major on students
      declare
         v_maxstudents constant number:=2;
         v_currentstudents number;
         v_studentid students.id%type;
         v_major students.major%type;
      begin
         for v_loopindex in 1..studentdata.v_numentries loop
           v_studentid :=studentdata.v_studentids(v_loopindex);
           v_major:= studentdata.v_studentmajors(v_loopindex);
           
    select count(*) into v_currentstudents
           from students where major= v_major;
           If V_currentstudents > V_maxstudents then
              RAISE_APPLICATION_ERROR(-20000,’too many students in major ’||v.major);
           End if;
           End loop;
           Studentdata.v_numentries:=0;
      End slimitmajors;
    
    说明
    1.确保变量成为全局变量的唯一方法就是将它存放在某个包中。
    2.注意用after语句级触发器中进行处理
    

      

  • 相关阅读:
    小程序,计算一个字符串中每个字符出现的次数
    打印好看的整齐的清单
    输入符号,宽,高,打印此符号组成的矩形
    输入名字显示其生日,没有则让输入生日,做记录
    51ll网产品信息保存为txt文件
    .py文件 改成默认用idle打开
    协程原理代码演示
    python: ImportError: cannot import name 'Style' from 'openpyxl.styles' 解决方法
    python编程快速上手第7章习题20
    HDU 1010 Tempter of the Bone
  • 原文地址:https://www.cnblogs.com/wust221/p/3073611.html
Copyright © 2020-2023  润新知