创建触发器

触发器的基础对象可分为表或视图，基于表的触发器支持AFTER与BEFORE触发时机，基于视图的触发器支持INSTEAD OF触发时机。

说明：

支持同一库下触发器名称与现有对象名重复。触发器属于被动调用的对象，不需要通过名称的唯一性去区分库中的对象，所以创建触发器的时候不需要检测当前库中已存在的对象名。

主要语法结构

语法格式

• 表或视图触发器

  CreateTrigStmt::=
      CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] name_space
        TriggerActionTime TriggerEvents ON name_space
        [OptTrggParamAlias]
        [FOR {EACH ROW | STATEMENT}]
        [WHEN bool_expr]
        [COMMENT SCONST]
      [DECLARE VarDefList]
      StmtBlock
      [name_space]

• 登录数据库触发器

  CreateTrigStmt::=
      CREATE [OR REPLACE] TRIGGER name_space
        AFTER LOGON ON DATABASE
        [COMMENT SCONST]
      [DECLARE VarDefList]
      StmtBlock
      [name_space]

说明：

当过程体含变量定义时，变量定义应在BEGIN关键字前通过DECLARE关键词显式定义；若无变量定义，则直接从BEGIN开始。

参数说明

• IF NOT EXISTS：创建触发器时若同名触发器存在则忽略错误。该关键字无法验证已有同名触发器与当前创建触发器属性是否一致。
• name_space：此处为触发器名或触发器的基表名称或视图名称，使用地方不同含义不同。
• TriggerActionTime：触发时机，有BEFORE、AFTER、INSTEAD OF三种情况，分别为被监视的动作之前、被监视的动作之后、替代受监视的动作，其中 INSTEAD OF 用于基于视图的触发器使用。
• TriggerOneEvent：受监视的操作类型，有INSERT、UPDATE、DELETE几种类型，INSERT 表示触发器将在表的插入操作时被触发，其余类推，其中UPDATE可以精确监视在某个字段上。
• OptTrggParamAlias：重命名NEW或OLD关键字。
• FOR EACH ROW：指定触发器为行级触发器，即每操作一行记录则触发器点火一次。
• FOR STATEMENT：指定触发器为语句级触发器，即每条操作命令触发点火一次。
• WHEN: 指定触发器约束。指定触发事件时必须满足的条件。
• DECLARE VarDefList：变量定义语句。包括变量定义、游标定义、异常定义等，详细信息请参见PL/SQL语言 > PL/SQL语法 > DECLARE声明部分。
• StmtBlock：触发器的过程体，详细信息请参见PL/SQL语言 > PL/SQL语法 > BEGIN...END块部分。

触发时机TriggerActionTime

语法格式

TriggerActionTime::=
    BEFORE
|   AFTER
|   INSTEAD OF

参数说明

• BEFORE：触发器在指定事件发生之前执行。
• AFTER：触发器用于在事件发生后执行后续操作，如日志记录或更新其他表。
• INSTEAD OF：触发器用于替代事件的默认行为，主要用于视图上。

触发事件TriggerEvents

语法格式

TriggerEvents::=
    TriggerOneEvent [OR TriggerOneEvent]...
    
TriggerOneEvent::=
    INSERT                
|   DELETE
|   UPDATE
|   UPDATE OF name_list
|   UPDATE OF ( name_list )

参数说明

OF name_list：可选项目，为表的列名，只对触发事件为UPDATE的触发器有效，当给定列名后，只有在修改动作变更指定列时，触发器才被点火。

触发器别名OptTrggParamAlias

语法格式

OptTrggParamAlias::=
    REFERENCING NEW AS ColId    
|   REFERENCING OLD AS ColId
|   REFERENCING NEW AS ColId OLD AS ColId
|   REFERENCING OLD AS ColId NEW AS ColId

参数说明

REFERENCING：为触发器中的OLD和NEW行定义别名。

示例

• 示例1
  在表test_trig_tab上创建名为trig_test的触发器，触发器用于监视表上的插入操作，当插入的记录的ID不小于10 时，将新记录同时插入到表test_trig_tab2中。

  CREATE TABLE TEST_TRIG_TAB(ID INT,NAME VARCHAR(20));
  CREATE TABLE TEST_TRIG_TAB2(ID INT,NAME VARCHAR(20));
  
  CREATE OR REPLACE TRIGGER trig_test
    AFTER INSERT ON test_trig_tab
    FOR EACH ROW
    WHEN (ID >= 10)
  BEGIN
    INSERT INTO test_trig_tab2 VALUES (new.id, new.name);
  END;
  /
  
  INSERT INTO TEST_TRIG_TAB VALUES(1, 'test_trig1');
  INSERT INTO TEST_TRIG_TAB VALUES(10, 'test_trig10');

• 示例2
  在表test_trig_tab上创建名为trig_test2的触发器，该触发器用于监视表的ID字段上的更改操作，当被更改的记录原ID值大于等于10时，向test_trig_tab2中插入一条记录，记录ID为test_trig_tab修改记录的旧值，记录NAME为字符串’trig_update’。

  CREATE OR REPLACE TRIGGER trig_test2
    AFTER UPDATE OF ID ON test_trig_tab
    FOR EACH ROW
    WHEN (old.ID >= 10)
  DECLARE
    num INTEGER;
  BEGIN
    INSERT INTO test_trig_tab2 VALUES (new.id, 'trig_update');
  END;

• 示例3
  创建一个使用referencing的触发器，referencing new AS nn表示把new关键字重命名为nn。当对表test_trig_tab插入记录的ID大于1000或者小于10时，自动往表test_trig_tab2插入一条记载消息记录。

  CREATE OR REPLACE TRIGGER trig_test3
    BEFORE INSERT ON test_trig_tab
    REFERENCING new AS nn
    FOR each ROW
  BEGIN
    IF nn.id > 1000 THEN
      INSERT INTO test_trig_tab2 VALUES (nn.id, '插入了一个过大的数！');
    ELSE
      IF nn.id < 10 THEN
        INSERT INTO test_trig_tab2 VALUES (nn.id, '插入了一个过小的数！');
      END IF;
    END IF;
  END;

• 示例4
  创建触发器支持IF NOT EXISTS。创建一个与原触发器同名但依赖表不同的触发器，不会影响原触发器。

  SQL> CREATE TABLE test_tab_trig1(a int);
  SQL> CREATE TRIGGER TRIG1
  BEFORE INSERT
  ON test_tab_trig1
  FOR EACH ROW
  BEGIN
      INSERT INTO test_tab_trig1 VALUES(99);
  END;
  SQL> SELECT dt.table_name,dr.trig_name FROM dba_triggers dr JOIN dba_tables dt ON dr.db_id=dt.db_id AND dr.OBJ_id=dt.table_id WHERE dt.table_name='TEST_TAB_TRIG1';
  TABLE_NAME |TRIG_NAME	
  -----------------------
  TEST_TAB_TRIG1 |TRIG1	   
  
  SQL> CREATE TABLE test_tab_trig2(a int);
  SQL> CREATE TRIGGER IF NOT EXISTS TRIG1 /*创建一个与原触发器依赖表不同的触发器，此处会返回警告*/
  BEFORE INSERT
  ON test_tab_trig2
  FOR STATEMENT
  BEGIN
      INSERT INTO test_tab_trig2 VALUES(99);
  END;
  SQL> SELECT dt.table_name,dr.trig_name FROM dba_triggers dr JOIN dba_tables dt ON dr.db_id=dt.db_id AND dr.OBJ_id=dt.table_id WHERE dt.table_name='TEST_TAB_TRIG2';/*不会对原触发器产生影响*/
  TABLE_NAME |TRIG_NAME	
  ----------------------

条件谓词

如果触发器的触发事件设置超过一种类型的DML语句，例如触发事件包括：INSERT、DELETE和UPDATE，那么触发体就可以使用条件谓词INSERTING、DELETING和UPDATING进行代码逻辑处理。

若触发器触发事件包括：

INSERT OR DELETE OR UPDATE OF column_name ON table_name

则在触发器处理代码中可使用以下条件进行相关代码逻辑处理：

IF inserting THEN .... END IF;
IF updating THEN .... END IF;
IF deleting THEN .... END IF;

有如下示例：

CREATE OR REPLACE TRIGGER trig_test4
  AFTER INSERT OR UPDATE OF id, name ON test_trig_tab
  FOR each ROW
BEGIN
  IF updating THEN
    INSERT INTO test_trig_tab2 VALUES (new.id, 'update tab');
  END IF;
  IF inserting THEN
    INSERT INTO test_trig_tab2 VALUES (new.id, 'insert tab');
  END IF;
END;

说明：

条件谓词触发器使用时，IF判断不能嵌套判断，各条件谓词间需并列处理。