postgresql触发器

最新推荐文章于 2024-04-29 17:00:54 发布
最新推荐文章于 2024-04-29 17:00:54 发布
阅读量1.6k 收藏 9
点赞数 1
分类专栏： other
原文链接：https://patchouli-know.com/2017/01/07/postgresql-trigger/
版权

触发器，Trigger，是一种由事件自动触发执行的特殊的存储过程，这些事件可以是对一个表进行 insert、update、delete 等操作。说的通俗一些，触发器就是一个特殊的函数，它与一系列指定的操作关联，当这些操作发生时，会自动执行触发器的函数。

触发器可以作用在表或者视图上。创建触发器时需要指定这个触发器被触发时执行的函数, 这个函数就是触发器函数。触发器函数可以使用系统自带的过程语言(例如 plpgsql、pltcl、plperl、plpython )来写, 也可以使用 C 来写。本文中均以 plpgsql 作为示例。

为方便理解，我们还是先从一个简单的例子开始。假如有一张学生表，student，和一张考试成绩表，score，定义如下：

create table student (
	s_id int PRIMARY key,
	s_name varchar(64),
	age int
);

create table score (
	s_id int,
	score int,
	test_date date
);

现在想要在删除学生记录时，把这个学生对应的成绩记录也一起删除。这时就可以使用触发器。首先，我们创建触发器的执行函数：

CREATE OR REPLACE FUNCTION student_delete_function()
RETURNS TRIGGER AS $$
BEGIN
	DELETE FROM score WHERE s_id = OLD.s_id;
	RETURN OLD;
END;
$$
LANGUAGE plpgsql;

然后创建触发器：

CREATE TRIGGER delete_student_trigger
	AFTER DELETE ON student
	FOR EACH ROW EXECUTE PROCEDURE student_delete_function();

现在，我们可以插入一些测试数据：​INSERT INTO student VALUES(1, ‘张三’, 14); INSERT INTO student VALUES(2, ‘李四’, 14); INSERT INTO student VALUES(3, ‘王五’, 13); INSERT INTO score VALUES(1, 78, ‘2016-12-30’); INSERT INTO score VALUES(1, 79, ‘2016-12-31’); INSERT INTO score VALUES(2, 66, ‘2016-12-30’); INSERT INTO score VALUES(2, 90, ‘2016-12-31’); INSERT INTO score VALUES(3, 87, ‘2016-12-30’); INSERT INTO score VALUES(3, 79, ‘2016-12-31’);

此时把学号为3的学生从表 student 中删除：

DELETE FROM student where s_id = 3;

这时查询成绩表 score，可以发现学号为3的学生成绩记录也一起被删除掉了。

SELECT * FROM score;

 s_id | score | test_date
------+--------+--------
  1	  |  78	  |  2016-12-30
  1	  |  79	  |  2016-12-31
  2	  |  66	  |  2016-12-30
  2	  |  90	  |  2016-12-31

这就是一个触发器的简单示例。下面我们继续学习触发器的各种特性。

触发器的创建、查看与删除

触发器的创建

触发器的创建语法如下：

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

上面的语法中，有几个地方需要注意：

  1. { BEFORE | AFTER | INSTEAD OF } 表示触发器可以在指定操作之前触发，也可以在指定操作完成之后触发，也可以替代这个操作。
  2. { event [ OR ... ] } 表示触发器监听的操作类型，event 可以是 INSERT、UPDATE、DELETE、TRUNCATE 中的一个或者多个。
  3. FOR [ EACH ] { ROW | STATEMENT } 表示触发器是行级还是语句级。语句级的触发器，每个 SQL 会触发一次；而行级的触发器，每行操作都会触发一次。
  4. [ WHEN ( condition ) ] 表示决定触发器是否执行的条件。只有当 condition 为 true 时，触发器才会执行。
  5. name 表示触发器的名称，table_name 表示触发器监听的表名， function_name 表示触发器函数名。

触发器列表查看

若想查看当前数据库上的触发器列表，可以使用如下语句进行查询：

SELECT * FROM pg_trigger;

这将会列出当前数据库上的所有触发器，以及相关信息。

删除触发器

如果想删除一个触发器，可以使用 DROP 语句：

DROP TRIGGER trigger_name on table_name;

触发器函数

触发器函数定义了触发器被触发后执行的操作。触发器函数同普通函数一样，使用 CREATE FUNCTION 语法创建，只是没有传参，并且返回类型为 TRIGGER。当把一个函数作为触发器函数调用的时候，系统会在顶层的声明段里自动创建几个特殊变量，作为传参方式。比如最上面例子中的 OLD，就表示触发操作的旧数据行。下面列一下触发器函数中可以使用的变量：

  • NEW：INSERT、UPDATE 操作触发的行级触发器中存储的新的数据行，数据类型为 RECORD。语句级触发器，与 DELETE 操作触发的行级触发器中，此变量没有分配。
  • OLD：INSERT、UPDATE 操作触发的行级触发器中存储的旧的数据行，数据类型为 RECORD。语句级触发器，与 DELETE 操作触发的行级触发器中，此变量没有分配。
  • TG_NAME：数据类型为 name，该变量包含了实际触发的触发器名。
  • TG_WHEN：数据类型为 text，内容为 BEFORE、AFTER、INSTEAD OF，取决于触发器的定义。
  • TG_LEVEL：数据类型为 text，内容为 ROW、STATEMENT，取决于触发器的定义。
  • TG_OP：数据类型为 text，内容为 INSERT、UPDATE、DELETE、TRUNCATE，代表当前触发器监听到的操作类型。
  • TG_RELID：数据类型为 oid，表示触发器被触发的表的 OID。
  • TG_RELNAME：数据类型为 name，表示触发器被触发的表名。已废弃。
  • TG_TABLE_NAME：数据类型为 name，表示触发器被触发的表名。
  • TG_TABLE_SCHEMA：数据类型为 name，表示触发器被触发的模式名。
  • TG_NARGS：数据类型为 integer，表示创建触发器语句中赋予触发器函数的参数个数。
  • TG_ARGV[]：数据类型为 text 的数组，是创建触发器语句中的参数。

我们再来用一个简单的例子来说明。我们创建一个新的触发器，执行的操作是将上面的变量保存到记录表中。首先我们先创建记录表：

create table trigger_detail (
	r_new text,
	r_old text,
	tg_name text,
	tg_when text,
	tg_level text,
	tg_op text,
	tg_relid text,
	tg_relname text,
	tg_table_name text,
	tg_table_schema text,
	tg_nargs text,
	tg_argv text[]
);

接下来创建触发器函数。函数中会将上面的参数插入到记录表中。

CREATE OR REPLACE FUNCTION trigger_detail_function()
RETURNS TRIGGER AS $$
BEGIN
	INSERT INTO trigger_detail values(NEW.s_id, OLD.s_id, TG_NAME, TG_WHEN,
	TG_LEVEL, TG_OP, TG_RELID, TG_RELNAME, TG_TABLE_NAME, TG_TABLE_SCHEMA, TG_NARGS, TG_ARGV);
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

最后创建触发器。

CREATE TRIGGER detail_trigger
	AFTER UPDATE ON student
	FOR EACH ROW EXECUTE PROCEDURE trigger_detail_function();

创建好以后，我们执行一个更新语句，执行后即可在记录表中看到触发器参数的记录。

UPDATE student set s_id = 4 where s_id = 2;
select * from trigger_detail;

| r_new | r_old | tg_name             | tg_when | tg_level | tg_op  | tg_relid | tg_relname | tg_table_name | tg_table_schema | tg_nargs | tg_argv |
+-------+-------+---------------------+---------+----------+--------+----------+------------+---------------+-----------------+----------+---------+
| 4     | 2     | log_student_trigger | AFTER   | ROW      | UPDATE | 22923647 | student    | student       | public          | 0        | NULL    |

行级触发器与语句级触发器

触发器按触发级别，可以分为行级触发器与语句级触发器：

  • 行级触发器是指每行数据的操作都会执行一次。也就是 SQL 语句影响了多少行，触发器就执行几次。
  • 语句级触发器是指执行每个 SQL 时，触发器只执行一次。也就是有几个 SQL 语句，触发器就执行几次。

我们为 student 表新创建一个触发器，用来记录 student 的操作日志。日志表如下：

create table log_student (
	update_time TIMESTAMP,  --操作时间
	db_user VARCHAR(40),    --操作的数据库用户名
	op_type VARCHAR(6)      --操作类型
);

创建触发器函数。每当 student 表发生变更时，触发器会将时间、用户名与操作类型插入到日志表中。

CREATE OR REPLACE FUNCTION log_student_function()
RETURNS TRIGGER AS $$
BEGIN
	INSERT INTO log_student values(now(), USER, TG_OP);
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

然后我们创建一个语句级的触发器：

CREATE TRIGGER log_student_trigger
	AFTER UPDATE OR INSERT OR DELETE ON student
	FOR EACH STATEMENT EXECUTE PROCEDURE log_student_function();

现在清空 student 表，并执行插入语句：

INSERT INTO student VALUES(1, '张三', 14), (2, '李四', 14), (3, '王五', 13);

select * from log_student;

| update_time                | db_user  | op_type |
+----------------------------+----------+---------+
| 2017-01-07 16:35:47.971886 | dpmp_dev | INSERT  |
+----------------------------+----------+---------+
1 row in set

可以看到，虽然是插入了三条记录，但是日志表中只存在一条记录，这是因为语句级的触发器，每条 SQL 语句触发一次触发器。

现在我们删除这个触发器，并创建一个新的行级触发器。

DROP TRIGGER log_student_trigger on student;

CREATE TRIGGER log_student_trigger
	AFTER UPDATE OR INSERT OR DELETE ON student
	FOR EACH ROW EXECUTE PROCEDURE log_student_function();

创建后，再来执行一下上面的插入语句：

INSERT INTO student VALUES(1, '张三', 14), (2, '李四', 14), (3, '王五', 13);

select * from log_student;

| update_time                | db_user  | op_type |
+----------------------------+----------+---------+
| 2017-01-07 16:40:43.262647 | dpmp_dev | INSERT  |
| 2017-01-07 16:40:43.262647 | dpmp_dev | INSERT  |
| 2017-01-07 16:40:43.262647 | dpmp_dev | INSERT  |
+----------------------------+----------+---------+
3 rows in set

可以看到，这时日志表里是三条记录，也就是说，行级触发器中，SQL 语句影响了几行数据，就会触发几次触发器。

触发器的触发时机

触发器的触发时机包括三种：

  • BEFORE：在操作进行前触发
  • AFTER：在操作完成之后触发
  • INSTEAD OF：使用触发器的动作来替代操作

其中，INSTEAD OF 仅可以定义在视图上，这里不做过多介绍。BEFORE 与 AFTER 分别表示操作前与操作后进行触发操作。这里的操作前与操作后又与触发器的触发级别相关。对于同一事件上的多个触发器，会按照如下的顺序来触发：

  1. before for each statement
  2. before for each row
  3. after for each row
  4. after for each statement

BEFORE 触发器可以直接修改 NEW 值来改变实际的更新值。我们创建一个触发器函数与对应的触发器：

CREATE OR REPLACE FUNCTION update_student_function()
RETURNS TRIGGER AS $$
BEGIN
	NEW.s_name = NEW.s_id || '-' || NEW.s_name;
	RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER update_student_trigger
	BEFORE UPDATE OR INSERT ON student
	FOR EACH ROW EXECUTE PROCEDURE update_student_function();

通俗的讲，在进行数据库插入或者更新操作时，NEW 就表示要插入或者更新的记录。通过修改 NEW 中的值，我们间接的影响了数据库插入或者更新后的结果。上面的函数就表示将更新的学生姓名改为学号 + 姓名的形式。这时我们再来执行插入语句，会得到下面的结果：

select * from student;

| s_id | s_name | age |
+------+--------+-----+
|    1 | 1-张三 |  14 |
|    2 | 2-李四 |  14 |
|    3 | 3-王五 |  13 |
+------+--------+-----+
3 rows in set

而如果上面的例子使用 AFTER，修改 NEW 是没有用的，因为 NEW 中的值已经更新到了表中。

在这里还有个小细节，就是关于触发器函数的返回值。语句级的触发器总是返回 NULL，必须显示的在触发器函数中加上 RETURN NULL，如果没有写，会导致创建触发器出错。对于 BEFORE 和 INSTEAD OF 这类行级触发器来说，如果返回 NULL，则表示忽略对当前行的更新操作。如果返回非 NULL 的行，对于 INSERT 与 UPDATE 操作来说，返回的行将成为被插入的行或者更新的行。而对于 AFTER 的行级触发器来说，其返回值会被忽略。

总结

本文简单的对 PostgreSQL 中的触发器进行了简单的介绍。最后我们再来总结一下触发器的相关知识：

  • 触发器是一种由事件自动触发执行的特殊的存储过程，它的本质是将一个触发器函数与在指定的表上的指定操作关联，当指定表上进行了指定的操作后，会自动执行触发器函数。
  • 触发器分为行级触发器与语句级触发器。行级触发器中，每行数据的更改都会触发操作；而语句级触发器中，一条 SQL 语句会触发一次操作。
  • 触发器可以监听的操作类型包括 INSERT、UPDATE、DELETE、TRUNCATE。
  • 触发器触发的时机分为 BEFORE、AFTER、INSTEAD OF。其中 INSTEAD OF 仅可在视图上操作。
  • 触发器函数可以由系统自带的过程语言或 C 语言编写，其传参为空，返回类型为 TRIGGER。
Meiko丶
关注
  • 1
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
postgresql 触发器的操作
冰雪中的昙花
05-20 5895
PostgreSQL的触发器是数据库自动执行指定的数据库事件发生时调用的回调函数。以下是有关PostgreSQL的触发器的要点： PostgreSQL的触发可以指定触发操作前尝试一行（在检查约束之前INSERT，UPDATE或DELETE）或操作完成后（在检查约束之后和INSERT，UPDATE或DELETE（删除）已完成），或替代的操作（在视图上插入，更新或删除的情况下）.. F
postgresql使用触发器分表
phq12的博客
04-27 1304
分表的概念和好处请自行查阅，与其他方法相比的优劣也不赘述，这里只介绍一种分表的方法。 使用触发器分表：每月一张表，分表表名格式mainNameYYYYMM。 第一步：创建数据库存储过程，插入数据时按照规则插入不同分表。插入时如果违反分表的约束会报错。 CREATE OR REPLACE FUNCTION auto_insert_into_table() RETURNS trigger ...
触发器：解密PostgreSQL数据库的强大利器
热门推荐
陈书予
05-14 1万+
触发器（Trigger）是一种特殊的存储过程，它可以在数据库管理系统中监测到某些特定的事件，如 INSERT、UPDATE 或 DELETE 操作，并在这些事件发生时自动执行相应的操作。
PostgreSql 触发器
脑子进水养啥鱼？的博客
07-11 6633
PostgreSQL从9.3 版开始支持一种称为"Event Trigger"的触发器，这种触发器主要用于弥补PostgreSQL 以前版本不支持 DDL 触发器的不足。由于事件触发器涉及的权限较大，比如能禁止 DDL 操作等，所以只有超级用户才能创建和修改事件触发器。目前，事件触发器支持以下 3 种 DDL 事件。：DDL开始执行前被触发。：一个DDL执行完成后被触发。sql_drop：删除数据库对象前被触发。
【PostgreSQL】pg触发器介绍
hdhdhdk的博客
04-29 744
如果您具有 SELECT 权限，则可以引用以 NEW 命名的列。在 BEFORE 触发器中，如果您具有 UPDATE 权限，则还可以使用 SET NEW.col_name = value 更改其值。触发器是在对指定表执行指定更改操作（SQL INSERT、UPDATE、DELETE 或 TRUNCATE 语句）时自动运行的一组操作。在上面的触发器函数中，有一个新的关键字“NEW”，它是触发器的 PostgreSQL 扩展。现在，我们可以创建触发器，该触发器将在执行时触发关联表的触发器中指定的事件。
PostgreSQL中的触发器
HighGO
04-05 1901
作者：瀚高PG实验室（HighgoPGLab） 丹心明月 注：本文章主要翻译自《PostgreSQL 13.0 Documentation》第三十八章 本章介绍编写函数触发器的相关信息。触发器函数可使用大部分可用的过程语言编写，包括PL/pgSQL（参见第42章），PL/Tcl（参见第43章），PL/Perl（参见第44章），和PL/Python（参见第45章）。阅读完本章后，可根据自己的喜好，到相应章节查看对应语言编写触发器的具体细节。 也可以使用C语言编写触发器，不过使用过程语言更简...
无涯教程-PostgreSQL - Triggers(触发器)
无涯教程
12-27 598
PostgreSQL 触发器是数据库回调函数，当发生指定的数据库事件时，它们会自动执行。
postgresql-触发器
Java_Fly1的博客
09-23 829
PostgreSQL 触发器（trigger）是一种特殊的函数，当某个数据变更事件（INSERT、UPDATE、DELETE 或者 TRUNCATE 语句）或者数据库事件（DDL 语句）发生时自动执行，而不是由用户或者应用程序进行调用基于某个表或者视图数据变更的触发器被称为数据变更触发器（DML 触发器），基于数据库事件的触发器被称为事件触发器（DDL 触发器）。一般我们更多使用的是数据变更触发器行级（row-level）触发器和 语句级（statement-level）触发器。
postgresql触发器详解
weixin_73350116的博客
08-16 2633
触发器（Trigger）是由事件自动触发执行的一种特殊的存储过程，触发事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。触发器经常用于加强数据的完整性约束和业务规则上的约束等在数据库中，为了提高自动化效率，往往会配置一些触发器，由某一事件的发生而触发运行指定的函数，增加数据库运行的自动化效率。在postgresql数据库中分为普通触发器和事件触发器在用法上有区别。
ptgo：Go中的PostgreSQL触发器
02-05
Go中的PostgreSQL触发器ptgo是如何在Go中编写PostgreSQL触发器的示例。 分叉它并使用它来实现自己的触发器！ 查看，以获取有关ptgo内部的描述。 今天就在Twitter上关注我们！
node-pgtrigger:管理版本化的PostgreSQL触发器
05-14
节点编译器此存储库包含用于从Node模块管理版本化的PostgreSQL触发器的代码。 有关详细信息，请参见代码。 这种方法似乎可行，但由于基于SQL的方法而被放弃。
worker：高性能Node.jsPostgreSQL作业队列（也适用于使PostgreSQL触发器生成的作业将函数触发到另一个工作队列中）
02-05
可以与任何PostgreSQL支持的应用程序一起使用。 与或。 众筹的开源软件 为了帮助我们在MIT许可下可持续地开发该软件，我们要求使用该软件的所有个人和企业通过赞助来帮助支持其正在进行的维护和开发。 并且请给...
postgresql-to-sqlite:易于使用的解决方案，可以从Postgresql Dump创建sqlite数据库
05-26
PostgreSQL到sqlite（pg2sqlite） 易于使用的解决方案，可以从postgresql dump创建sqlite数据库。 默认脚本格式 尽可能快地 默默忽略不支持的Postgresql功能 gzip支持 在发行部分中，您可以下载pg2sqlite.jar的预...
postgresql.jar
最新发布
06-24
它支持多种数据类型，包括标准SQL数据类型以及自定义类型，提供丰富的函数库，并且具有强大的触发器和存储过程功能。 **二、Java JDBC驱动** Java JDBC（Java Database Connectivity）是Java平台中用于与各种...
PostgreSql TRIGGER 触发器简单样例
nioqnw的博客
11-29 4485
1.创建测试表、视图 CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); CREATE ...
PostgreSQL Trigger
Free Labor 的专栏
08-20 627
1. Create two tables, there are a1 and a2. table: a1 table a2: 2. Create a function:record_a1 CREATE OR REPLACE FUNCTION record_a1() returns trigger as $$ declare nCnt int; begin select count(0) into nCnt from a2 where column1=new.column1; if ...
postgreSQL触发器
qq_25775675的博客
09-05 5126
触发器是某个数据库操作发生时被**自动调用的函数**。可以在`INSERT`、`UPDATE`或`DELETE`操作之前或之后调用触发器。`PostgreSQL`支持两种类型的触发器，一种是**数据行级触发器**，另外一种是**语句级触发器**。对于数据行级的触发器，触发发触发器的语句每操作一个数据行，它就被执行一次。对于语句级的触发器，它只会被执行一次。
PostgreSQL教程：触发器
学亮编程手记
09-17 708
它们可以在导致触发器事件的语句末尾被引发或者在包含该语句的事务末尾被引发。当约束触发器实现的约束被违背时，约束触发器应该抛出一个异常。操作时，会触发表的Trigger（看触发器的创建时指定的事件）在简单了解了一下plpgsql的语法后，编写一个触发器函数。开始构建触发器，在学生信息表删除时，执行前面声明的触发器函数。这和一个常规触发器相同，不过触发该触发器的时机可以使用。在删除学生信息的同时，自动删除学生的分数。构建两张表，学生信息表，学生分数表。构建一个删除学生分数的触发器函数。约束触发器必须是表上的。
PostgreSQL 触发器
吴均的博客
01-19 839
PostgreSQL 触发器 PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。 下面是关于 PostgreSQL 触发器几个比较重要的点： PostgreSQL 触发器可以在下面几种情况下触发： 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。 在执行操作之后（在检查约束并插入、更新或删除完成之后）。 更新操作（在对一个视图...
postgresql 触发器函数
08-17
在PostgreSQL中，触发器函数是一个没有参数并且返回trigger类型的函数。在创建触发器之前，首先需要创建这样一个函数。触发器函数的基本语法如下： CREATE FUNCTION 触发器函数名称() RETURNS trigger AS $$ BEGIN...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值