当前位置：首页 > news >正文

【postgresql初级使用】触发器的创建删除，你不知道的触发器函数中的系统变量，数据一致性还可以这样来保证

news 来源：原创 2024/9/29 2:53:12

使用触发器

专栏内容：

postgresql使用入门基础
手写数据库toadb
并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

文章目录

使用触发器
概述
创建触发器
- 触发器函数
删除触发器
案例分析
- 数据准备
- 动作函数
- 增加触发器
- 触发结果
总结
结尾

概述

前一节介绍了触发器的原理，以及在postgresql中支持的触发器的类型，有基于行的触发器，也有基于SQL语句的触发器，同时触发的位置可以是执行前，也可以是执行后。

从本节开始介绍触发器的创建，使用，通过案例看看它们的什么样的区别。

创建触发器

触发器的创建语法如下：

CREATE TRIGGER trigger_name {BEFORE | AFTER} ON table_name[FOR [EACH] { ROW | STATEMENT }]EXECUTE { FUNCTION | PROCEDURE } trigger_function;

语法说明

触发器创建使用关键字 create trigger, 然后指定触发器的名称；
使用关键字before 或者 after来指定是在执行前或者执行后触发；
on指定触发器作用的表或视图；
选择行触发器还是语句级触发器，行触发器使用for each row，而语句级触发器使用 for each statement; 当然each可以省略；
最后指定动作执行函数；如果这里指定为function时，函数定义也必须为function,这里建议使用function， procedure是一种历史写法。

触发器函数

触发器的内容包含一个自定义的函数，所以首先得有一个执行动作的函数。

先来定义一个函数，如果触发就打印一条信息。

CREATE FUNCTION tri_fun() RETURNS TRIGGER LANGUAGE PLPGSQL
AS $$
BEGIN-- trigger logicraise notice 'trigger excute';
END;
$$

函数说明

触发器使用的函数没有参数，在系统自动调用时会传入默认的参数；
返回值类型必须为 trigger;

函数参数

在系统调用触发器函数时，会传入默认的参数，参数类型为TriggerData, 它的定义如下：

typedef struct TriggerData
{
NodeTag type;
TriggerEvent tg_event;
Relation tg_relation;
HeapTuple tg_trigtuple;
HeapTuple tg_newtuple;
Trigger *tg_trigger;
TupleTableSlot *tg_trigslot;
TupleTableSlot *tg_newslot;
Tuplestorestate *tg_oldtable;
Tuplestorestate *tg_newtable;
const Bitmapset *tg_updatedcols;
} TriggerData;

如果使用C语言或其它编程语言时，就可以直接访问此结构，如果使用plpgsql时，默认也定义了一组系统变量。

这些是您在 PostgreSQL 的行级触发器（row-level triggers）中可以访问的特殊变量。它们提供了关于触发器的元信息和触发触发器的事件的上下文。以下是这些变量的详细解释：

NEW record
- 描述：对于 INSERT/UPDATE 操作，这个变量包含将要插入或更新的新记录。在 DELETE 操作和语句级触发器（statement-level triggers）中，这个变量是 NULL。
- 用途：可以在触发器中访问或修改将要插入或更新的记录。
OLD record
- 描述：对于 UPDATE/DELETE 操作，这个变量包含被更新或删除的旧记录。在 INSERT 操作和语句级触发器中，这个变量是 NULL。
- 用途：可以在触发器中访问被更新或删除的记录。
TG_NAME
- 描述：触发器的名称。
- 用途：可以用于日志记录或其他需要知道触发器名称的情况。
TG_WHEN
- 描述：触发器是在操作之前（BEFORE）还是之后（AFTER）触发，或者是替代（INSTEAD OF）操作。
- 用途：决定触发器应该做什么，以及何时做。
TG_LEVEL
- 描述：触发器是行级（ROW）还是语句级（STATEMENT）。
- 用途：确定如何在触发器中处理数据（逐行或整个语句）。
TG_OP
- 描述：触发触发器的事件类型：INSERT、UPDATE、DELETE 或 TRUNCATE。
- 用途：决定触发器应该执行什么逻辑。
TG_RELID
- 描述：导致触发器调用的表的 object ID。
- 用途：可以通过这个 ID 查找表的更多信息。
TG_RELNAME 和 TG_TABLE_NAME
- 描述：这两个变量都表示导致触发器调用的表的名称。但 TG_RELNAME 已被弃用，并可能在未来的版本中消失。应使用 TG_TABLE_NAME。
- 用途：用于日志记录或需要知道哪个表触发了触发器的情况。
TG_TABLE_SCHEMA
- 描述：触发触发器的表的模式（schema）。
- 用途：当您需要在多个模式中有相同表名的表之间区分时，这会很有用。
TG_NARGS 和 TG_ARGV
- 描述：TG_NARGS 表示在 CREATE TRIGGER 语句中给触发器函数提供的参数数量。TG_ARGV 是一个文本数组，其中包含这些参数。
- 用途：使触发器更加灵活，可以根据传递给它的参数执行不同的逻辑。

这些特殊变量在编写 PostgreSQL 触发器时非常有用，因为它们提供了关于触发器和触发触发器的事件的上下文信息。

删除触发器

触发器删除就比较简单了，语法如下：

DROP TRIGGER trigger_name 
ON table_name 
[ CASCADE | RESTRICT ];

语法说明

使用drop 命令，指定触发器名称与触发器对应的表即可；
如果有级联，可以指定cascade进行级联删除。

案例分析

下面来通过一个案例，看看触发器的效果。

数据准备

先来建一张表emp，然后通过触发器检查数据的一致性，并且将插入数据的日期和用户名进行记录。

CREATE TABLE emp (empname text,salary integer,last_date timestamp,last_user text
);

动作函数

使用plpgsql语言编写执行函数。

CREATE FUNCTION emp_stamp() 
RETURNS trigger AS $emp_stamp$
BEGIN-- Check that empname and salary are givenIF NEW.empname IS NULL THENRAISE EXCEPTION 'empname cannot be null';END IF;IF NEW.salary IS NULL THENRAISE EXCEPTION '% cannot have null salary',NEW.empname;END IF;-- Who works for us when they must pay for it?IF NEW.salary < 0 THENRAISE EXCEPTION '% cannot have a negative salary',NEW.empname;END IF;-- Remember who changed the payroll whenNEW.last_date := current_timestamp;NEW.last_user := current_user;RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;

这里使用了new系统参数值，它们分别代表插入前的新值，这里可以改变它的内容。

增加触发器

创建一个before触发器，在数据真正修改前做校验，并修改其中的内容，在insert和update动作进行触发。

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE FUNCTION emp_stamp();

这是一个行级触发器，每一行数据的插入和更新都会触发。

触发结果

自动赋值

这里只需要插入前两个字段就可以，后两个字段会被自动赋值。

postgres=> insert into emp(empname,salary) values('alex',4000);
INSERT 0 1
postgres=> select * from emp;empname | salary |         last_date          | last_user
---------+--------+----------------------------+-----------alex    |   4000 | 2024-06-04 08:43:54.428469 | senllang
(1 row)

可以看到后两个字段被自动赋值，用户采用当前登陆的用户名。

一致性校验

如果插入的数据不符合设计要求，在触发器中进行检查时会抛出异常；

postgres=> insert into emp(empname,salary) values('white',-1);
ERROR:  white cannot have a negative salary
CONTEXT:  PL/pgSQL function emp_stamp() line 15 at RAISE