人大金仓数据库KingbaseES 触发器介绍之行级触发器

金仓数据库KingbaseES 触发器介绍之行级触发器

关键字：

KingbaseES、触发器、人大金仓、KingbaseES

触发器介绍

当我们对表进行数据操作时，需要同步对表执行相应操作，正常情况下，我们使用sql语句进行更新，需要执行多条SQL语句。如果采用触发器的形式，当执行相应操作时，就会触发执行触发器定义操作。下面对KingbaseES触发器使用进行介绍。

KingbaseES的触发器在触发时间上可以分为：前置触发器（在语句执行前触发）、instead of触发器、后置触发器和延迟触发器。触发器在执行粒度上可以分为：语句触发器和行级触发器。

触发器的使用

KingbaseES中，触发器创建语法可以分为两类：在创建触发器前定义执行函数、在创建触发器时定义执行函数。具体语法如下：

create_trigger ::= CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] TRIGGER plsql_trigger_source plsql_trigger_source ::= [ schema. ]trigger_name { simple_dml_trigger | instead_of_dml_trigger } simple_dml_trigger ::= { BEFORE | AFTER } dml_event_clause [ referencing_clause ] [ FOR EACH ROW ] [ WHEN ( condition ) ] trigger_body instead_of_dml_trigger ::= INSTEAD OF { DELETE | INSERT | UPDATE } [ OR { DELETE | INSERT | UPDATE } ]... ON [ schema. ] noneditioning_view [ referencing_clause ] [ FOR EACH ROW ] trigger_body dml_event_clause ::= { DELETE | INSERT | UPDATE [ OF column [, column ]... ] } [ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }... ON [ schema.] { table | view } trigger_body ::= { plsql_block | CALL routine_clause } routine_clause ::= [ schema. ] [ type. ] { procedure | method } ( )

• 前置触发器的使用:触发器将在语句执行前执行
• 创建前置触发器

--创建表 create table base1(id int, name text); CREATE OR REPLACE FUNCTION trigger_before () RETURNS TRIGGER AS $$ BEGIN New.id := new.id +1; return NEW; END; $$ LANGUAGE plpgsql; create trigger base1_before before insert on base1 for EACH ROW execute procedure trigger_before (); insert INTO base1 values(1,'trigger'); --查询插入结果，发现插入列数据被加1 SELECT * from base1;

• instead of 触发器的使用：执行后将不在执行相关操作。

--创建第二个表 create table base2(id int, birthday text); --INSTEAD OF 触发器 CREATE OR REPLACE FUNCTION trigger_instead () RETURNS TRIGGER AS $$ BEGIN Insert into base2 VALUES (NEW.ID,new.name); return NULL; END; $$ LANGUAGE plpgsql; CREATE VIEW base1_view as SELECT * from base1; create trigger base1_instead instead of insert on base1_view for EACH ROW execute procedure trigger_instead (); INSERT into base1_view values(1,'instead_of_trigger'); --base1并没有插入相关数据 SELECT * FROM base1; --数据插入到了base2中 SELECT * FROM base2;

• 后置触发：将在操作执行后进行触发

Drop trigger base1_before; --after 触发器 CREATE OR REPLACE FUNCTION trigger_after () RETURNS TRIGGER AS $$ BEGIN UPDATE base1 set id =3; return NULL; END; $$ LANGUAGE plpgsql; create trigger base1_after after insert on base1 for EACH ROW execute procedure trigger_after (); insert INTO base1 values(1,'trigger'); --后置触发UPDATE修改了插入结果 SELECT * FROM base1;

• 延迟触发器：将在事务提交时触发

Drop trigger base1_after; --创建延迟 触发器 CREATE OR REPLACE FUNCTION trigger_DEFERRED () RETURNS TRIGGER AS $$ BEGIN UPDATE base1 set id =3; return NULL; END; $$ LANGUAGE plpgsql; create CONSTRAINT trigger base1_DEFERRED after insert or update or DELETE on base1 INITIALLY DEFERRED for EACH ROW execute procedure trigger_DEFERRED (); --清除原有数据 DELETE from base1; DELETE from base2; --在事务中插入数据 Begin INSERT INTO base1 values(1,'TRIGGER_ori'); SELECT * from base1; --事务块结束后再进行查询，可以获取触发器执行结果 End; SELECT * from base2;

总结

本文主要介绍了行级触发器的创建和使用。前置的行级触发器将在执行操作前触发，instead of触发器将在替换原有操作，后置触发器将在执行操作后触发，延迟触发器将在事务提交阶段进行触发。

参考资料

《KingbaseES产品手册》