金仓数据库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;

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;

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;

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产品手册》](http://help.kingbase.com.cn/development/sql-plsql/sql/SQL_Statements_6.html#create-trigger)

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

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

关键字：

触发器介绍

触发器的使用