触发器七(复合触发器)(学习笔记)

复合触发器

复合触发器是在Oracle 11g之后引入进来的一种新结构的触发器，复合触发器既是表级触发器又是行级触发器。在之前针对于不同级别的触发器，如果要在一张数据表上完成表级触发（BEFORE和AFTER）与行级触发（BEFORE和AFTER）则需要编写四个触发器才可以完成，而有了复合触发器之后，只需要一个触发器就可以定义完全部的四个功能

使用复合触发器可以捕获四个操作事件：

1. 触发执行语句之前（BEFORE STATEMENT）
2. 触发语句中的每一行发生变化之前（BEFORE EACH ROW）
3. 触发语句中的每一行发生变化之后（AFTER EACH ROW）
4. 触发执行语句之后（AFTER STATEMENT）

复合触发器创建语法

--复合触发器创建语法
CREATE [OR REPLACE] TRIGGER 触发器名称
    FOR [INSERT | UPDATE | UPDATE OF 列名称 [,列名称,...] | DELETE] ON 表名称
    COMPOUND TRIGGER
        [ BEFORE STATEMENT IS    -- 语句执行前触发（表级）
            [ 声明部分 ; ]
        BEGIN
            程序主体部分 ;
        END BEFORE STATEMENT ; ]
        [ BEFORE EACH ROW IS    -- 语句执行前触发（行级）
            [ 声明部分 ; ]
        BEGIN
            程序主体部分 ;
        END BEFORE EACH ROW ; ]
        [ AFTER STATEMENT IS    -- 语句执行后触发（表级）
            [ 声明部分 ; ]
        BEGIN
            程序主体部分 ;
        END AFTER STATEMENT ; ]
        [ AFTER EACH ROW IS    -- 语句执行后触发（行级）
            [ 声明部分 ; ]
        BEGIN
            程序主体部分 ;
        END AFTER EACH ROW ; ]
    END ;
    /

 示例一、创建复合触发器

--创建复合触发器
create or replace trigger compound_trigger
  FOR INSERT OR UPDATE OR DELETE  on dept  
   compound TRIGGER

    BEFORE STATEMENT IS  -- 语句执行前触发（表级）
    BEGIN
      DBMS_OUTPUT.put_line('1、BEFORE STATEMENT .') ;
    END BEFORE STATEMENT;
    BEFORE EACH ROW IS  -- 语句执行前触发（行级）
    BEGIN
      DBMS_OUTPUT.put_line('2、BEFORE EACH ROW .') ;
    END BEFORE EACH ROW;
    AFTER STATEMENT IS  -- 语句执行后触发（表级）
    BEGIN
      DBMS_OUTPUT.put_line('3、AFTER STATEMENT .') ;
    END AFTER STATEMENT;
    AFTER EACH ROW IS  -- 语句执行后触发（行级）
    BEGIN
      DBMS_OUTPUT.put_line('4、AFTER EACH ROW .') ;
    END AFTER EACH ROW;

end compound_trigger;

--测试
INSERT INTO dept(deptno,Dname,loc)
VALUES(97,'RD','深圳');
SELECT * FROM dept;
--结果
1、BEFORE STATEMENT .
2、BEFORE EACH ROW .
4、AFTER EACH ROW .
3、AFTER STATEMENT .

示例二、

定义触发器，此触发器可以完成如下的功能

在周末时间不允许更新emp表数据；

在更新数据时，要求将所有增加的数据自动变为大写；

在更新完成之后，新增雇员的工资不得高于公司的平均工资

--创建复合触发器
create or replace trigger emp_compound_trigger
  FOR INSERT OR UPDATE OR DELETE on emp  
  compound TRIGGER
           BEFORE STATEMENT IS         --表级，语句执行前触发                         
            v_curweek                     VARCHAR2(20);          --声明,--周末不能更新                   
           BEGIN                           
             SELECT to_char(SYSDATE,'day') INTO v_curweek FROM dual;
             IF TRIM(v_curweek) IN ('星期六','星期日') THEN
               raise_application_error(-20004,'周末不允许更新员工表emp');
               END IF;
                 EXCEPTION
            WHEN OTHERS THEN
              dbms_output.put_line(SQLCODE||SQLERRM);
           END BEFORE STATEMENT;
           BEFORE EACH ROW IS                             --行级，语句执行前触发                       
                v_avgsal           emp.sal%TYPE;           --声明平均工资变量              
           BEGIN
             IF inserting OR updating THEN
               :new.ename:=upper(:new.ename);
               :new.job:=upper(:new.job);
             END IF;
             IF inserting THEN
                 SELECT AVG(sal) INTO v_avgsal FROM emp;
                 IF :new.sal > v_avgsal THEN
                   raise_application_error(-20005,'新员工工资不重高于公司平均工资!');
                 END IF;
             END IF;
               EXCEPTION
            WHEN OTHERS THEN
              dbms_output.put_line(SQLCODE||SQLERRM);
          END BEFORE EACH ROW;
        
           
end emp_compound_trigger;

--测试
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(8888,'test','clerk',7369,SYSDATE,1000,10);

--时间改为周末
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(9999,'test1','clerk',7369,SYSDATE,5000,20);