Oracle 学习之触发器
1. 触发器简介
触发器是存储在数据库服务器中的程序单元，当一个表或一个视图被改变，或者数据库发生某些事件时，Oracle会自动触发触发器，并执行触发器中的代码。只有在触发器中定义的事件发生时，触发器才被触发。触发器是自动执行的代码块，和存储过程的区别在于，用户可以直接调用存储过程，而不能直接调用触发器。
触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。
2. 触发器种类
能够触发触发器的事件有以下几种：
1 DML操作（INSERT、UPDATE、DELETE）
2 DDL操作（CREATE、ALTER、DROP）
3 系统事件（数据库的关闭与启动等）
4 用户事件（用户的登陆等）
上述的这些语句都可以触发触发器。如果你想在这些事件发生时干些别的事情，这个时候只需要定义对应的触发器即可，在触发器中完成你的工作。
触发器是基于表、视图、模式、数据库的，于此，我们可以把触发器分为下面的几类：
2.1 行级触发器和语句级触发器
行级触发器，即触发机制是基于行的，当表中数据改变时，将触发行级触发器，改变一行数据，触发一次；改变N行数据，就会触发N次；
语句级触发器是基于语句级的，当一条SQL语句改变数据时，无论这条SQL语句影响多少条记录，语句级触发器都只触发一次。SQL语句每执行一次，语句级触发器就被触发一次。
2.2 BEFORE和AFTER触发器
BEFORE：表示在触发语句运行前先运行“触发动作”。
AFTER表示触发语句运行之后才运行“触发动作”。
BEFORE和AFTER适用于行级触发器和语句级触发器。
2.3 复合触发器
复合触发器是表上的触发器，它有4个时间点，可以让我们针对不同的时间点指定不同的处理动作。这四个时间点分别如下：
-
- 在触发语句执行前（BEFORE STATEMENT）
- 在触发语句执行后（AFTER STATEMENT）
- 在每行记录被修改之前（BEFORE EACH ROW）
- 在每行记录被修改之后（AFTER EACH ROW）
触发语句必须是DML，如果触发语句没有影响任何一行数据，并且也没有指定BEFORE STATEMENT和AFTER STATEMENT两个时间点，则触发器也不会被触发。
2.4 INSTEAD OF触发器
有的视图，我们不能直接对其进行更新操作，但是我们可以在这种视图上建立触发器，利用触发器对视图的基表进行更新操作，这种类型的触发器就叫做“INSTEAD OF触发器”。
2.5 系统级触发器
系统事件触发器是基于数据库系统的触发器，系统事件触发器与表、视图没有关系，系统事件包括数据库启动、关闭、服务器错误、数据库角色改变等。当这些事件发生时，就会触发系统事件触发器。可以通过系统事件触发器实现对数据库的审计。
3. 触发器的组成
触发器一般由以下几部分组成:
1. 触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。 2. 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。 3. 触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。 4. 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。 5. 触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。 6. 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；
行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。
4. 创建触发器
创建触发器的一般语法是:
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER } {INSERT | DELETE | UPDATE [OF column [, column …]]} [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] ON [schema.]table_name | [schema.]view_name [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] [FOR EACH ROW ] [WHEN condition] PL/SQL_BLOCK | CALL procedure_name;
其中：
BEFORE 和 AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。
FOR EACH ROW选项说明触发器为行触发器，行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略 FOR EACH ROW 选项时，BEFORE 和 AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。
REFERENCING 子句说明相关名称，在行触发器的 PL/SQL 块和 WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。
当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。
4.1 创建DML触发器
DML触发器基本要点
（1）触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指 定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。
（2）触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用O R逻辑组合，不能使用AND逻辑组合）。
（3）条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供 的如下条件谓词。
（4）INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE 时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x 是可选的。
（5）DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。
（6）触发对象：指定触发器是创建在哪个表、视图上。
（7）触发类型：是语句级还是行级触发器。
（8）触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。
:NEW 修饰符访问操作完成后列的值， :OLD 修饰符访问操作完成前列的值
示例1：建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE --指定触发时机为删除操作前触发 ON scott.emp FOR EACH ROW --说明创建的是行级触发器 BEGIN --将修改前数据插入到日志记录表 del_emp ,以供监督使用。 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ) VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate ); END;
示例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表
CREATE OR REPLACE TRIGGER tr_dept_time BEFORE INSERT OR DELETE OR UPDATE ON departments BEGIN IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表'); END IF; END;
示例3：限定只对部门号为80的记录进行行触发器操作
CREATE OR REPLACE TRIGGER tr_emp_sal_comm BEFORE UPDATE OF salary, commission_pct OR DELETE ON HR.employees FOR EACH ROW WHEN (old.department_id = 80) BEGIN CASE WHEN UPDATING ('salary') THEN IF :NEW.salary < :old.salary THEN RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降'); END IF; WHEN UPDATING ('commission_pct') THEN IF :NEW.commission_pct < :old.commission_pct THEN RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降'); END IF; WHEN DELETING THEN RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录'); END CASE; END;
示例4 利用行触发器实现级联更新，在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id
CREATE OR REPLACE TRIGGER tr_reg_cou AFTER update OF region_id ON regions FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id ||'、新的region_id值是'||:new.region_id); UPDATE countries SET region_id = :new.region_id WHERE region_id = :old.region_id; END;
示例5 在触发器中调用过程
CREATE OR REPLACE PROCEDURE add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type ) IS BEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END add_job_history; --创建触发器调用存储过程... CREATE OR REPLACE TRIGGER update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROW BEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id); END
4.2 创建替代(INSTEAD OF)触发器
创建触发器的一般语法是:
CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF {INSERT | DELETE | UPDATE [OF column [, column …]]} [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] ON [schema.] view_name --只能定义在视图上 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] [FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定 [WHEN condition] PL/SQL_block | CALL procedure_name;
其中：
INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF触发器。
FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器
REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。
创建INSTEAD OF触发器需要注意以下几点：
- 只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
- 不能指定BEFORE 或 AFTER选项。
- FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
- 没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。
4.3 创建系统事件触发器
ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：
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;
其中: ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；
database_event_list：一个或多个数据库事件，事件间用 OR 分开；
系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。
示例：创建登录、退出触发器
CREATE TABLE log_event (user_name VARCHAR2(10), address VARCHAR2(20), logon_date timestamp, logoff_date timestamp); --创建登录触发器 CREATE OR REPLACE TRIGGER tr_logon AFTER LOGON ON DATABASE BEGIN INSERT INTO log_event (user_name, address, logon_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp); END tr_logon; --创建退出触发器 CREATE OR REPLACE TRIGGER tr_logoff BEFORE LOGOFF ON DATABASE BEGIN INSERT INTO log_event (user_name, address, logoff_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp); END tr_logoff;
4.4 使用触发器谓词
|
INSERTING |
如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE |
|
UPDATING |
如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE |
|
DELETING |
如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE |
4.5 数据库触发器的应用举例
1. 创建一个DML语句级触发器，当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新 dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。
CREATE TABLE dept_summary( Deptno NUMBER(2), Sal_sum NUMBER(9, 2), Emp_count NUMBER); INSERT INTO dept_summary(deptno, sal_sum, emp_count) SELECT deptno, SUM(sal), COUNT(*) FROM emp GROUP BY deptno; --创建一个PL/SQL过程disp_dept_summary --在触发器中调用该过程显示dept_summary标中的数据。 CREATE OR REPLACE PROCEDURE disp_dept_summary IS Rec dept_summary%ROWTYPE; CURSOR c1 IS SELECT * FROM dept_summary; BEGIN OPEN c1; FETCH c1 INTO REC; DBMS_OUTPUT.PUT_LINE('deptno sal_sum emp_count'); DBMS_OUTPUT.PUT_LINE('-------------------------------------'); WHILE c1%FOUND LOOP DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6)|| To_char(rec.sal_sum, '$999,999.99')|| LPAD(rec.emp_count, 13)); FETCH c1 INTO rec; END LOOP; CLOSE c1; END; BEGIN DBMS_OUTPUT.PUT_LINE('插入前'); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig1 AFTER INSERT OR DELETE OR UPDATE OF sal ON emp BEGIN DBMS_OUTPUT.PUT_LINE(''正在执行trig1 触发器…''); DELETE FROM dept_summary; INSERT INTO dept_summary(deptno, sal_sum, emp_count) SELECT deptno, SUM(sal), COUNT(*) FROM emp GROUP BY deptno; END; '); INSERT INTO dept(deptno, dname, loc) VALUES(90, ‘demo_dept’, ‘none_loc’); INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9999, 3000); DBMS_OUTPUT.PUT_LINE('插入后'); Disp_dept_summary(); UPDATE emp SET sal=1000 WHERE empno=9999; DBMS_OUTPUT.PUT_LINE('修改后'); Disp_dept_summary(); DELETE FROM emp WHERE empno=9999; DELETE FROM dept WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('删除后'); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig1’); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;
例2：创建DML语句行级触发器。当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。
BEGIN DBMS_OUTPUT.PUT_LINE('插入前'); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_update AFTER UPDATE OF sal ON emp REFERENCING OLD AS old_emp NEW AS new_emp FOR EACH ROW WHEN (old_emp.sal != new_emp.sal) BEGIN DBMS_OUTPUT.PUT_LINE(''正在执行trig2_update 触发器…''); DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); UPDATE dept_summary SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal WHERE deptno = :new_emp.deptno; END;' ); DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_insert AFTER INSERT ON emp REFERENCING NEW AS new_emp FOR EACH ROW DECLARE I NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE(''正在执行trig2_insert 触发器…''); SELECT COUNT(*) INTO I FROM dept_summary WHERE deptno = :new_emp.deptno; IF I > 0 THEN UPDATE dept_summary SET sal_sum=sal_sum+:new_emp.sal, Emp_count=emp_count+1 WHERE deptno = :new_emp.deptno; ELSE INSERT INTO dept_summary VALUES (:new_emp.deptno, :new_emp.sal, 1); END IF; END;' ); DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_delete AFTER DELETE ON emp REFERENCING OLD AS old_emp FOR EACH ROW DECLARE I NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE(''正在执行trig2_delete 触发器…''); SELECT emp_count INTO I FROM dept_summary WHERE deptno = :old_emp.deptno; IF I >1 THEN UPDATE dept_summary SET sal_sum=sal_sum - :old_emp.sal, Emp_count=emp_count - 1 WHERE deptno = :old_emp.deptno; ELSE DELETE FROM dept_summary WHERE deptno = :old_emp.deptno; END IF; END;' ); INSERT INTO dept(deptno, dname, loc) VALUES(90, 'demo_dept', 'none_loc'); INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9999, 3000); INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9998, 2000); DBMS_OUTPUT.PUT_LINE('插入后'); Disp_dept_summary(); UPDATE emp SET sal = sal*1.1 WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('修改后'); Disp_dept_summary(); DELETE FROM emp WHERE deptno=90; DELETE FROM dept WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('删除后'); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update'); DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert'); DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;
例3：利用ORACLE提供的条件谓词INSERTING、UPDATING和DELETING创建与例2具有相同功能的触发器。
BEGIN DBMS_OUTPUT.PUT_LINE('插入前'); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2 AFTER INSERT OR DELETE OR UPDATE OF sal ON emp REFERENCING OLD AS old_emp NEW AS new_emp FOR EACH ROW DECLARE I NUMBER; BEGIN IF UPDATING AND :old_emp.sal != :new_emp.sal THEN DBMS_OUTPUT.PUT_LINE(''正在执行trig2 触发器…''); DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); UPDATE dept_summary SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal WHERE deptno = :new_emp.deptno; ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); SELECT COUNT(*) INTO I FROM dept_summary WHERE deptno = :new_emp.deptno; IF I > 0 THEN UPDATE dept_summary SET sal_sum=sal_sum+:new_emp.sal, Emp_count=emp_count+1 WHERE deptno = :new_emp.deptno; ELSE INSERT INTO dept_summary VALUES (:new_emp.deptno, :new_emp.sal, 1); END IF; ELSE DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); SELECT emp_count INTO I FROM dept_summary WHERE deptno = :old_emp.deptno; IF I > 1 THEN UPDATE dept_summary SET sal_sum=sal_sum - :old_emp.sal, Emp_count=emp_count - 1 WHERE deptno = :old_emp.deptno; ELSE DELETE FROM dept_summary WHERE deptno = :old_emp.deptno; END IF; END IF; END;' ); INSERT INTO dept(deptno, dname, loc) VALUES(90, 'demo_dept', 'none_loc'); INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9999, 3000); INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9998, 2000); DBMS_OUTPUT.PUT_LINE('插入后'); Disp_dept_summary(); UPDATE emp SET sal = sal*1.1 WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('修改后'); Disp_dept_summary(); DELETE FROM emp WHERE deptno=90; DELETE FROM dept WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('删除后'); Disp_dept_summary(); DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;
例4：创建INSTEAD OF 触发器。首先创建一个视图myview, 由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作。
DECLARE No NUMBER; Name VARCHAR2(20); BEGIN DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE VIEW myview AS SELECT empno, ename, ''E'' type FROM emp UNION SELECT dept.deptno, dname, ''D'' FROM dept '); -- 创建INSTEAD OF 触发器trigger3; DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig3 INSTEAD OF INSERT ON myview REFERENCING NEW n FOR EACH ROW DECLARE Rows INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…''); IF :n.type = ''D'' THEN SELECT COUNT(*) INTO rows FROM dept WHERE deptno = :n.empno; IF rows = 0 THEN DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…''); INSERT INTO dept(deptno, dname, loc) VALUES (:n.empno, :n.ename, ''none’’); ELSE DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| ''的部门已存在，插入操作失败！''); END IF; ELSE SELECT COUNT(*) INTO rows FROM emp WHERE empno = :n.empno; IF rows = 0 THEN DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’); INSERT INTO emp(empno, ename) VALUES(:n.empno, :n.ename); ELSE DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| ''的人员已存在，插入操作失败!''); END IF; END IF; END; '); INSERT INTO myview VALUES (70, 'demo', 'D'); INSERT INTO myview VALUES (9999, USER, 'E'); SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70; DBMS_OUTPUT.PUT_LINE('员工编号：'||TO_CHAR(no)||'姓名：'||name); SELECT empno, ename INTO no, name FROM emp WHERE empno=9999; DBMS_OUTPUT.PUT_LINE('部门编号：'||TO_CHAR(no)||'姓名：'||name); DELETE FROM emp WHERE empno=9999; DELETE FROM dept WHERE deptno=70; DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3'); END;
例5：利用ORACLE事件属性函数，创建一个系统事件触发器。首先创建一个事件日志表eventlog，由它存储用户在当前数据库中所创建的数据库对象，以及用户的登陆和注销、数据库的启动和关闭等事件，之后创建trig4_ddl、trig4_before和trig4_after触发器，它们调用事件属性函数将各个事件记录到eventlog数据表中。
BEGIN -- 创建用于记录事件日志的数据表 DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE TABLE eventlog( Eventname VARCHAR2(20) NOT NULL, Eventdate date default sysdate, Inst_num NUMBER NULL, Db_name VARCHAR2(50) NULL, Srv_error NUMBER NULL, Username VARCHAR2(30) NULL, Obj_type VARCHAR2(20) NULL, Obj_name VARCHAR2(30) NULL, Obj_owner VARCHAR2(30) NULL ) '); -- 创建DDL触发器trig4_ddl DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig4_ddl AFTER CREATE OR ALTER OR DROP ON DATABASE DECLARE Event VARCHAR2(20); Typ VARCHAR2(20); Name VARCHAR2(30); Owner VARCHAR2(30); BEGIN -- 读取DDL事件属性 Event := SYSEVENT; Typ := DICTIONARY_OBJ_TYPE; Name := DICTIONARY_OBJ_NAME; Owner := DICTIONARY_OBJ_OWNER; --将事件属性插入到事件日志表中 INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner) VALUES(event, typ, name, owner); END; '); -- 创建LOGON、STARTUP和SERVERERROR 事件触发器 DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig4_after AFTER LOGON OR STARTUP OR SERVERERROR ON DATABASE DECLARE Event VARCHAR2(20); Instance NUMBER; Err_num NUMBER; Dbname VARCHAR2(50); User VARCHAR2(30); BEGIN Event := SYSEVENT; IF event = ''LOGON'' THEN User := LOGIN_USER; INSERT INTO eventlog(eventname, username) VALUES(event, user); ELSIF event = ''SERVERERROR'' THEN Err_num := SERVER_ERROR(1); INSERT INTO eventlog(eventname, srv_error) VALUES(event, err_num); ELSE Instance := INSTANCE_NUM; Dbname := DATABASE_NAME; INSERT INTO eventlog(eventname, inst_num, db_name) VALUES(event, instance, dbname); END IF; END; '); -- 创建LOGOFF和SHUTDOWN 事件触发器 DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig4_before BEFORE LOGOFF OR SHUTDOWN ON DATABASE DECLARE Event VARCHAR2(20); Instance NUMBER; Dbname VARCHAR2(50); User VARCHAR2(30); BEGIN Event := SYSEVENT; IF event = ''LOGOFF'' THEN User := LOGIN_USER; INSERT INTO eventlog(eventname, username) VALUES(event, user); ELSE Instance := INSTANCE_NUM; Dbname := DATABASE_NAME; INSERT INTO eventlog(eventname, inst_num, db_name) VALUES(event, instance, dbname); END IF; END; '); END; CREATE TABLE mydata(mydate NUMBER); CONNECT SCOTT/TIGER COL eventname FORMAT A10 COL eventdate FORMAT A12 COL username FORMAT A10 COL obj_type FORMAT A15 COL obj_name FORMAT A15 COL obj_owner FORMAT A10 SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error FROM eventlog; DROP TRIGGER trig4_ddl; DROP TRIGGER trig4_before; DROP TRIGGER trig4_after; DROP TABLE eventlog; DROP TABLE mydata;
4.6 数据库触发器的应用实例
用户可以使用数据库触发器实现各种功能：复杂的审计功能；例：将EMP 表的变化情况记录到AUDIT_TABLE和AUDIT_TABLE_VALUES中。
CREATE TABLE audit_table( Audit_id NUMBER, User_name VARCHAR2(20), Now_time DATE, Terminal_name VARCHAR2(10), Table_name VARCHAR2(10), Action_name VARCHAR2(10), Emp_id NUMBER(4)); CREATE TABLE audit_table_val( Audit_id NUMBER, Column_name VARCHAR2(10), Old_val NUMBER(7,2), New_val NUMBER(7,2)); CREATE SEQUENCE audit_seq START WITH 1000 INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE; CREATE OR REPLACE TRIGGER audit_emp AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW DECLARE Time_now DATE; Terminal CHAR(10); BEGIN Time_now:=sysdate; Terminal:=USERENV('TERMINAL'); IF INSERTING THEN INSERT INTO audit_table VALUES(audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'INSERT', :new.empno); ELSIF DELETING THEN INSERT INTO audit_table VALUES(audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'DELETE', :old.empno); ELSE INSERT INTO audit_table VALUES(audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'UPDATE', :old.empno); IF UPDATING('SAL') THEN INSERT INTO audit_table_val VALUES(audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal); ELSE UPDATING('DEPTNO') INSERT INTO audit_table_val VALUES(audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno); END IF; END IF; END;
示例2 增强数据的完整性管理
例：修改DEPT表的DEPTNO列时，同时把EMP表中相应的DEPTNO也作相应的修改；
CREATE SEQUENCE update_sequence INCREMENT BY 1 START WITH 1000 MAXVALUE 5000 CYCLE; ALTER TABLE emp ADD update_id NUMBER; CREATE OR REPLACE PACKAGE integritypackage AS Updateseq NUMBER; END integritypackage; CREATE OR REPLACE PACKAGE BODY integritypackage AS END integritypackage; CREATE OR REPLACE TRIGGER dept_cascade1 BEFORE UPDATE OF deptno ON dept DECLARE Dummy NUMBER; BEGIN SELECT update_sequence.NEXTVAL INTO dummy FROM dual; Integritypackage.updateseq:=dummy; END; CREATE OR REPLACE TRIGGER dept_cascade2 AFTER DELETE OR UPDATE OF deptno ON dept FOR EACH ROW BEGIN IF UPDATING THEN UPDATE emp SET deptno=:new.deptno, update_id=integritypackage.updateseq WHERE emp.deptno=:old.deptno AND update_id IS NULL; END IF; IF DELETING THEN DELETE FROM emp WHERE emp.deptno=:old.deptno; END IF; END; CREATE OR REPLACE TRIGGER dept_cascade3 AFTER UPDATE OF deptno ON dept BEGIN UPDATE emp SET update_id=NULL WHERE update_id=integritypackage.updateseq; END; SELECT * FROM EMP ORDER BY DEPTNO; UPDATE dept SET deptno=25 WHERE deptno=20;
转自：http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html#undefined 该博主特别无私，分享了许多有用的东西，希望大家可以多去看看
