ORACLE触发器　行级触发器

骑猪看日落 2022-08-12 00:24 325阅读 0赞

行级触发器

本章介绍行级触发器机制。大部分例子以INSERT出发器给出，行级触发器可从insert update delete语句触发。

1、介绍  
触发器是存储在数据库已编译的存储过程，使用的语言是PL/SQL，用编写存储过程一样的方式编写和编译触发器。下面在SQL\*PLUS会话中创建和示例一个简单的Insert行级触发器。这个触发器调用DBMS\_OUTPUT在每插入一行数据时打印“executing temp\_air”  
SQL> set feedback off  
SQL> CREATE TABLE temp (N NUMBER);  
SQL> CREATE OR REPLACE TRIGGER temp\_air  
  2  AFTER INSERT ON TEMP  
  3  FOR EACH ROW  
  4  BEGIN  
  5  dbms\_output.put\_line('executing temp\_air');  
  6  END;  
7   /  
8   SQL> INSERT INTO temp VALUES (1);      -- insert 1 row  
executing temp\_air  
SQL> INSERT INTO temp SELECT \* FROM temp;  -- insert 1 row  
executing temp\_air  
SQL> INSERT INTO temp SELECT \* FROM temp;  -- inserts 2 rows  
executing temp\_air  
executing temp\_air  
SQL>

尽管第三个Insert语句是一条SQL语句，但插入TEMP表中两条记录。许多insert语句插入一条记录，但可以用一条语句插入许多行。

2、行级触发器语法：  
   CREATE OR REPLACE TRIGGER trigger\_name  
 AFTER|BEFORE INSERT|UPDATE|DELETE ON table\_name  
 FOR EACH ROW  
 \[WHEN (Boolean expression)\]  
 DECLARE  
     Local declarations  
 BEGIN  
     Trigger Body written PL/SQL  
 END;

·Trigger\_name  
      用触发器名来确定表名和触发器类型。PL/SQL运行时错误将产生一个PL/SQL错误信息，涉及触发器名和行数。下面Oracle错误显示了在students表上的AFTER-INSERT行触发器的第5行有一个被0除错误。  
        ORA-01476: divisor is equal to zero  
  ORA-06512: at "SCOTT.STUDENTS\_AIR", line 5  
  ORA-04088: error during execution of trigger  
    'SCOTT.STUDENTS\_AIR'  
      行记数从关键字DECLARE行开始，如果没有DECLARE部分，BEGIN语句是第一行。触发器名称存储在USER\_TRIGGERS表的TRIGGER\_NAME。触发器名一般由表名、触发器类型、触发事件，语法如下：  
      trigger\_name = table\_name\_\[A|B\] \[I|U|D\] \[R|S\]  
       
      trigger\_name 最长30个字符，所以有时不得不使用表名缩写。常表名一般要有一个规则的缩写。这样可以减少故障分析处理时间。  
      \[A|B\]        表示是AFTER 或 BEFORE 触发器类型  
      \[I|U|D\]      表示触发事件，可能是 insert ,update 或者delete  
      \[R|S\]        表示行级(row)或语句级(statement)触发器类型。

·BEFORE|AFTER insert on table\_name  
      这条语句告诉Oracle什么时候执行触发器.它可能在ORACLE 完整性约束检查前或后执行，可以指定一个Before或after触发器在多语句操作类型上触发，如：  
        BEFORE INSERT OR UPDATE on table\_name  
  BEFORE INSERT OR UPDATE OR DELETE on table\_name  
  AFTER INSERT OR DELETE on table\_name  
      DBMS\_STANDARD 包提供了四个boolean函数来区分SQL语句类型。  
        PACKAGE DBMS\_STANDARD IS  
      FUNCTION inserting RETURN BOOLEAN;  
      FUNCTION updating RETURN BOOLEAN;  
      FUNCTION updating (colnam VARCHAR2) RETURN BOOLEAN;  
      FUNCTION deleting RETURN BOOLEAN;  
      etc,  
  END DBMS\_STANDARD;  
   在触发器中可以直接使用函数名称，不需要指定包名：  
   CREATE OR REPLACE TRIGGER temp\_aiur  
  AFTER INSERT OR UPDATE ON TEMP  
  FOR EACH ROW  
  BEGIN  
      CASE  
      WHEN inserting THEN  
          dbms\_output.put\_line  
              ('executing temp\_aiur - insert');  
      WHEN updating THEN  
          dbms\_output.put\_line  
              ('executing temp\_aiur - update');  
      END CASE;  
  END;  
      对于Update行级触发器，可以指定被更新的列作为触发器触发条件。  
     CREATE OR REPLACE TRIGGER temp\_aur  
   AFTER INSERT OR UPDATE OF M, P ON TEMP  
   FOR EACH ROW  
   BEGIN  
       dbms\_output.put\_line  
          ('after insert or update of m, p');  
   END;  
    ·WHEN(BOOLEAN EXPRESSION)  
        这是个可选语句，用来过滤触发触发器的条件。  
         
        CREATE OR REPLACE TRIGGER temp\_air  
   AFTER INSERT ON TEMP  
   FOR EACH ROW  
   WHEN (NEW.N = 0)  
   BEGIN  
       dbms\_output.put\_line('executing temp\_air');  
   END;  
  上例中表示AFTER INSERT行触发器触发的条件是：N字段的值等于0.  
  NEW.COLUMN\_NAME : INSERT或UPDATE触发器中WHEN语句中引用字段的语法。  
  OLD.COLUMN\_NAME : 用于UPDATE或DELETE行级触发器中WHEN语句中。在INSERT语句中为Null。