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