Oracle（25）pl/sql编程触发器及管理触发器及case...end case使用

た入场券 2024-02-17 21:03 4阅读 0赞

①触发器  
是指存放在数据库中，被隐含执行的存储过程，可以支持dml，还支持基于系统事件和ddl操作建立触发器。  
②语法

create [or replace] trigger trigger_name
    {
        before | after}
    {
        insert|delete|update[of column [, column ....]]}
    on [schema.]table_name
    [for each row]
    [when condition]
    begin
        trigger_body;
    end;

### ———————————————————————————— ###

①dml触发器  
②实例  
行级触发器和语句级触发器的区别：在创建触发器的时候，带不带for each row

create or replace trigger trigger_emp
    after
    insert
    on my_emp
    begin
      dbms_output.put_line('添加了一条');
    end;
    ---------------------------------------------------------------
    create or replace trigger trigger_emp
    after update on
    scott.my_emp
    for each row -- 表示这是一个行级触发器
    begin
      dbms_output.put_line('修改了一条数据');
    end;
    ----------------***休息日不允许删除数据***------------------------------------
    create or replace trigger trigger_emp
    before delete on my_emp
    begin
      if  to_char(sysdate, 'day') in ('星期日', '星期六') then
        raise_application_error(-20001, '休息日，不允许删除数据');
      end if;
    end;
    ---PROCEDURE PAISE_APPLICATION_ERROR(error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
    ---error_number_in自定义，从-20000到-20999之间，这样就不会与Oracle的任何错误代码发生冲突。error_msg_in自定义，它的长度不超过2K，否则截取2K。

③使用条件谓词  
当触发器中同时包含多个触发事件（insert，update，delete）时，为了在触发器代码中区分具体的触发事件，可以使用三个条件  
inserting updating deleting

create or replace trigger trigger_emp
    before 
    insert or update or delete 
    on my_emp
    begin
      case 
        when inserting then
          dbms_output.put_line('请不要添加');
          raise_application_error(-20002, '请不要添加');
        when updating then
          dbms_output.put_line('请不要修改');
          raise_application_error(-20003, '请不要修改');
        when deleting then
          dbms_output.put_line('请不要删除');
          raise_application_error(-20004, '请不要删除');
      end case;
    end;

④使用:old和:new  
当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值  
:new 修饰符访问操作完成后列的值  
:old 修饰符访问操作完成前列的值

create or replace trigger tri2
    before update on scott.emp
    for each row   -------行级触发器
    begin
      if :new.sal < :old.sal || :new.sal>:old.sal*1.2 then
        dbms_output.put_line('工资不能低于原来的工资');
        raise_application_error(-20005, '工资不能低于原来的工资');
      end if;
    end;

### ———————————————————————————— ###

①系统触发器  
系统事件触发器是指基于Oracle事件（例如logon和startup）所建立的触发器。通过使用系统事件触发器，提供了跟踪系统或是数据库变化的机制。  
常用的系统事件属性函数（建立事件触发器时需要时间属性函数）：  
ora\_client\_ip\_address // 返回客户端的ip  
ora\_database\_name // 返回数据库名  
ora\_login\_user // 返回登录用户名  
ora\_sysevent // 返回触发触发器的系统事件名  
ora\_des\_encrypted\_password //返回用户des(md5)加密后的密码  
②语法  
create or replace trigger 触发器名  
after\[before\] logon\[logoff\] on database  
begin  
—执行语句  
end;  
③实例

---创建一张表，用于保存用户登录或退出的情况
    create table log_table(
        username varchar2(20),
        logon_time date,
        logoff_time date,
        address varchar2(20));
    ---分别创建logon和logoff触发器
    create or replace trigger tri_on
    after logon on database
    begin
      insert into log_table(username, logon_time, address) values (ora_login_user,  sysdate, ora_client_ip_address);
    end;
    ---------
    create or replace trigger tri_off
    before logoff on database
    begin
      insert into log_table(username, logoff_time, address) values (ora_login_user,  sysdate, ora_client_ip_address);
    end;

### ———————————————————————————— ###

①ddl触发器  
create、alter、drop  
②语法  
create or replace trigger 触发器名  
after ddl on 方案名.schema  
begin  
–执行语句  
end;  
③实例

-- 创建表
    create table my_ddl_record(
    event varchar2(64),
    username varchar2(64),
    ddl_time date);
    --- 创建触发器
    create or replace trigger ddl_tri
    after ddl on scott.schema
    begin
      insert into my_ddl_record values(ora_sysevent, ora_login_user, sysdate);
    end;

### ———————————————————————————— ###

①管理触发器  
\- 禁止触发器 `alter trigger 触发器名 disable;`  
\- 激活触发器 `alter trigger 触发器名 enable;`  
\- 禁止或激活表的所有触发器`alter table emp disable all triggers; alter table emp enable all triggers;`  
\- 删除触发器 `drop trigger 触发器名;`