oraclePL/SQL（procedurelanguage/SQL）程序设计之触发器（trigger）

创建触发器

触发器类似于过程和函数&＃xff0c;都拥有声明、执行和异常处理过程的带名PL/SQL块。与包类似&＃xff0c;触发器必须存储在数据库中。
前面已经讲过&＃xff0c;过程是显式地通过过程调用执行的&＃xff0c;同时过程调用可以传递参数。与之相反&＃xff0c;触发器是在事件发生时隐式地运行的&＃xff0c;并且触发器不能接受参数。
运行触发器的方式叫做激发(firing)触发器。
触发事件可以是对数据库表的DML&＃xff08;INSERT、UPDATE、DELETE&＃xff09;操作或某种视图的操作。Oracle8i把触发器功能扩展到了可以激发系统事件&＃xff0c;如数据库的启动和关闭&＃xff0c;以及某种DDL操作。
触发器主要分为&＃xff1a;DML触发器、替代触发器和系统触发器。本次课程主要讲述DML触发器。
触发器可用来补充声明的参照完整性&＃xff0c;强制实施复杂的业务规则。
触发器是一个独立的事务&＃xff0c;被当作一个整体执行&＃xff0c;在执行过程中如果发生错误&＃xff0c;则整个事务会自动回滚。  

DML触发器由DML(INSERT、UPDATE、DELETE)语句激发。
创建DML触发器语法&＃xff1a;
CREATE [OR REPLACE] TRIGGER trigger_name
   {BEFORE | AFTER} triggering_event ON table_reference
   [FOR EACH ROW [WHEN trigger_condition]]
trigger_body;
trigger_name是触发器名&＃xff0c;triggering_event指定了何时激发触发器&＃xff0c; table_reference是定义触发器的表&＃xff0c; trigger_body是触发器的主要处理代码。WHEN子句的trigger_condition如果被使用的话&＃xff0c;将首先进行求值。触发器的主体仅当此条件求值为TRUE时才被执行

触发器的激发顺序
执行BEFORE语句级触发器&＃xff08;如果有的话&＃xff09;。
对于受语句影响的每一行&＃xff1a;
执行BRFORE行级触发器&＃xff08;如果有的话&＃xff09;。(for each row)
执行DML语句
执行AFTER行级触发器&＃xff08;如果有的话&＃xff09;。
执行AFTER语句级触发器&＃xff08;如果有的话&＃xff09;。

实例

CREATE OR REPLACE TRIGGER tri_emp_stmBefore
BEFORE INSERT  ON EMP
BEGIN
  dbms_output.put_line(&＃39;Before Statement trigger&＃39;);
END;
CREATE OR REPLACE TRIGGER tri_emp_stmAfter
after INSERT  ON EMP
BEGIN
  dbms_output.put_line(&＃39;After Statement trigger&＃39;);
END;

CREATE OR REPLACE TRIGGER tri_emp_rowBefore
before INSERT  ON EMP
for each row
BEGIN
  dbms_output.put_line(&＃39;Beore row trigger&＃39;);
END;
CREATE OR REPLACE TRIGGER tri_emp_rowAfter
after INSERT  ON EMP
for each row
BEGIN
  dbms_output.put_line(&＃39;After row trigger&＃39;);
END;

创建语句级触发器&＃xff1a; Example
CREATE OR REPLACE TRIGGER secure_emp
   BEFORE INSERT ON emp
   BEGIN
     IF (TO_CHAR(SYSDATE, &＃39;DY&＃39;) IN (&＃39;星期六&＃39;, &＃39;星期天&＃39;))
           OR (TO_CHAR(SYSDATE, &＃39;HH24:MI&＃39;) NOT BETWEEN &＃39;08:00&＃39; AND &＃39;18:00&＃39;)
     THEN
         RAISE_APPLICATION_ERROR (-20500, &＃39;You may insert into EMP table only during business hours.&＃39;);
      END IF;
END;
创建语句级触发器&＃xff1a; 测试上述触发器
SQL>INSERT INTO emp (empno, ename,hiredate,job, sal, deptno)
               VALUES (300,&＃39;Smith Rob&＃39;,SYSDATE,&＃39;IT_PROG&＃39;, 4500, 60)
创建行级触发器&＃xff1a; Example
CREATE OR REPLACE TRIGGER restrict_salary
  BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW
    BEGIN
      IF NOT (:NEW.job IN (&＃39;AD_PRES&＃39;, &＃39;AD_VP&＃39;)) AND :NEW.sal > 15000
      THEN
        RAISE_APPLICATION_ERROR (-20202, &＃39;Employee cannot earn this amount&＃39;);
      END IF;
END;
创建行级触发器&＃xff1a; 测试上述触发器
SQL> UPDATE emp
              SET sal &＃61; 15500
              WHERE ename &＃61; &＃39;SMITH&＃39;;

在行级触发器中使用 :old和 :new
触发语句所处理的每一行都激发一次行级触发器。在触发器的内部&＃xff0c;你可以访问当前正在被处理的行。这是通过“伪记录”——:old和:new实现的。它们的类型是table_reference%ROWTYPE

在行级触发器中使用 :old和 :new
create or replace TRIGGER GenerateStudentID
  BEFORE INSERT  ON t_student
  FOR EACH ROW
BEGIN
   SELECT seq_student_id.NEXTVAL
      INTO :new.n_student_ID
      FROM dual;
END GenerateStudentID;
执行上述触发器
SQL>INSERT INTO students (first_name, last_name)
  VALUES (&＃39;Lolita&＃39;, &＃39;Lazarus&＃39;);
SQL>INSERT INTO students (ID, first_name, last_name)
  VALUES (-7, &＃39;Zelda&＃39;, &＃39;Zoom&＃39;);

在行级触发器中使用限制条件

创建替代触发器
替代触发器只能定义在视图上

CREATE [OR REPLACE] TRIGGER trigger_name
   INSTEAD OF
       event1 [OR event2 OR event3]
       ON view_name
    [REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW]
trigger_body

实例
CREATE TABLE new_emps AS
      SELECT empno, ename, sal, deptno, job, hiredate  FROM emp;
CREATE TABLE new_depts AS
     SELECT d.deptno, d.dname, d.loc,sum(e.sal) tot_dept_sal
          FROM emp e, dept d
         WHERE e.deptno &＃61; d.deptno
         GROUP BY d.deptno, d.dname, d.loc;
CREATE VIEW emp_details AS
     SELECT e.empno, e.ename, e.sal, e.deptno,e.job, d.dname, d.loc
          FROM emp e, dept d
          WHERE e.deptno &＃61; d.deptno;

管理触发器&＃xff08;&＃xff09;

删除触发器    DROP TRIGGER secure_emp;