PostgreSQL数据库触发器简介

阅读： 5,960

PostgreSQL数据库触发器是在PostgreSQL数据库中经常会遇到的，在本篇文章中，我将简单的介绍下触发器，并用一个父子表的例子演示编写触发器的过程。

在这一部分中，我将简单介绍PostgreSQL数据库触发器和触发器函数，以及触发器涉及到的一些sql。
1.1 触发器
PostgreSQL数据库触发器按照官方的定义就是：A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed，
其中可以理解为数据库自动执行或指定的数据库事件发生时调用的回调函数。它既可以绑定在数据库表上，也可以绑定在数据库视图上。
创建触发器的基本语法如下图所示，其中event可以为 INSERT、UPDATE、DELETE、TRUNCATE中的一个。

- 触发器可以在指定触发操作前（在检查约束之前INSERT，UPDATE或DELETE）或操作完成后（在检查约束之后和INSERT，UPDATE或DELETE已完成），或替代的操作（视图上INSERT，UPDATE或DELETE）等情况下执行触发器函数。
- FOR EACH ROW触发器被标记的操作修改的每一行被称为一次；FOR EACH STATEMENT触发器为只执行一次对于任何给定的操作，不关心其修改多少行。
- 若有WHERE子句，则只执行WHERE子句为TRUE的行；若无，则执行所有行。
- 同一张表或视图上可以创建多个触发器，触发器调用的顺序和触发器的类型有关，其中表上各类触发器调用顺序如下所示：
```
BEFORE FOR EACH STATEMENT > BEFORE FOR EACH ROW > AFTER FOR EACH ROW >AFTER FOR EACH STATEMENT，视图上各类触发器调用顺序如下：BEFORE FOR EACH STATEMENT > INSTEAD FOR EACH ROW > AFTER FOR EACH STATEMENT。
```

 如果一张表或视图上有多个同类触发器，则按照触发器名称的排序进行调用，例如

trigger1 > trigger2 > trigger3。

1.2 触发器函数
触发器函数的官方定义就是：The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)，触发器函数就是在创建触发器时需要指定触发器被触发时执行的函数，它的返回类型是trigger，其必须在创建触发器之前定义。如果需要给触发器函数传入参数, 不能定义在触发器函数的参数列表中, 而是通过其他方式传入(TriggerData数据结构)。
定义一个触发器函数的语法如下所示（触发器函数中定义变量，赋值和控制结构等部分参照第二部分中的例子）：

编写触发器函数的语言有多种，例如plpgsql、pltcl、plperl、plpython、C，使用plpgsql写的触发器函数, 通过变量TG_ARGV[]来接收传入的变量值。
可以利用RAISE语句报告信息和抛出错误，其声明形式为：
例如：

同一个触发器函数可以多次被触发器调用。
在定义完触发器函数后，就可以创建触发器，使得触发器函数被触发器调用，并与数据库表或者视图绑定。
1.3 触发器查询和删除sql
查询当前数据库中所有触发器：

SELECT * FROM pg_trigger;

查询指定表上的触发器名称：

SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='table_name';

删除一个触发器：

DROP TRIGGER trigger_name;

父子表结构

父子表是一种数据在插入到父表后，也会按照相应规则创建对应的子表，并插入到该张子表中的结构（假如父表是applog，2017年6月5日的日志在插入applog这张父表后，也会同时根据当天时间插入到applog_20170605这张子表中）。这种按天区分的父子表结构，可以在父表数据量很大的时候，通过查询特定天的子表，避免查询数据量大的表，从而提高查询性能。
这种生成父子表的方式是通过编写数据库触发器的方式实现的，下面我将实现一个类似父子表结构，用来介绍创建数据库触发器的过程。假如父表是一张表ip_number，其中存储各个ip出现的次数，在插入数据到父表的同时需要按照该ip出现的次数分别创建ip_number_thousand、ip_number_thousand_million、ip_number _million三张子表，并将数据插入到对应次数区间的子表中。
2.1 创建父表结构
创建如下图的父表结构：其中假设限定number的最大次数是1000000000。

2.2 创建触发器函数（plpgsql编写实现触发器函数）
（1）触发器函数中变量声明和子表区间边界值赋值

（2）子表名称赋值和检查子表是否存在

（3）若子表不存在，创建子表和RULE

触发器函数会在往父表插入数据时执行，生成子表，并创建规则。从而使得往父表插入数据时，自动创建对应区间的子表，并也将数据插入到对应的子表中。
2.3 创建触发器
创建触发器，向父表中插入数据时执行触发器函数。

最后父表结构如下图所示：

sql查询ip_number表上的触发器和触发器详情：

2.4 测试
在测试中，分别向父表中插入number 为100，10000，10000000的三条数据。

通过观察父表ip_number，可以看出触发器和规则均已生效。

三种类型的子表成功自动创建，并已插入数据。

按照以上方式可以实现PostgreSQL数据库触发器，并创建父子表结构。

对于以上的部分，如果有不正确或者更好的地方，希望大家帮忙指正。谢谢！
参考连接：

http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html
http://www.postgresql.org/docs/9.2/static/trigger-definition.html
http://www.postgresql.org/docs/9.2/static/trigger-interface.html
http://www.php100.com/manual/PostgreSQL8/infoschema-tables.html

PostgreSQL数据库触发器简介