PL/SQL复合触发器

---

复合触发器

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

复合触发器的好处是程序单元集中，便于维护。

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

• 触发执行语句之前（BEFORE STATEMENT）
• 触发语句中的每一行发生变化之前（BEFORE EACH ROW）
• 触发语句中的每一行发生变化之后（AFTER EACH ROW）
• 触发执行语句之后（AFTER STATEMENT）

如果复合触发器影响到多行，则其执行顺序如下：

1. BEFORE语句级触发器
2. 第1行的BEFORE行级触发器
3. 第1行执行操作语句
4. 第1行的AFTER行级触发器
5. 第2行的BEFORE行级触发器
6. 第2行执行操作语句
7. 第2行的AFTER行级触发器
8. AFTER语句机触发器

--复合触发器创建语法
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', '深圳');

--结果
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 dept  
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);