当前位置:网站首页>HighGo Database触发器使用案例(APP)
HighGo Database触发器使用案例(APP)
2021-01-06 12:02:56 【瀚高pg实验室】
本文介绍瀚高数据库中查询触发器信息的方法及触发器的使用案例。
一、触发器介绍
HighGo Database触发器是先创建触发器函数,再创建触发器。触发器必须结合触发器函数来使用。
1)pg_trigger表部分字段展示
Name |
Type |
References |
Description |
|
|
Row identifier (hidden attribute; must be explicitly selected) |
|
|
|
|
The table this trigger is on |
|
|
Trigger name (must be unique among triggers of same table) |
|
|
|
|
The function to be called |
|
|
True if trigger is internally generated (usually, to enforce the constraint identified by |
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 BEGIN NEW.update_time := current_timestamp::timestamp(0) without time zone; RETURN NEW; END; 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 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; 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
边栏推荐
- OPTIMIZER_TRACE详解
- 使用Consul实现服务发现:instance-id自定义
- OPTIMIZER_ Trace details
- Using consult to realize service discovery: instance ID customization
- Summary of common string algorithms
- Summary of common algorithms of linked list
- Linked blocking Queue Analysis of blocking queue
- 构建者模式(Builder pattern)
- Builder pattern
- Newbe.ObjectVisitor 样例 1
猜你喜欢
-
Newbe.ObjectVisitor Example 1
-
Farewell to runaway
-
LeetCode Algorithm 0060 - Permutation Sequence (Medium)
-
编程基础 - 栈的应用 - 混洗(Stack Shuffling)
-
LeetCode Algorithm 0060 - Permutation Sequence (Medium)
-
Fundamentals of programming stack shuffling
-
【色卡】常用色谱简析,中国传统颜色卡,代码附RBG,HC
-
[color card] brief analysis of commonly used chromatograms, Chinese traditional color cards, code with RBG, HC
-
MongoDB 副本集之入门篇
-
Introduction to mongodb replica set
随机推荐
- My name is mongodb, don't understand me. After reading my story, you will get started!
- roboguide破解安装教程
- Roboguide cracking installation tutorial
- The transformation of town street intelligent street lamp under the industrial intelligent gateway
- Remote smoke monitoring of environmental protection data acquisition instrument under Internet of things
- JS实现鼠标移入DIV随机变换颜色
- Flutter 页面中的异常处理ErrorWidget
- Exception handling errorwidget in fluent page
- Bolt's practice of route management of flutter (page decoupling, process control, function expansion, etc.)
- C语言系统化精讲 重塑你的编程思想 打造坚实的开发基础
- Skywalking系列博客6-手把手教你编写Skywalking插件
- Skywalking series blog 7 - dynamic configuration
- Skywalking series blog 6 - help you write skywalking plug-in
- 博客主机_自动申请续期免费证书
- Blog host_ Automatic renewal of free certificate
- 0x05 - 综合示例,导出 CSV
- 0x05 - synthesis example, export to CSV
- 0x02 - create and cache object visitors
- flutter圆形或线型进度条
- flutter给滚动内容添加粘性header组件
- Fluent round or linear progress bar
- Fluent adds sticky header components to scrolling content
- Typora uses latex to insert mathematical formulas
- 配电自动化终端dtu
- How to write a thesis opening report
- 基于C的PHP快速IP解析扩展,IP检测
- Based on C PHP fast IP resolution extension, IP detection
- 点击平滑滚动效果
- Click smooth scrolling effect
- Use case of highgo database trigger (APP)
- ES6之Map对象
- Flutter 最常出现的错误
- Flutter's most common mistakes
- 捕获 flutter app的崩溃日志并上报
- Capture and report the crash log of the flutter app
- SQL Server递归查询在Highgo DB中实现 (APP)
- Implementation of SQL Server recursive query in highgo dB (APP)
- 关于browserslist配置项
- About browserlist configuration items
- FTK1000使用视频一招搞定多模光损耗