HighGo Database触发器使用案例（APP）

作者：瀚高PG实验室 （Highgo PG Lab)

目录

环境

文档用途

详细信息

 

环境

系统平台：Microsoft Windows (64-bit) 10

版本：5.6.4

 

文档用途

本文介绍瀚高数据库中查询触发器信息的方法及触发器的使用案例。

 

详细信息

一、触发器介绍

  HighGo Database触发器是先创建触发器函数，再创建触发器。触发器必须结合触发器函数来使用。

  1）pg_trigger表部分字段展示    

Name	Type	References	Description
oid	oid		Row identifier (hidden attribute; must be explicitly selected)
tgrelid	oid	pg_class.oid	The table this trigger is on
tgname	name		Trigger name (must be unique among triggers of same table)
tgfoid	oid	pg_proc.oid	The function to be called
tgisinternal	bool		True if trigger is internally generated (usually, to enforce   the constraint identified by tgconstraint)

2）列出当前数据库所有触发器

 

highgo=# select * from pg_trigger;

3）列举出特定表的触发器

 

highgo=# select pt.* from pg_class pc join pg_trigger pt on pt.tgrelid=pc.oid where relname='table_name';

4） 查询指定模式下的触发器，及其作用的表，使用的触发器函数

 

highgo=# select pc.oid tableoid, pc.relname "表名",            pt.oid triggeroid, pt.tgname "触发器名",            pp.oid functionoid, pp.proname "触发器函数名" from pg_trigger pt join pg_class pc on pc.oid=pt.tgrelid join pg_namespace pn on pc.relnamespace = pn.oid left join pg_proc pp on pt.tgfoid=pp.oid where tgisinternal=false -- 不是系统创建的 and pn.nspname ='schema_name';

二、触发器使用案例

1）Mysql ON UPDATE CURRENT_TIMESTAMP在hgdb实现

mysql：

CREATE TABLE tab_hgdb_tri_update_yf (   tab_id int(11) NOT NULL AUTO_INCREMENT,   tab_name VARCHAR(10),   insert_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,   update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (tab_id) );   mysql> insert into tab_hgdb_tri_update_yf(tab_name) VALUES ('12'); mysql> select * from tab_hgdb_tri_update_yf; +--------+----------+---------------------+---------------------+ | tab_id | tab_name | insert_time    | update_time     | +--------+----------+---------------------+---------------------+ |   1 | 12     | 2019-11-08 13:40:56 | 2019-11-08 13:40:56 | +--------+----------+---------------------+---------------------+ mysql> update tab_hgdb_tri_update_yf set tab_name='34' where tab_id=1 ; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> select * from tab_hgdb_tri_update_yf; +--------+----------+---------------------+---------------------+ | tab_id | tab_name | insert_time      | update_time      | +--------+----------+---------------------+---------------------+ |    1 | 34     | 2019-11-08 13:40:56 | 2019-11-08 13:43:02 | +--------+----------+---------------------+---------------------+

hgdb:

highgo=# CREATE TABLE tab_hgdb_tri_update_yf (     tab_id bigserial NOT NULL,     tab_name varchar(10) NULL,     insert_time timestamp NULL DEFAULT CURRENT_TIMESTAMP::timestamp(0) without time zone,     update_time timestamp NULL,     CONSTRAINT tab_update_default_pkey PRIMARY KEY (tab_id) );   highgo=# insert into tab_hgdb_tri_update_yf(tab_name) VALUES ('12'); highgo=# select * from tab_hgdb_tri_update_yf;  tab_id | tab_name |      insert_time       | update_time --------+----------+------------------------+-------------       1 | 12       | 2019-11-08 14:07:02 |   highgo=# CREATE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$       BEGIN        NEW.update_time := current_timestamp::timestamp(0) without time zone;        RETURN NEW;       END;       $update_timestamp$ LANGUAGE plpgsql;   highgo=# CREATE TRIGGER update_timestamp        BEFORE INSERT OR UPDATE ON tab_hgdb_tri_update_yf       FOR EACH ROW EXECUTE PROCEDURE update_timestamp();     highgo=# insert into tab_hgdb_tri_update_yf(tab_name) VALUES ('56'); highgo=# select * from tab_hgdb_tri_update_yf;  tab_id | tab_name |  insert_time   |  update_time --------+----------+------------------------+------------------------     1 | 12    | 2019-11-08 14:07:02 |     2 | 56    | 2019-11-08 14:08:00 | 2019-11-08 14:08:00   highgo=# update tab_hgdb_tri_update_yf set tab_name='01' where tab_id=2; highgo=# select * from tab_hgdb_tri_update_yf;  tab_id | tab_name |      insert_time       |      update_time --------+----------+------------------------+------------------------       1 | 12       | 2019-11-08 14:07:02 |       2 | 01       | 2019-11-08 14:08:00 | 2019-11-08 14:08:27

   2）触发器实现主键自增id及exception处理

CREATE TABLE tab_emp_seq_yf (     emp_id int,     emp_name varchar(50) );   create sequence seq_tab_emp_seq_yf_emp_id minvalue 1 start with 1 increment by 1;   create or replace FUNCTION fun_tri_tab_emp_seq_yf_before_insert() RETURNS trigger AS $tri_fun$           BEGIN     new.emp_id := nextval('seq_tab_emp_seq_yf_emp_id');     RETURN NEW;     exception       when others then        raise notice '%', '异常号:' || substr(to_char(SQLSTATE), 1, 200) || '; 异常信息:' || coalesce(sqlerrm::text,'');     return null; --此处一定要注意，根据自己的业务逻辑选择；如果是return null，则数据不会插入到表；return new，则emp_id列不会插入，其他列的数据会插入到表。 END; $tri_fun$ LANGUAGE plpgsql;   模拟异常： drop sequence seq_tab_emp_seq_yf_emp_id; 异常输出信息： 异常号:42P01; 异常信息:relation "seq_tab_emp_seq_yf_emp_id" does not exist

 3）before/after insert/update/delete操作

更多详细信息请登录【瀚高技术支持平台】查看https://support.highgo.com/#/index/docContentHighgo/529c561c03b6a1d2