CREATE TRIGGER
功能描述
CREATE TRIGGER语句用于创建一个触发器。触发器是一种特殊类型的存储过程,触发器主要用于复杂的业务规则和要求,有助于引用的完整性, 触发器将与指定的表或视图关联,并在特定条件下执行指定的函数,这样便于在添加、更新或删除表中的行时保留表中已定义的关系。
注意事项
- 当前仅支持在普通行存表上创建触发器,不支持在临时表、unlogged表等类型表上创建触发器。
- 如果为同一事件定义了多个相同类型的触发器,则按触发器的名称字母顺序触发它们。
- 触发器常用于多表间数据关联同步场景,对SQL执行性能影响较大,不建议在大数据量同步及对性能要求高的场景中使用。
- 执行触发器语句时是用触发器创建者的身份进行权限判断的。
- 执行创建触发器操作的用户需要拥有指定表的TRIGGER权限或被授予了CREATE ANY TRIGGER权限。
- BEFORE触发的行级触发器函数可以返回一个NULL值,表示忽略对该行的操作,随后的触发器将不再执行,并且不会对该行产生INSERT/UPDATE/DELETE动作。AFTER触发器函数返回值无影响。
- BEFORE DELETE的情况下,触发器函数返回值NEW等于NULL;BEFORE INSERT的情况下,触发器函数返回值OLD等于NULL;BEFORE UPDATE的情况下,触发器函数返回值只有显示为NULL才是NULL值。
- 对于event为INSERT/UPDATE的触发器函数,正常返回值是NEW。如果返回一个非NULL的行,将修改插入或者更新的行。对于event为DELETE的触发器函数,正常返回值是OLD。
语法格式
CREATE [OR REPLACE] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table_name [ FROM referenced_table_name ] { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } } [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] [ EXECUTE PROCEDURE function_name ( arguments ) | AnonyBlock ];
其中event包含以下几种:
1
|
INSERT |
1
|
UPDATE [ OF column_name [, ... ] ] |
1
|
DELETE |
1
|
TRUNCATE |
参数说明
- OR REPLACE
可选项,指定此参数将在TRIGGER已存在的前提下修改已有的TRIGGER。
无法通过OR REPLACE语法创建或修改CONSTRAINT TRIGGER和INTERNAL TRIGGER。CONSTRAINT TRIGGER是指通过CREATE CONSTRAINT TRIGGER创建的TRIGGER,CONSTRAINT TRIGGER是指某些SQL语句隐式创建的TRIGGER(如给表加上外键约束会隐式创建4个TRIGGER,这4个TRIGGER即为INTERNAL TRIGGER)。
- CONSTRAINT
可选项,指定此参数将创建约束触发器,即触发器作为约束来使用。除了可以使用SET CONSTRAINTS调整触发器触发的时间之外,这与常规触发器相同。 约束触发器必须是AFTER ROW触发器。
- name
触发器名称,该名称不能限定模式,因为触发器自动继承其所在表的模式,且同一个表的触发器不能重名。 对于约束触发器,使用SET CONSTRAINTS修改触发器行为时也使用此名称。
取值范围:符合标识符命名规范的字符串,且最大长度不超过63个字符。
- BEFORE
触发器函数是在触发事件发生前执行。
- AFTER
触发器函数是在触发事件发生后执行,约束触发器只能指定为AFTER。
- INSTEAD OF
触发器函数直接替代触发事件。
- event
启动触发器的事件,取值范围包括:INSERT、UPDATE、DELETE或TRUNCATE,也可以通过OR同时指定多个触发事件。
对于UPDATE事件类型,可以使用下面语法指定列:
UPDATE OF column_name1 [, column_name2 ... ]
表示当这些列作为UPDATE语句的目标列时,才会启动触发器,但是INSTEAD OF UPDATE类型不支持指定列信息。如果UPDATE 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,则触发器不会排队等待延迟执行。
- ANONYBLOCK
A数据库风格的匿名块,其中的语句在触发器触发时执行。使用匿名块语法创建trigger时会创建一个与trigger同名的函数,需要注意不能和现有函数名冲突。
- function_name
用户定义的函数,必须声明为不带参数并返回类型为触发器,在触发器触发时执行。
- arguments
执行触发器时要提供给函数的可选的以逗号分隔的参数列表。参数是文字字符串常量,简单的名称和数字常量也可以写在这里,但它们都将被转换为字符串。 请检查触发器函数的实现语言的描述,以了解如何在函数内访问这些参数。
关于触发器种类:
- 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为空,并且对于statement级别的触发器此变量也为空。
OLD
UPDATE及DELETE操作涉及tuple信息中的旧值,对INSERT为空,并且对于statement级别的触发器此变量也为空。
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[]
触发器函数参数列表。
示例
--创建源表及触发表。 gaussdb=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT); gaussdb=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
INSERT触发器的使用。
--创建insert触发器函数。 gaussdb=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS $$ DECLARE BEGIN INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3); RETURN NEW; END $$ LANGUAGE plpgsql; --创建INSERT触发器。 gaussdb=# CREATE TRIGGER insert_trigger BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_insert_func(); --执行INSERT触发事件并检查触发结果。 gaussdb=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300); gaussdb=# SELECT * FROM test_trigger_src_tbl; id1 | id2 | id3 -----+-----+----- 100 | 200 | 300 (1 row) gaussdb=# SELECT * FROM test_trigger_des_tbl; //查看触发操作是否生效。 id1 | id2 | id3 -----+-----+----- 100 | 200 | 300 (1 row)
匿名块和OR REPLACE语法创建触发器。
--使用匿名块语法创建INSERT触发器。 gaussdb=# CREATE TRIGGER insert_trigger_with_anonyblock BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW DECLARE BEGIN INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3); RETURN NEW; END; / --使用OR REPLACE语法创建INSERT触发器。 gaussdb=# CREATE OR REPLACE TRIGGER insert_trigger_with_anonyblock BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW DECLARE BEGIN INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3); RETURN NEW; END; / --删除触发器。 gaussdb=# DROP TRIGGER insert_trigger_with_anonyblock ON test_trigger_src_tbl; -- 会将隐式创建的函数insert_trigger_with_anonyblock一起删除
UPDATE触发器的使用。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
--创建UPDATE触发器函数。 gaussdb=# CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS $$ DECLARE BEGIN UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1; RETURN OLD; END $$ LANGUAGE plpgsql; --创建UPDATE触发器。 gaussdb=# CREATE TRIGGER update_trigger AFTER UPDATE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_update_func(); --执行UPDATE触发事件并检查触发结果。 gaussdb=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100; gaussdb=# SELECT * FROM test_trigger_src_tbl; id1 | id2 | id3 -----+-----+----- 100 | 200 | 400 (1 row) gaussdb=# SELECT * FROM test_trigger_des_tbl; //查看触发操作是否生效。 id1 | id2 | id3 -----+-----+----- 100 | 200 | 400 (1 row) |
DELETE触发器的使用。
--创建DELETE触发器函数。 gaussdb=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS $$ DECLARE BEGIN DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1; RETURN OLD; END $$ LANGUAGE plpgsql; --创建DELETE触发器。 gaussdb=# CREATE TRIGGER delete_trigger BEFORE DELETE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_delete_func(); --执行DELETE触发事件并检查触发结果。 gaussdb=# DELETE FROM test_trigger_src_tbl WHERE id1=100; gaussdb=# SELECT * FROM test_trigger_src_tbl; id1 | id2 | id3 -----+-----+----- (0 rows) gaussdb=# SELECT * FROM test_trigger_des_tbl; //查看触发操作是否生效。 id1 | id2 | id3 -----+-----+----- (0 rows)
修改触发器名称。
1 2 |
--修改触发器的名称。 gaussdb=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed; |
禁用触发器。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
--禁用insert_trigger触发器。 gaussdb=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger; gaussdb=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300); gaussdb=# SELECT * FROM test_trigger_src_tbl; id1 | id2 | id3 -----+-----+----- 100 | 200 | 300 (1 row) gaussdb=# SELECT * FROM test_trigger_des_tbl; //可以看到触发器没有生效。 id1 | id2 | id3 -----+-----+----- (0 rows) --禁用当前表上所有触发器。 gaussdb=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL; |
删除触发器。
1 2 3 4 5 |
gaussdb=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl; gaussdb=# DROP TRIGGER update_trigger ON test_trigger_src_tbl; gaussdb=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl; |
删除函数。
1 2 3 4 5 |
gaussdb=# DROP FUNCTION tri_insert_func; gaussdb=# DROP FUNCTION tri_update_func; gaussdb=# DROP FUNCTION tri_delete_func; |
1 2 3 |
--删除源表及触发表。 gaussdb=# DROP TABLE test_trigger_src_tbl; gaussdb=# DROP TABLE test_trigger_des_tbl; |