Oracle兼容-存储过程-触发器（TRIGGER）

1. 语法

  1. 1.
  2. SET sql_mode = ORACLE;
  4. CREATE TRIGGER trigger_name
  5.   trigger_time trigger_event
  6.   ON tbl_name FOR EACH ROW
  7.   [trigger_order]
  8.   trigger_body
  10. trigger_time: { BEFORE | AFTER }
  12. trigger_event: { INSERT | UPDATE | DELETE | INSERT OR UPDATE | INSERT OR DELETE | UPDATE OR DELETE | INSERT OR UPDATE OR DELETE }
  14. 2. ALTER TRIGGER trigger_name [enable|disable]

2. 定义和用法

在 ORACLE 模式下，GreatSQL存储过程支持Oracle风格的触发器大部分语法。

同时也支持在任何模式下启用和禁用触发器。

3. Oracle兼容说明

  1. 不支持在视图中使用触发器，不支持 INSTEAD OF语法

  2. 不支持在 UPDATE 触发条件后跟这 OF 语法。

  3. 在 ORACLE 模式下，相同触发事件只执行最后创建的触发器；而在 DEFAULT 模式下，相同触发事件按创建触发器的顺序逐个执行。

4. 示例

创建测试表并初始化数据

  1. greatsql> CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(300) NOT NULL);
  2. greatsql> INSERT INTO t1 VALUES(1, 'row1'), (2, 'row2'), (3,'row3') ;
  • 1 示例1：INSERT
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. CREATE TRIGGER trg_t1_before_insert BEFORE INSERT 
  5. ON t1 FOR EACH ROW
  6. BEGIN
  7.  IF :NEW.a < 0 THEN
  8.   :NEW.b := '-trg_t1_before_insert INSERT' || :NEW.b;
  9.  END IF;
  10. END; //
    1. 示例2：INSERT OR UPDATE
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. CREATE TRIGGER trg_t1_before_insert_or_update BEFORE INSERT OR UPDATE
  5. ON t1 FOR EACH ROW
  6. BEGIN
  7.  IF :NEW.a < 0 THEN
  8.   :NEW.b := '-trg_t1_before_insert_or_update INSERT OR UPDATE' || :NEW.b;
  9.  END IF;
  10. END; //
    1. 示例3：UPDATE OR DELETE
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. CREATE TRIGGER trg_t1_before_update_or_delete BEFORE UPDATE OR DELETE
  5. ON t1 FOR EACH ROW
  6. BEGIN
  7.  IF :NEW.a < 0 THEN
  8.   :NEW.b := '-trg_t1_before_update_or_delete UPDATE OR DELETE' || :NEW.b;
  9.  END IF;
  10. END; //
    1. 示例4：支持 WHEN
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. CREATE TRIGGER trg_t1_before_update_or_insert BEFORE UPDATE OR INSERT
  5. ON t1 FOR EACH ROW
  6. WHEN(NEW.a < 0)
  7. DECLARE
  8.  a INT := 0;
  9. BEGIN
  10.  IF :NEW.a < 0 THEN
  11.   :NEW.a := - :NEW.a;
  12.   :NEW.b := '-trg_t1_before_update_or_insert UPDATE OR INSERT' || :NEW.b;
  13.  END IF;
  14. END; //
    1. 示例5

创建完上述4个触发器之后，再执行下面的测试：

  1. -- 在 `ORACLE` 模式下，相同触发事件只执行最后创建的触发器
  2. greatsql> SET sql_mode = ORACLE;
  4. greatsql> SELECT * FROM t1;
  5. +---+------+
  6. | a | b    |
  7. +---+------+
  8. | 1 | row1 |
  9. | 2 | row2 |
  10. | 3 | row3 |
  11. +---+------+
  13. greatsql> INSERT INTO t1 VALUES(-4, '-row4');
  14. Query OK, 1 row affected (0.00 sec)
  16. greatsql> SELECT * FROM t1;
  17. +---+-------------------------------------------------------+
  18. | a | b                                                     |
  19. +---+-------------------------------------------------------+
  20. | 1 | row1                                                  |
  21. | 2 | row2                                                  |
  22. | 3 | row3                                                  |
  23. | 4 | -trg_t1_before_update_or_insert UPDATE OR INSERT-row4 |
  24. +---+-------------------------------------------------------+
  26. greatsql> UPDATE t1 SET a = -3, b = '-row3' WHERE a = 3;
  27. Query OK, 1 row affected (0.00 sec)
  28. Rows matched: 1  Changed: 1  Warnings: 0
  30. greatsql> SELECT * from t1;
  31. +---+-------------------------------------------------------+
  32. | a | b                                                     |
  33. +---+-------------------------------------------------------+
  34. | 1 | row1                                                  |
  35. | 2 | row2                                                  |
  36. | 3 | -trg_t1_before_update_or_insert UPDATE OR INSERT-row3 |
  37. | 4 | -trg_t1_before_update_or_insert UPDATE OR INSERT-row4 |
  38. +---+-------------------------------------------------------+
  40. -- 而在 `DEFAULT` 模式下，相同触发事件按创建触发器的顺序逐个执行
  41. greatsql> SET sql_mode = ORACLE;
  43. greatsql> INSERT INTO t1 VALUES(-5, '-row5');
  44. Query OK, 1 row affected (0.00 sec)
  46. greatsql> SELECT * FROM t1;
  47. +---+-----------------------------------------------------------------------------------------------------------------------------------+
  48. | a | b                                                                                                                                 |
  49. +---+-----------------------------------------------------------------------------------------------------------------------------------+
  50. | 1 | row1                                                                                                                              |
  51. | 2 | row2                                                                                                                              |
  52. | 3 | -trg_t1_before_update_or_insert UPDATE OR INSERT-row3                                                                             |
  53. | 4 | -trg_t1_before_update_or_insert UPDATE OR INSERT-row4                                                                             |
  54. | 5 | -trg_t1_before_update_or_insert UPDATE OR INSERT-trg_t1_before_insert_or_update INSERT OR UPDATE-trg_t1_before_insert INSERT-row5 |
  55. +---+-----------------------------------------------------------------------------------------------------------------------------------+
  57. greatsql> UPDATE t1 SET a = -2, b = '-row2' WHERE a = 2;
  58. Query OK, 1 row affected (0.00 sec)
  59. Rows matched: 1  Changed: 1  Warnings: 0
  61. greatsql> SELECT * FROM t1;
  62. +---+-------------------------------------------------------------------------------------------------------------------------------------------------------+
  63. | a | b                                                                                                                                                     |
  64. +---+-------------------------------------------------------------------------------------------------------------------------------------------------------+
  65. | 1 | row1                                                                                                                                                  |
  66. | 2 | -trg_t1_before_update_or_insert UPDATE OR INSERT-trg_t1_before_update_or_delete UPDATE OR DELETE-trg_t1_before_insert_or_update INSERT OR UPDATE-row2 |
  67. | 3 | -trg_t1_before_update_or_insert UPDATE OR INSERT-row3                                                                                                 |
  68. | 4 | -trg_t1_before_update_or_insert UPDATE OR INSERT-row4                                                                                                 |
  69. | 5 | -trg_t1_before_update_or_insert UPDATE OR INSERT-trg_t1_before_insert_or_update INSERT OR UPDATE-trg_t1_before_insert INSERT-row5                     |
  70. +---+-------------------------------------------------------------------------------------------------------------------------------------------------------+
    1. 示例6：启用/禁用触发器
  1. greatsql> SET sql_mode = ORACLE;
  3. greatsql> ALTER TRIGGER trg_t1_before_update_or_delete DISABLE;
  4. Query OK, 0 rows affected (0.00 sec)
  6. greatsql> ALTER TRIGGER trg_t1_before_update_or_delete ENABLE;
  7. Query OK, 0 rows affected (0.00 sec)
  10. greatsql> SET sql_mode = DEFAULT;
  12. greatsql> ALTER TRIGGER trg_t1_before_update_or_delete DISABLE;
  13. Query OK, 0 rows affected (0.00 sec)
  15. greatsql> ALTER TRIGGER trg_t1_before_update_or_delete ENABLE;
  16. Query OK, 0 rows affected (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx