可以使用 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 触发器实现。

  1. CREATE TABLE account(id number NOT NULL PRIMARY KEY
  2. , name varchar2(50) NOT NULL UNIQUE
  3. , value number NOT NULL
  4. , gmt_create date DEFAULT sysdate NOT NULL
  5. , gmt_modified date DEFAULT sysdate NOT NULL );
  6. CREATE TABLE account_log(id number NOT NULL PRIMARY KEY
  7. , acc_id number NOT NULL
  8. , acc_name varchar2(50) NOT NULL
  9. , old_value number NULL
  10. , new_value number NULL
  11. , gmt_create date DEFAULT sysdate NOT NULL );
  12. CREATE SEQUENCE seq_account_log START WITH 1 INCREMENT BY 1 nocycle ;
  13. CREATE SEQUENCE seq_account START WITH 1 INCREMENT BY 1 nocycle;
  14. delimiter /
  15. CREATE OR REPLACE TRIGGER trg_before_ins_account
  16. BEFORE INSERT
  17. ON account
  18. FOR EACH ROW
  19. BEGIN
  20. select seq_account.nextval INTO :NEW.id FROM DUAL ;
  21. END;
  22. /
  23. delimiter ;
  24. obclient> SET serveroutput ON;
  25. obclient> SET SESSION autocommit=off;
  26. obclient> INSERT INTO account(name, value) VALUES('Jack', 100),('Jim', 200),('Mike', 150);
  27. obclient> COMMIT;
  28. obclient> select * from account;
  29. +----+------+-------+---------------------+---------------------+
  30. | ID | NAME | VALUE | GMT_CREATE | GMT_MODIFIED |
  31. +----+------+-------+---------------------+---------------------+
  32. | 1 | Jack | 100 | 2020-04-02 19:32:05 | 2020-04-02 19:32:05 |
  33. | 2 | Jim | 200 | 2020-04-02 19:32:05 | 2020-04-02 19:32:05 |
  34. | 3 | Mike | 150 | 2020-04-02 19:32:05 | 2020-04-02 19:32:05 |
  35. +----+------+-------+---------------------+---------------------+
  36. 3 rows in set (0.00 sec)

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

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

  1. CREATE TABLE account(id number NOT NULL PRIMARY KEY
  2. , name varchar2(50) NOT NULL UNIQUE
  3. , value number NOT NULL
  4. , gmt_create date DEFAULT sysdate NOT NULL
  5. , gmt_modified date DEFAULT sysdate NOT NULL );
  6. CREATE TABLE account_log(id number NOT NULL PRIMARY KEY
  7. , acc_id number NOT NULL
  8. , acc_name varchar2(50) NOT NULL
  9. , old_value number NULL
  10. , new_value number NULL
  11. , gmt_create date DEFAULT sysdate NOT NULL );
  12. CREATE SEQUENCE seq_account_log START WITH 1 INCREMENT BY 1 nocycle ;
  13. CREATE SEQUENCE seq_account START WITH 1 INCREMENT BY 1 nocycle;
  14. delimiter /
  15. CREATE OR REPLACE TRIGGER trg_after_dml_account
  16. AFTER INSERT OR UPDATE OR DELETE
  17. ON account
  18. FOR EACH ROW
  19. DECLARE
  20. acc_id account.id%TYPE;
  21. acc_name account.name%TYPE ;
  22. old_value account.value%TYPE;
  23. new_value account.value%TYPE;
  24. BEGIN
  25. IF INSERTING THEN
  26. acc_id := :NEW.id;
  27. acc_name := :NEW.name;
  28. new_value := :NEW.value;
  29. ELSIF UPDATING THEN
  30. acc_id := :NEW.id;
  31. acc_name := :NEW.name;
  32. new_value := :NEW.value;
  33. old_value := :OLD.value;
  34. ELSIF DELETING THEN
  35. acc_id := :OLD.id;
  36. acc_name := :OLD.name;
  37. old_value := :OLD.value;
  38. ELSE
  39. dbms_output.put_line('This code is not applicable.') ;
  40. END IF ;
  41. INSERT INTO account_log(id, acc_id, acc_name, old_value, new_value)
  42. VALUES(seq_account_log.nextval, acc_id, acc_name, old_value, new_value);
  43. END ;
  44. /
  45. delimiter ;
  46. obclient> INSERT INTO account(id,name, value) VALUES(1,'Jack', 100),(2,'Jim', 200),(3,'Mike', 150);
  47. Query OK, 3 rows affected (0.15 sec)
  48. Records: 3 Duplicates: 0 Warnings: 0
  49. obclient> UPDATE account SET value=value*2, gmt_Modified=sysdate ;
  50. Query OK, 3 rows affected (0.01 sec)
  51. Rows matched: 3 Changed: 3 Warnings: 0
  52. obclient> DELETE FROM account WHERE id=2;
  53. Query OK, 1 row affected (0.00 sec)
  54. obclient> COMMIT;
  55. Query OK, 0 rows affected (0.01 sec)
  56. obclient> SELECT * FROM account;
  57. +----+------+-------+---------------------+---------------------+
  58. | ID | NAME | VALUE | GMT_CREATE | GMT_MODIFIED |
  59. +----+------+-------+---------------------+---------------------+
  60. | 1 | Jack | 200 | 2020-03-11 18:04:55 | 2020-03-11 18:05:00 |
  61. | 3 | Mike | 300 | 2020-03-11 18:04:55 | 2020-03-11 18:05:00 |
  62. +----+------+-------+---------------------+---------------------+
  63. 2 rows in set (0.00 sec)
  64. obclient> SELECT * FROM account_log;
  65. +----+--------+----------+-----------+-----------+---------------------+
  66. | ID | ACC_ID | ACC_NAME | OLD_VALUE | NEW_VALUE | GMT_CREATE |
  67. +----+--------+----------+-----------+-----------+---------------------+
  68. | 1 | 1 | Jack | NULL | 100 | 2020-03-11 18:04:56 |
  69. | 2 | 2 | Jim | NULL | 200 | 2020-03-11 18:04:56 |
  70. | 3 | 3 | Mike | NULL | 150 | 2020-03-11 18:04:56 |
  71. | 4 | 1 | Jack | 100 | 200 | 2020-03-11 18:05:00 |
  72. | 5 | 2 | Jim | 200 | 400 | 2020-03-11 18:05:00 |
  73. | 6 | 3 | Mike | 150 | 300 | 2020-03-11 18:05:00 |
  74. | 7 | 2 | Jim | 400 | NULL | 2020-03-11 18:05:03 |
  75. +----+--------+----------+-----------+-----------+---------------------+
  76. 7 rows in set (0.00 sec)
  77. obclient>