1. 首页
  2. 文章中心
  3. other

postgresql触发器

74 阅读 0 评论
我是靠谱客的博主 酷酷发卡，最近开发中收集的这篇文章主要介绍postgresql触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

触发器，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。

最后

以上就是酷酷发卡为你收集整理的postgresql触发器的全部内容，希望文章能够帮你解决postgresql触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(49)

相关文章

AntDB属性丢失问题分析
AntDB属性丢失问题分析
JS判断手机是否装了APP，若装了则打开APP，否则跳转到相应的下载链接
JS判断手机是否装了APP，若装了则打开APP，否则跳转到相应的下载链接
Postgresql 自增语句
Postgresql 自增语句
js判断手机是否安装某款app，有则打开，没有去下载
js判断手机是否安装某款app，有则打开，没有去下载
postgresql触发器
postgresql触发器
postgresql之查看pg_wal目录下xlog文件总大小
postgresql之查看pg_wal目录下xlog文件总大小
PostgreSQL学习笔记-系统概述
PostgreSQL学习笔记-系统概述
js调用APP后，如果有App直接进App，没有则进入App下载页面
js调用APP后，如果有App直接进App，没有则进入App下载页面

评论列表共有 0 条评论

立即
投稿
返回
顶部