PL/SQL之四 触发器

1.概述

   -定义：它是存储于数据库的命名PL/SQL语句块，当触发事件发生时它会会隐含执行。“触发事件”可以是如下的任何一种:处理数据库表的DML语句（如:INSERT、UPDATE、DELETE）；特定用户模式下或者任何用户执行的DDL语句（CREATE或ALTER);系统事件（启动或关闭数据库）；用户事件（登录和注销）。

   -特点：可被数据库自动反复调用；无法显示调用它，但可显式禁用或启用它。

   -用途：自动生成虚拟的列值；记录事件；收集统计数据；当DML发生在视图上时，能修改表中的数据；保证引用一致性；发布关于数据库事件、用户事件和SQL语句给订阅的应用程序；阻止工作时间之外的DML操作；阻止非法事务；解决约束中无法完成的复杂的商业或引用一致性规则。

   -限制：

         1）触发器除自治事务的触发器外通常不会执行事务控制语句。

         2）不允许在触发器体中声明LONG或LONG RAW变量。

         3）触发器调用的任何函数或者过程也不会执行事务控制语句，除非包括自治事务。

   -创建触发器通用语法：  

create [or replace] trigger trigger_name
{before|AFTER}trigger_eventontable_name  --before|After 指明何时触发器执行。
[for each row] --指定触发器为行触发器，没有指定为‘语句触发器’。
[followsanother_trigger] --指定触发器被触发的顺序。适用于相同表上所定义的，并且在相同时间点会执行的触发器。
[ENABLE/DISABLE] --指定触发器在何种状态（启用|禁用）下创建，不指定时，默认启用。
[When condition]--指定执行触发器必须满足的条件。WHNE子句‘:NEW’|‘:OLD’不使用冒号作为前缀，直接使用NEW|OLD。
Declare
   statements;
begin
    statements;
EXCEPTION
    statements;
end;

2.分类

   按基于的对象：表触发器、视图触发器（instead of)、模式触发器、数据库触发器。

   按时间点：before触发器、after触发器。

   是否按行触发（仅针于DML触发器）：行级触发器、语句级触发器。

3.DML触发器

   -概览：

         要么创建在表上，要么创建在视图上

         简单或者复合

         --简单触发器：before行级触发器、after行级触发器、before语句级触发器、after语句级触发器、在同一个对象上任意一类触发器可以有多个、但视图上只允许存在行级触发器。

         --复合触发器：合并若干个简单触发器，但上述四种类型每种最多出现一个。

   -条件断言,用于判断具体是哪种DML行为：inserting、updating、updating('fieldname'）、deleting。

   -相关名

        -默认为NEW和OLD，可视为table%rowtype类型的变量

        -只适用于行级触发器

        -使用方法：:new.fieldname、:old.fieldname

        -NEW字段值只有在表上的before触发器可以被改变

        -old的字段值不可被改变

        -不同的DML行为对NEW和OLD的影响

            DML语句        NEW值          OLD值

            insert       待插入的值          NULL

            UPDATE       更新后的值        更新前的值

            delete          NULL           删除前的值

   -与约束的区别

        -是否对新数据生成？触发器对新数据生效，但对触发器创建前已有的数据无效；约束即可以吸对新数据生效，也可以对所有数据生效。

        -谁的代码复杂？

        -什么时候用触发器？复杂的商业或引用一致性规则；仅在主子表不在同一数据库，而主子表之间需要维持数据一致性。

   -before触发器    

create or replace trigger student_bi
before insert on student
for each row
begin
  --':new'是伪记录，通过它可以访问当前正在被处理的数据行。
  :new.STUDENT_ID:=STUDENT_ID_SEQ.NEXTVAL;
  :new.CREATED_BY:=user;
  :new.CREATED_DATE:=sysdate;
end; 

-After触发器

create or replace trigger instructor_aud
after update or delete on INSTRUCTOR
declare
  v_type varchar2(20);
begin
      if updating then
            v_type:='UPDATE';
      elsif DELETING then
            v_type:='DELETE';
      end if;
      --有值更新
      update statistics a
      set a.TRANSACTION_USER=USER,
          a.TRANSACTION_DATE=sysdate
  where a.TABLE_NAME='INSTRUCTOR' and a.TRANSACTION_NAME=v_type;
      --没有插入记录
      if SQL%notfound then
            insert into statistics values('INSTRUCTOR',v_type,user,sysdate);
      end if;
end;    

--自治事务

    它是由其他事务发起的独立事务。自治事务会执行多个DML语句，并且commit或者rollback，而不会commit或rollback主事务执行的DML语句。

    定义自治事务通过‘Autonomous_transaction’编译指令。

    示例：某表中的数据被delete时，要记录删除日志。即使主事务失败，仍然能够记录删除日志。  

create or replace trigger instructor_aud
after delete on INSTRUCTOR
declare
  v_type varchar2(20):='DELETE';
  pragma AUTONOMOUS_TRANSACTION; --自治事务必须要有
begin 
      update statistics a
      set a.TRANSACTION_USER=USER,a.TRANSACTION_DATE=sysdate
      where a.TABLE_NAME='INSTRUCTOR' and a.TRANSACTION_NAME=v_type;
      
      insert into statistics values('INSTRUCTOR',v_type,user,sysdate);
      commit;--自治事务必须要有
end; 
--2.删除INSTRUCTOR表中的数据，再rollback
--3.查看statistics，会显示相关数据

-代替触发器

   它定义于视图。视图查询中包含以下情况不能被UPDATE、INSERT、DELETE：

• 集合操作，如union\union all\intersect\minus。
• 分组函数，如avg\count\max\min\sum
• group by或者having
• connect by 或者start with
• distinct
• rownum伪列

   示例：

create or replace trigger ins_del
instead of delete on ins_view
for each row
begin
  delete from ins where ins_id=:old.id;
end;  

 --练习

     

--场景一： emp_bak表中 任何操作 都要写到日志表中。
--1）创建log_emp_bak表
CREATE TABLE log_emp_bak(username VARCHAR2(20),create_date DATE,table_name VARCHAR2(20),opation_type VARCHAR2(10),
otherString VARCHAR2(200));

--2)创建基于log_emp_bak表的存储过程
CREATE OR REPLACE PROCEDURE pro_log_emp_bak(opation_type VARCHAR2,ostring VARCHAR2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION; --自治事务 必须要有
BEGIN
  INSERT INTO log_emp_bak(username, create_date, table_name, opation_type, otherstring)
  VALUES(USER,SYSDATE,Dictionary_obj_name,opation_type,ostring);
  COMMIT;--自治事务秘须要有
END;

 
--3)创建基于emp_bak表的触发器
CREATE OR REPLACE TRIGGER tri_emp_bak
AFTER INSERT OR UPDATE OR DELETE ON emp_bak
FOR EACH ROW
DECLARE
  v_type VARCHAR2(10);
  v_other VARCHAR2(200);
BEGIN
  IF updating THEN
     v_type:='update';
  ELSIF deleting THEN
     v_type:='delete';
     v_other:='delete empno:'||:old.empno||';';
  ELSIF inserting THEN
     v_type:='insert';
     v_other:='insert into 新empno:'||:new.empno||';';
  END IF;
  pro_log_emp_bak(v_type,v_other);
END; 

--场景二：对emp_bak表修改必须在周六或周日 才能修改
CREATE OR REPLACE TRIGGER tri_emp_bak_time  --语句级触发器
BEFORE INSERT OR UPDATE OR DELETE ON emp_bak
BEGIN
  IF  (TO_CHAR(sysdate, 'DAY') NOT IN('星期六','星期天')) THEN
     RAISE_APPLICATION_ERROR(-20001, '周一到周五不能修改emp_bak表');
  END IF;
END;

--场景三：emp_bak中deptno为20,且sal 小于800时 才能更新200
SELECT * FROM emp_bak;
CREATE OR REPLACE TRIGGER tri_emp_bak_sal
BEFORE UPDATE OF sal ON emp_bak
FOR EACH ROW
WHEN (old.deptno=20) --限定在deptno为20
BEGIN
CASE
     WHEN UPDATING('sal') THEN
        IF (:old.sal>1000 OR ((:new.sal-:old.sal)>200)) THEN
           raise_application_error(-20002,'原sal必须小于1000，且增加额必须是200之内才能更新');
        END IF;
 END CASE;

4.系统触发器

   4.1语法

CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE|AFTER}
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.]SCHEMA }
[WHEN condition]
PL/SQL_block | CALL procedure_name;

   4.2常见系统触发事件

  * STARTUP  AFTER  数据库实例启动之后触发
* SHUTDOWN  BEFORE  数据库实例关闭之前触发
* SERVERERROR  AFTER  出错误时触发
* LOGON  AFTER  用户登录后触发
* LOGOFF  BEFOR  用户注销前触发

   4.3常见事件属性函数

          ora_client_ip_address:用于返回客户端的IP地址

ora_database_name：用于返回当前数据库名

ora_des_encrypted_password:用于返回DES加密后的用户口令

ora_dict_obj_name:用于返回DDL操作所对应的数据库对象名

ora_dict_obj_name_list(name_list_ OUT ora_name_list_t):用于返回字事件中被修改的对象名列表

ora_dict_obj_owner:用于返回DDL操作所对应的对象的所有者名。

ora_dict_obj_ower_list(ower_list OUT ora_name_list_t):用于返回在事件中被修改对象的所有者列表

ora_dict_obj_type:用于返回DDL操作所对应的数据库对象的类型。

ora_grantee(user_list OUT ora_name_list_t):用于返回授权时事件授权者。

ora_instance_num:用于返回历程号。

ora_is_alter_column(column_name IN VARCHAR2):用于检测特定列是否被修改

ora_is_creating_nested_table:用于检测是否正在建立嵌套表

ora_is_drop_column(column_name IN VARCHAR2):用于检测特定列是否被删除

ora_is_servererror(error_number):用于检测是否返回了特定Oracle错误。

ora_login_user：用于返回登录用户名

          ora_sysevent ：用于返回触发 触发器的系统时间名。

   4.4示例

--1.创建事件日志表
CREATE TABLE log_event
(user_name VARCHAR2(10),
 address VARCHAR2(20),
 logon_date timestamp,
 logoff_date timestamp);

--2.创建基于数据库级的触发器
--开启数据库
CREATE OR REPLACE TRIGGER tr_startup
after startup  ON DATABASE
BEGIN
  INSERT INTO log_event(user_name, address, logon_date, logoff_date)
  VALUES(ora_login_user,ora_client_ip_address,sysdate,sysdate);
END;

--关闭数据库
 CREATE OR REPLACE TRIGGER tr_shutdown
before shutdown  ON DATABASE
BEGIN
  INSERT INTO log_event(user_name, address, logon_date, logoff_date)
  VALUES(ora_login_user,ora_client_ip_address,sysdate,sysdate);
END;

--登录数据库
CREATE OR REPLACE TRIGGER tr_logon
AFTER logon ON DATABASE
BEGIN
   INSERT INTO log_event(user_name, address, logon_date, logoff_date)
   VALUES(ora_login_user,ora_client_ip_address,sysdate,sysdate);
END;
--退出数据库
CREATE OR REPLACE TRIGGER tr_logoff
BEFORE logoff ON DATABASE
BEGIN
  INSERT INTO log_event(user_name, address, logon_date, logoff_date)
  VALUES(ora_login_user,ora_client_ip_address,sysdate,sysdate);
END;
--3.开启、关闭、登录、退出数据库并查看log_event，会发现相关日志已经写入。

 

--4.创建模式触发器
--a.dll_event

CREATE TABLE ddl_event
(crt_date timestamp PRIMARY KEY,
 event_name VARCHAR2(20),
 user_name VARCHAR2(10),
 obj_type VARCHAR2(20),
 obj_name VARCHAR2(20));

--b.tr_ddl
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL  ON SCHEMA
BEGIN
  INSERT INTO ddl_event(crt_date, event_name, user_name, obj_type, obj_name)
  VALUES(Systimestamp,ora_sysevent,ora_login_user,ora_dict_obj_type,ora_dict_obj_name);
END;

5.复合触发器

   5.1语法

      为避免变异表错误（ORA-04091:table XXX is mutating,trigger/function may not see it!)。

      它将多个不同类型的触发器合并为一个触发器，可以合并：before statement\after statement\before each row\after each row这四种触发器。

      基础语法：

CREATE OR REPLACE TRIGGER tri_fh
FOR INSERT OR UPDATE OR DELETE ON emp_bak
COMPOUND TRIGGER
 --before 语句级
 BEFORE STATEMENT  IS
 BEGIN
   dbms_output.put_line('before statement');
 END BEFORE STATEMENT;
 --before 行级
 BEFORE EACH ROW IS
 BEGIN
   dbms_output.put_line('before each row ');
 END BEFORE EACH ROW;
 --after 语句级
 AFTER STATEMENT IS
 BEGIN
   dbms_output.put_line('after statement');
 END AFTER STATEMENT;
 --after 行级
 AFTER EACH ROW IS
 BEGIN
   dbms_output.put_line('after each row');
 END AFTER EACH ROW;
END;

5.2示例

create or replace trigger section_compound
for insert or update on section
compound trigger
 v_instructor_id INSTRUCTOR.INSTRUCTOR_ID%type;
 v_name varchar2(20);
 v_total number;
before each row is
begin
  if :new.INSTRUCTOR_ID is not null then
        v_instructor_id:=:new.INSTRUCTOR_ID;
        select first_name||' '||last_name into v_name from INSTRUCTOR where instructor_id=:new.INSTRUCTOR_ID;
      end if;           
end before each row;
after statement is
begin
   select count(*) into v_total from section where INSTRUCTOR_ID=v_instructor_id;
       if(v_total>10) then
         raise_application_error(-20000,'Instructor,'||v_name||',is overbooked!');
       end if;
end after statement;
end;