Oracle触发器

一.Oracle触发器概述

触发器是一种特殊类型的存储计程,编译后存储在数据库服务器中,当特定事件发生时,由系统自动调用执行，而不能由应用程序显式地调用执行。此外，触发器不接受任何参数。主要用于护那些通过创建表时的声明约束不可能实现的复杂的完整性约束,并对数据库中特定事件进行监控和响应。

根据触发器作用的对象不同,触发器分为DML触发器 INSTEAD OF触发器和系统触发器3类。DML触发器是建立在基本表上的触发器，响应基本表的INSERT、UPDATE,DELETE操作；INSTEAD OF触发器是建的视图上的触发器，响应视图上的INSERT、UPDATE,DELETE操作；系统触发器是建文在系系统或模式上的触发器，响应系统事修和DDL（INSERT、UPDATE,DELETE）操作.

触发器由触发器头部和触发器体两个部分组成。触发器头部包括:

  • (1)作用对象:触发器作用的对象包括表、视图、数据库和模式。
  • (2)触发事件:激发触发器执行的事件。
  • (3)触发时间:用于指定触发器在触发事件完成之前还是之后执行。如果指定为AFTER则表示先执行触发事件，然后再执行触发器:如果指定为BEFORE,则表示先执行的触发器，然后再执行触发事件。
  • (4)触发级别:触发级别用于指定触发器响应触发事件的方式。默认为语句级触发展。即触发事件发生后，触发器只执行一次。如果指定为FOR EACH ROW,即为行级触发器则触发事件每次作用于一个记录，触发器就会执行一次。
  • (5)触发条件:由WHEN子句指定一个逻辑表达式， 当触发事件发生，而且WHEN条件为TRUE时，触发器才会执行。

二.DML触发器概述

建立在基本表上的触发器称为DML触发器。当对基本表进行数据的INSERT、UPDATE和DELETE操作时，会激发相应DML触发器的执行。DML触发器包括语句级前触发器、语句级后触发器、行级前触发器、行级后触发器4大类，其执行的顺序如下。
(1) 如果存在，则执行语句级前触发器。
(2)对于受触发事件影响的每--个记录:
●如果存在，则执行行级前触发器;
●执行当前记录的DML操作(触发事件);
●如果存在，则执行行级后触发器。
(3)如果存在，则执行语句级后触发器。
在每一类触发器内部，根据事件的不同又分为3种,如针对INSERT操作的语句级前触发器、语句级后触发器、行级前触发器、行级后触发器。对于同级别的DML触发器，其执行顺序是随机的。

三.创建DML触发器

创建DML触发器的语法为:

CREATE [OR REPLACE] TRIGGER trigger_ name
BEFORE |AFTER triggering_ event [oF column_ name]
ON table_ name
[FOR EACH ROW]
[WHEN trigger_ condition]
DECLARE
/*Declarative section is here */
BEGIN .
/*Excutable section si here*/
EXCEPTION
/*Exception section is here*/
END [trigger_ name] ;

1.创建语句级DML触发器

在默认情况下创建的DML触发器为语句级触发器，即触发事件发生后，触发器只执行一次。在语句级触发器中不能对列值进行访问和操作，也不能获取当前行的信息。
[例1]创建名为“TRG_SECURE_ EMP"的触发器，保证非工作时间禁止对.EMPLOYEES表进行DML操作。

CREATE OR REPLACE TRIGGER trg_ secure_ emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
OR TO _CHAR (SYSDATE, 'DY','NLS_.DATE_ LANGUAGE=AMERICAN') IN('SAT','SUN')
THEN
RAISE_ APPLICATION_ ERROR (-20005, '只能在正常的工作时间内进行改变。');
END IF;
END trg_ secure_ emp;

如果触发器响应多个DML事件，而且需要根据事件的不同进行不同的操作，则可以在触发器体中使用3个条件谓词。
(1) INSERTING:当触发事件是INSERT操作时，该条件谓词返回TRUE,否则返回FALSE。
(2) UPDATING:当触发事件是UPDATE操作时,该条件谓词返回TRUE,否则返回FALSE。
(3) DELETING:当触发事件是DELETE操作时，该条件谓词返回TRUE,否则返回FALSE。
[例2]为 employees表创建一个触发器 “TRG EMP_ DEPT_ STAT"， 当执行插入或删除操作时，统计操作后各个部门员工人数;当执行更新工资操作时，统计更新后各个部门员工平均工资。

CREATE OR REPLACE TRIGGER trg_emp_dept_stat
AFTER INSERT OR DELETE OR UPDATE OF salary ON employees
BECLARE
v_count NUMBER;
v_salary NUMBER(6,2);
BEGIN
IF INSERTING OR DELETING THEN
FOR v_deptionfo IN(SELECT department_id,count(*) employ_count FROM employees GROUP BY department_id)
LOOP
DBMS_OUTPUT.PUT_LINE(v_Deptinfo.department_id||'  '||v_Deptinfo.employ_count);
END LOOP;
ELSIF UPDATING THEN
FOR v_deptsal IN(SELECT department_id,avg(salary) avgsal FROM employees GROUP BY department_id)
LOOP
DBMS_OUTPUT.PUT_LINE( v_deptsal .department_id||'  '|| v_deptsal .employ_count);
END LOOP;
END IF;
END trg_emp_dept_stat;

2.创建行级DML触发器

行级触发器是指执行DML操作时，每操作一个记录， 触发器就执行一次， 一个DML操作涉及多少个记录，触发器就执行多少次。在行级触发器中可以使用WHEN条件，进一步控制触发器的执行。在触发器体中，可以对当前操作的记录进行访问和操作。

在行级触发器中引入了:old和:new两个标识符，来访问和操作当前被处理记录中的数据。PL/SQL将:old和:new作为triggering_table%ROWTYPE类型的两个变量。在不同触发事件中，:old 和:new的意义不同，见下表:

old和:new标识符含义

触发事件

 :old

 :new

INSER

未定义，所有字段都为NULL

当语句完成时，被插入的记录

UPDATE

更新前原始记录

当语句完成时，更新后的记录

DELETE

记录被刑除前的原始值

未定义，所有字段都为NULL

在触发器体内引用这两个标识符时，只能作为单个字段引用而不能作为整个记录引用,方法为:old.field和:new.field。 如果在WHEN子句中引用这两个标识符,则标识符前不需要加“：”。

[例3]为employees表创建一个名为“TRG_ EMP_ DML_ ROW”的触发器，当插入新员工时显示新员工的员工号、员工名;当更新员工工资时，显示修改前后员工工资;当删除员工时，显示被删除的员工号、员工名。

CREATE OR REPLACE TRIGGER trg_ emp_ _dml_ row

BEFORE INSERT OR UPDATE OR DELETE ON employees

FOR EACH ROW

BEGIN

IF INSERTING THEN

DBMS_ OUTPUT . PUT_ LINE (:new.employee_ id1I'  'II:new.first_ name||' '||

:new. last_ name) ;

ELSIF UPDATING THEN

DBMS_ OUTPUT. PUT_ LINE (:old.salary||'  '|| :new.salary) ;

ELSE

DBMS_ OUTPUT.PUT_ LINE(:old. employee_ id||' '||:old.first_ name||' '||

:old.last_ name) ;

END IF;

END trg_ emp_ dml_ row;

四.变异表触发器

变异表是指激发触发器的DML语句所操作的表，即触发器为之定义的表，或者由于DELETE CASCADE操作而需要修改的表，即当前表的子表。

约束表是指由于引用完整性约束而需要从中读取或修改数据的表，即当前表的父表。

当对一个表创建行级触发器，或创建由DELETECASCADE操作而激发的语句级触发器时，有下列两条限制:

●不能读取或修改任何触发语句的变异表;

●不能读取或修改触发表的一个约束表的PRIMARY KEY, UNIQUE或FOREIGN KEY

关键字的列,但可以修改其他列。

注意:如果INSERT..VALUES语句只影响一行，那么该语句的行级前触发器不会把触发表当做变异表对待。这是行级别触发器可以读取或修改触发表的的唯一情况。诸如INSERT INTO Table SELECT..等语句总是把触发表当做变异表，即使子查查询仅仅返回一条记录。

因为变异表触发器的限制条件主要是针对行级触发器的，那么,可以将行级触发器与语句级触发器结合起来,在行级触发器中获取要修改的记求的信息，存放到一个软件包的全局变量中，然后在语句级后触发器中利用软件包中全局变量信息对变异表的查询，并根据在询的结果进行业务处理。

[例4]为了实现在更新员工所在部门或向部门插入新员工时，部门中员人数不超过20人，可以在employees表上创建两个触发器，同时创建一个共享信息的包。

CREATE OR REPLACE PACKAGE mutate_ pkg

AS

V_deptno NUMBER(2);

END;

CREATE OR REPLACE TRIGGER trg_rmutate

BEFORE INSERT OR UPDATE OF department_id ON employees

FOR EACH ROW

BEGIN

mutate_ pkg.V_ deptno:= :new. department_ id;

END;

CREATE OR REPLACE TRIGGER trg_ smutate

AFTER INSERT OR UPDATE OF department_ id ON employees

DECLARE

V_ num number (3) ;

BEGIN

SELECT count (*) INTO v_num FROM employees

WHERE department_id=mutate_ pkg. v_ deptno;

IE v_num>20 THEN

RAISE APPLICATION_ERROR (-20003, 'T0O MANY EMPLOYEES IN DEPARTMENT 'II

mutate_ pkg.v_deptno) ;

END IF;

END;

当执行插入或更新操作时，只要部门人数不超过20人，就可以正常进行:如果部门人数超过20人，触发器将阻止操作的进行。例如:

SQL>INSERT INTO employees (employee_ id, first_ _name, last_ name, email,

hire_ date, job_id, department _id) VALUES (employees_ seq.nextval,

'Jason', 'smith', 'jason@neusoft. edu.cn' ,sysdate,'AC. _ACCOUNT',50);

INSERT INTO employees (employee_ id, first_ _name, last_ name, email,

*

第1行出现错误:

ORA-20003: Too MANY EMPLOYEES IN DEPARTMENT 50

ORA-06512:在"HUMAN. TRG_ SMUTATE", line 6

ORA-04088:触发器’HUMAN. TRG_ SMUTATE'执行过程中出错

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/CX66/p/14092980.html