当前位置:网站首页>HighGo Database触发器使用案例(APP)

HighGo Database触发器使用案例(APP)

2021-01-06 12:02:56 瀚高pg实验室

目录
环境
文档用途
详细信息
 
环境
系统平台: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 u p d a t e t i m e s t a m p update_timestamp

      BEGIN

       NEW.update_time := current_timestamp::timestamp(0) without time zone;

       RETURN NEW;

      END;

      u p d a t e t i m e s t a m p 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 t r i f u n 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;

t r i f u n 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

版权声明
本文为[瀚高pg实验室]所创,转载请带上原文链接,感谢
https://my.oschina.net/u/4908520/blog/4881251

随机推荐