数据库技术学习 https://www.itkc8.com
复合触发器
复合触发器是在Oracle 11g之后引入进来的一种新结构的触发器,复合触发器既是表级触发器又是行级触发器。在之前针对于不同级别的触发器,如果要在一张数据表上完成表级触发(BEFORE和AFTER)与行级触发(BEFORE和AFTER)则需要编写四个触发器才可以完成,而有了复合触发器之后,只需要一个触发器就可以定义完全部的四个功能
使用复合触发器可以捕获四个操作事件:
- 触发执行语句之前(BEFORE STATEMENT)
- 触发语句中的每一行发生变化之前(BEFORE EACH ROW)
- 触发语句中的每一行发生变化之后(AFTER EACH ROW)
- 触发执行语句之后(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);
数据库技术学习 https://www.itkc8.com