目录
环境
文档用途
详细信息
环境
系统平台：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
