CREATE TRIGGER

功能描述

  • 创建一个触发器。 触发器将与指定的表或视图关联，并在特定条件下执行指定的函数。
  • 对比原始openGauss语法，新增了使用MySQL 的格式创建触发器的语法。
  • 新增了使用单条sql创建触发器的语法。

注意事项

  • 当前仅支持在普通行存表上创建触发器，不支持在列存表、临时表、unlogged表等类型表上创建触发器。
  • 如果为同一事件定义了多个相同类型的触发器，则按触发器的名称字母顺序触发它们。
  • 触发器常用于多表间数据关联同步场景，对SQL执行性能影响较大，不建议在大数据量同步及对性能要求高的场景中使用。
  • 执行创建触发器操作的用户需要拥有指定表的TRIGGER权限或被授予了CREATE ANY TRIGGER权限。

语法格式

  • o风格创建触发器的语法
1. CREATE [ CONSTRAINT ] TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
2. ON table_name
3. [ FROM referenced_table_name ]
4. { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
5. [ FOR [ EACH ] { ROW | STATEMENT } ]
6. [ WHEN ( condition ) ]
7. EXECUTE PROCEDURE function_name ( arguments );
  • 兼容mysql兼容风格的创建触发器的语法
1. CREATE [ CONSTRAINT ] [ DEFINER=user ] TRIGGER [ IF NOT EXISTS ] trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
2. ON table_name
3. [ FROM referenced_table_name ]
4. { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
5. [ FOR [ EACH ] { ROW | STATEMENT } ]
6. [ WHEN ( condition ) ]
7. [ trigger_order ]
8. trigger_body

其中event包含以下几种：

1. INSERT
2. UPDATE [ OF column_name [, ... ] ]
3. DELETE
4. TRUNCATE

其中trigger_order是：

1. { FOLLOWS|PRECEDES } other_trigger_name

参数说明

  • CONSTRAINT

可选项，指定此参数将创建约束触发器，即触发器作为约束来使用。除了可以使用SET CONSTRAINTS调整触发器触发的时间之外，这与常规触发器相同。 约束触发器必须是AFTER ROW触发器。 - DEFINER

可选项，指定此参数可以影响触发器内引用对象的权限控制。 - IF NOT EXISTS

可选项，指定此参数如果触发器在相同的模式中具有相同的名称、相同的表、相同的表，则防止发生错误。 - trigger_name

触发器名称，该名称不能限定模式，因为触发器自动继承其所在表的模式，且同一个表的触发器不能重名。 对于约束触发器，使用SET CONSTRAINTS修改触发器行为时也使用此名称。

取值范围：符合标识符命名规范的字符串，且最大长度不超过63个字符。 - BEFORE

触发器函数是在触发事件发生前执行。 - AFTER

触发器函数是在触发事件发生后执行，约束触发器只能指定为AFTER。 - INSTEAD OF

触发器函数直接替代触发事件。 - event

启动触发器的事件，取值范围包括：INSERT、UPDATE、DELETE或TRUNCATE，也可以通过OR同时指定多个触发事件。

对于UPDATE事件类型，可以使用下面语法指定列：

1. UPDATE OF column_name1 [, column_name2 ... ]

表示当这些列作为UPDATE语句的目标列时，才会启动触发器，但是INSTEAD OF UPDATE类型不支持指定列信息。 - table_name

需要创建触发器的表名称。

取值范围：数据库中已经存在的表名称。 - referenced_table_name

约束引用的另一个表的名称。 只能为约束触发器指定，常见于外键约束。

取值范围：数据库中已经存在的表名称。 - DEFERRABLE | NOT DEFERRABLE

约束触发器的启动时机，仅作用于约束触发器。这两个关键字设置该约束是否可推迟。

详细介绍请参见CREATE TABLE。 - INITIALLY IMMEDIATE | INITIALLY DEFERRED

如果约束是可推迟的，则这个子句声明检查约束的缺省时间，仅作用于约束触发器。

详细介绍请参见CREATE TABLE。 - FOR EACH ROW | FOR EACH STATEMENT

触发器的触发频率。

  • FOR EACH ROW是指该触发器是受触发事件影响的每一行触发一次。
  • FOR EACH STATEMENT是指该触发器是每个SQL语句只触发一次。

未指定时默认值为FOR EACH STATEMENT。约束触发器只能指定为FOR EACH ROW。 - condition

决定是否实际执行触发器函数的条件表达式。当指定WHEN时，只有在条件返回true时才会调用该函数。

在FOR EACH ROW触发器中，WHEN条件可以通过分别写入OLD.column_name或NEW.column_name来引用旧行或新行值的列。 当然，INSERT触发器不能引用OLD和DELETE触发器不能引用NEW。

INSTEAD OF触发器不支持WHEN条件。

WHEN表达式不能包含子查询。

对于约束触发器，WHEN条件的评估不会延迟，而是在执行更新操作后立即发生。 如果条件返回值不为true，则触发器不会排队等待延迟执行。 - function_name

用户定义的函数，必须声明为不带参数并返回类型为触发器，在触发器触发时执行。 - arguments

执行触发器时要提供给函数的可选的以逗号分隔的参数列表。参数是文字字符串常量，简单的名称和数字常量也可以写在这里，但它们都将被转换为字符串。 请检查触发器函数的实现语言的描述，以了解如何在函数内访问这些参数。 - trigger_order

可选项，trigger_order特征中的{FOLLOWS|PRECEDES}控制触发器的优先触发顺序，B兼容性模式下允许对同一个表，在同一触发事件定义多个触发器，会按照触发器创建的先后顺序来决定触发的优先顺序（先创建的优先）。可以通过{FOLLOWS|PRECEDES}来调整优先级。使用FOLLOWS，最后一次使用的触发器与原始触发器最紧挨着，其他的触发器的优先级都顺序向后挤压；使用PRECEDES，最后一次使用的触发器与原始触发器最紧挨着，其他的触发器的优先级都顺序向前挤压。 - trigger_body

直接通过在begin…end之间书写代码块，定义触发器之后要完成的工作。

也可以是单条sql语句，目前支持的语句：insert、update、delete、set、call。

当设置了分隔符后，使用MySQL 风格的创建触发器的语法，trigger_body 的格式是按照MySQL 的格式规定书写的，declare 段落需要写在begin … end段落之间。

CREATE TRIGGER - 图1 说明：

关于触发器种类：

  • INSTEAD OF的触发器必须标记为FOR EACH ROW，并且只能在视图上定义。
  • BEFORE和AFTER触发器作用在视图上时，只能标记为FOR EACH STATEMENT。
  • TRUNCATE类型触发器仅限FOR EACH STATEMENT。

表 1 表和视图上支持的触发器种类：

触发时机 触发事件 行级 语句级
BEFORE INSERT/UPDATE/DELETE 表 表和视图
TRUNCATE 不支持 表
AFTER INSERT/UPDATE/DELETE 表 表和视图
TRUNCATE 不支持 表
INSTEAD OF INSERT/UPDATE/DELETE 视图 不支持
TRUNCATE 不支持 不支持

表 2 PLPGSQL类型触发器函数特殊变量：

变量名 变量含义
NEW INSERT及UPDATE操作涉及tuple信息中的新值，对DELETE为空。
OLD UPDATE及DELETE操作涉及tuple信息中的旧值，对INSERT为空。
TG_NAME 触发器名称。
TG_WHEN 触发器触发时机（BEFORE/AFTER/INSTEAD OF）。
TG_LEVEL 触发频率（ROW/STATEMENT）。
TG_OP 触发操作（INSERT/UPDATE/DELETE/TRUNCATE）。
TG_RELID 触发器所在表OID。
TG_RELNAME 触发器所在表名（已废弃，现用TG_TABLE_NAME替代）。
TG_TABLE_NAME 触发器所在表名。
TG_TABLE_SCHEMA 触发器所在表的SCHEMA信息。
TG_NARGS 触发器函数参数个数。
TG_ARGV[] 触发器函数参数列表。

示例

1. --创建源表及触发表
2. openGauss=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
3. openGauss=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

5. --创建触发器函数
6. openGauss=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
7. $$
8. DECLARE
9. BEGIN
10. INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
11. RETURN NEW;
12. END
13. $$ LANGUAGE PLPGSQL;

15. openGauss=# CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
16. $$
17. DECLARE
18. BEGIN
19. UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1;
20. RETURN OLD;
21. END
22. $$ LANGUAGE PLPGSQL;

24. openGauss=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
25. $$
26. DECLARE
27. BEGIN
28. DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
29. RETURN OLD;
30. END
31. $$ LANGUAGE PLPGSQL;

33. --创建INSERT触发器
34. openGauss=# CREATE TRIGGER insert_trigger
35. BEFORE INSERT ON test_trigger_src_tbl
36. FOR EACH ROW
37. EXECUTE PROCEDURE tri_insert_func();

39. --创建UPDATE触发器
40. openGauss=# CREATE TRIGGER update_trigger
41. AFTER UPDATE ON test_trigger_src_tbl
42. FOR EACH ROW
43. EXECUTE PROCEDURE tri_update_func();

45. --创建DELETE触发器
46. openGauss=# CREATE TRIGGER delete_trigger
47. BEFORE DELETE ON test_trigger_src_tbl
48. FOR EACH ROW
49. EXECUTE PROCEDURE tri_delete_func();

51. --执行INSERT触发事件并检查触发结果
52. openGauss=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
53. openGauss=# SELECT * FROM test_trigger_src_tbl;
54. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效。

56. --执行UPDATE触发事件并检查触发结果
57. openGauss=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
58. openGauss=# SELECT * FROM test_trigger_src_tbl;
59. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效

61. --执行DELETE触发事件并检查触发结果
62. openGauss=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
63. openGauss=# SELECT * FROM test_trigger_src_tbl;
64. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效

66. --修改触发器
67. openGauss=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

69. --禁用insert_trigger触发器
70. openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;

72. --禁用当前表上所有触发器
73. openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;

75. --删除触发器
76. openGauss=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
77. openGauss=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
78. openGauss=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;
79. --创建B兼容性数据库
80. openGauss=# create database db_mysql dbcompatibility 'B';
81. --创建触发器定义用户
82. openGauss=# create user test_user password 'Gauss@123';
83. --创建原表及触发表
84. db_mysql=# create table test_mysql_trigger_src_tbl (id INT);
85. db_mysql=# create table test_mysql_trigger_des_tbl (id INT);
86. db_mysql=# create table animals (id INT, name CHAR(30));
87. db_mysql=# create table food (id INT, foodtype VARCHAR(32), remark VARCHAR(32), time_flag TIMESTAMP);
88. --创建MySQL兼容definer语法触发器
89. db_mysql=# create definer=test_user trigger trigger1
90. after insert on test_mysql_trigger_src_tbl
91. for each row
92. begin
93. insert into test_mysql_trigger_des_tbl values(1);
94. end;
95. /
96. --创建MySQL兼容trigger_order语法触发器
97. db_mysql=# create trigger animal_trigger1
98. after insert on animals
99. for each row
100. begin
101. insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
102. end;
103. /
104. --创建MySQL兼容FOLLOWS触发器
105. db_mysql=# create trigger animal_trigger2
106. after insert on animals
107. for each row
108. follows animal_trigger1
109. begin
110. insert into food(id, foodtype, remark, time_flag) values (2,'chocolate', 'sdsdsdsd', now());
111. end;
112. /
113. db_mysql=# create trigger animal_trigger3
114. after insert on animals
115. for each row
116. follows animal_trigger1
117. begin
118. insert into food(id, foodtype, remark, time_flag) values (3,'cake', 'sdsdsdsd', now());
119. end;
120. /
121. db_mysql=# create trigger animal_trigger4
122. after insert on animals
123. for each row
124. follows animal_trigger1
125. begin
126. insert into food(id, foodtype, remark, time_flag) values (4,'sausage', 'sdsdsdsd', now());
127. end;
128. /
129. --执行insert触发事件并检查触发结果
130. db_mysql=# insert into animals (id, name) values(1,'lion');
131. db_mysql=# select * from animals;
132. db_mysql=# select id, foodtype, remark from food;
133. --创建MySQL兼容PROCEDES触发器
134. db_mysql=# create trigger animal_trigger5
135. after insert on animals
136. for each row
137. precedes animal_trigger3
138. begin
139. insert into food(id, foodtype, remark, time_flag) values (5,'milk', 'sdsds', now());
140. end;
141. /
142. db_mysql=# create trigger animal_trigger6
143. after insert on animals
144. for each row
145. precedes animal_trigger2
146. begin
147. insert into food(id, foodtype, remark, time_flag) values (6,'strawberry', 'sdsds', now());
148. end;
149. /
150. --执行insert触发事件并检查触发结果
151. db_mysql=# insert into animals (id, name) values(2, 'dog');
152. db_mysql=# select * from animals;
153. db_mysql=# select id, foodtype, remark from food;
154. --创建MySql
155. --创建MySQL兼容if not exists语法触发器
156. db_mysql=# create trigger if not exists animal_trigger1
157. after insert on animals
158. for each row
159. begin
160. insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
161. end;
162. /
163. --创建MySQL格式触发器
164. db_mysql=# delimiter //

166. db_mysql=# create trigger animal_d_trigger1
167. after insert on animals
168. for each row
169. begin
170. insert into food (id ,foodtype, remark, time_flag) values(1,'ice','avcs', now());
171. end;
172. //

174. db_mysql=# delimiter ;
175. --创建MySQL兼容trigger_body为单条sql语法触发器
176. db_mysql=# create trigger animal_trigger_single
177. after insert on animals
178. for each row
179. insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());

相关链接

ALTER TRIGGER，DROP TRIGGER，ALTER TABLE