使用触发器 - 创建触发器 - 《OceanBase v2.2.30 官方教程》

关于 OLD 和 NEW 伪记录
关于触发器 DML 语句类型检测
教程：用触发器实现表的列自增
教程：用触发器记录表的变化日志

可以使用 CREATE TRIGGER 语句创建触发器。默认情况下，创建的触发器是启用状态。如果要创建一个禁用状态的触发器，使用 CREATE TRIGGER 语句并带上 DISABLE 子句。

注意

创建触发器，您需要有相应的权限。

关于 OLD 和 NEW 伪记录

当行级触发器被触发时，STORED PROCEDURE 运行时会创建两个伪记录 OLD 和 NEW。之所以称之为伪记录是因为它们并不一定包含记录的全部字段。

分析触发器正在处理的记录：

对于一个 INSERT 触发器，OLD 记录没有值，NEW 记录包含新增的值。
对于一个 UPDATE 触发器，OLD 记录包含更新前的值， NEW 记录包含更新后的值。
对于一个 DELETE 触发器，OLD 记录包含删除前的值，NEW 记录没有值。

要引用一个伪记录，需要在名称前加一个冒号“ :OLD ” 或“ :NEW ”。

关于触发器 DML 语句类型检测

当触发器触发事件是 DML 语句时，STORED PROCEDURE 运行时会创建4个布尔变量并赋值，以表示当前行级记录被修改的类型。语句类型如下：

INSERTING：表示本次执行是由 INSERT 语句触发。
UPDATING：表示本次执行是由 UPDATE 语句触发。
UPDATING(‘column_name’)：表示本次执行由更新指定列的 UPDATE 语句触发。
DELETING：表示本次执行由 DELETE 语句触发。

教程：用触发器实现表的列自增

以下示例创建了一个表 account ，其 ID 要求是自增的，可以使用序列 seq_account.nextval 值填充这个列。通常可以要求客户端所有应用去显式插入这个值，另外一种方法就是利用表的行级 BEFORE INSERT 触发器实现。

CREATE TABLE account(id number NOT NULL PRIMARY KEY
    , name varchar2(50) NOT NULL UNIQUE 
    , value number NOT NULL
    , gmt_create date DEFAULT sysdate NOT NULL 
    , gmt_modified date DEFAULT sysdate NOT NULL  );
CREATE TABLE account_log(id number NOT NULL PRIMARY KEY
    , acc_id number NOT NULL 
    , acc_name varchar2(50) NOT NULL
    , old_value number NULL
    , new_value number  NULL
    , gmt_create date DEFAULT sysdate NOT NULL );
CREATE SEQUENCE seq_account_log START WITH 1 INCREMENT BY 1 nocycle ;
CREATE SEQUENCE seq_account START WITH 1 INCREMENT BY 1 nocycle;
delimiter /
CREATE OR REPLACE TRIGGER trg_before_ins_account
BEFORE INSERT 
ON account
FOR EACH ROW  
BEGIN 
    select seq_account.nextval INTO :NEW.id FROM DUAL ;
END;
/
delimiter ;
obclient> SET serveroutput ON;
obclient> SET SESSION autocommit=off;
obclient> INSERT INTO account(name, value) VALUES('Jack', 100),('Jim', 200),('Mike', 150);
obclient> COMMIT;
obclient> select * from account;
+----+------+-------+---------------------+---------------------+
| ID | NAME | VALUE | GMT_CREATE          | GMT_MODIFIED        |
+----+------+-------+---------------------+---------------------+
|  1 | Jack |   100 | 2020-04-02 19:32:05 | 2020-04-02 19:32:05 |
|  2 | Jim  |   200 | 2020-04-02 19:32:05 | 2020-04-02 19:32:05 |
|  3 | Mike |   150 | 2020-04-02 19:32:05 | 2020-04-02 19:32:05 |
+----+------+-------+---------------------+---------------------+
3 rows in set (0.00 sec)

教程：用触发器记录表的变化日志

以下示例演示如何用触发器实现对业务表的关键数据变化的审计，将表的关键字段变更记录到一个日志表中。

CREATE TABLE account(id number NOT NULL PRIMARY KEY
    , name varchar2(50) NOT NULL UNIQUE 
    , value number NOT NULL
    , gmt_create date DEFAULT sysdate NOT NULL 
    , gmt_modified date DEFAULT sysdate NOT NULL  );
CREATE TABLE account_log(id number NOT NULL PRIMARY KEY
    , acc_id number NOT NULL 
    , acc_name varchar2(50) NOT NULL
    , old_value number NULL
    , new_value number  NULL
    , gmt_create date DEFAULT sysdate NOT NULL );
CREATE SEQUENCE seq_account_log START WITH 1 INCREMENT BY 1 nocycle ;
CREATE SEQUENCE seq_account START WITH 1 INCREMENT BY 1 nocycle;
delimiter /
CREATE OR REPLACE TRIGGER trg_after_dml_account
AFTER INSERT OR UPDATE OR DELETE 
ON account
FOR EACH ROW 
DECLARE
    acc_id      account.id%TYPE;
    acc_name    account.name%TYPE ;
    old_value   account.value%TYPE;
    new_value   account.value%TYPE;
BEGIN
    IF INSERTING THEN
        acc_id      := :NEW.id;
        acc_name    := :NEW.name;
        new_value   := :NEW.value;
    ELSIF UPDATING THEN
        acc_id      := :NEW.id;
        acc_name    := :NEW.name;
        new_value   := :NEW.value;
        old_value   := :OLD.value;
    ELSIF DELETING THEN
        acc_id      := :OLD.id;
        acc_name    := :OLD.name;
        old_value   := :OLD.value;
    ELSE
        dbms_output.put_line('This code is not applicable.') ;
    END IF ;
    INSERT INTO account_log(id, acc_id, acc_name, old_value, new_value)
    VALUES(seq_account_log.nextval, acc_id, acc_name, old_value, new_value);
END ;
/
delimiter ;
obclient> INSERT INTO account(id,name, value) VALUES(1,'Jack', 100),(2,'Jim', 200),(3,'Mike', 150);
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0
obclient> UPDATE account SET value=value*2, gmt_Modified=sysdate ;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
obclient> DELETE FROM account WHERE id=2;
Query OK, 1 row affected (0.00 sec)
obclient> COMMIT;
Query OK, 0 rows affected (0.01 sec)
obclient> SELECT * FROM account;
+----+------+-------+---------------------+---------------------+
| ID | NAME | VALUE | GMT_CREATE            | GMT_MODIFIED          |
+----+------+-------+---------------------+---------------------+
|  1 | Jack |   200  | 2020-03-11 18:04:55 | 2020-03-11 18:05:00 |
|  3 | Mike |   300  | 2020-03-11 18:04:55 | 2020-03-11 18:05:00 |
+----+------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)
obclient> SELECT * FROM account_log;
+----+--------+----------+-----------+-----------+---------------------+
| ID | ACC_ID | ACC_NAME | OLD_VALUE | NEW_VALUE | GMT_CREATE            |
+----+--------+----------+-----------+-----------+---------------------+
|  1 |       1 | Jack       |       NULL |         100 | 2020-03-11 18:04:56 |
|  2 |       2 | Jim        |       NULL |         200 | 2020-03-11 18:04:56 |
|  3 |       3 | Mike       |       NULL |         150 | 2020-03-11 18:04:56 |
|  4 |       1 | Jack       |        100 |         200 | 2020-03-11 18:05:00 |
|  5 |       2 | Jim        |        200 |         400 | 2020-03-11 18:05:00 |
|  6 |       3 | Mike       |        150 |         300 | 2020-03-11 18:05:00 |
|  7 |       2 | Jim        |        400 |        NULL | 2020-03-11 18:05:03 |
+----+--------+----------+-----------+-----------+---------------------+
7 rows in set (0.00 sec)
obclient>