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

使用触发器

​专栏内容：

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

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

文章目录

概述

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

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

创建触发器

触发器的创建语法如下：

CREATETRIGGER 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是一种历史写法。

触发器函数

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

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

CREATEFUNCTION tri_fun()RETURNSTRIGGERLANGUAGE PLPGSQL
AS $$
BEGIN-- trigger logic
   raise notice 'trigger excute';END;
$$

1. 函数说明

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

1. 函数参数

在系统调用触发器函数时，会传入默认的参数，参数类型为

TriggerData

, 它的定义如下：

typedefstructTriggerData{
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）中可以访问的特殊变量。它们提供了关于触发器的元信息和触发触发器的事件的上下文。以下是这些变量的详细解释：

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

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

删除触发器

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

DROPTRIGGER trigger_name 
ON table_name 
[CASCADE|RESTRICT];

语法说明

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

案例分析

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

数据准备

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

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

动作函数

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

CREATEFUNCTION emp_stamp()RETURNStriggerAS $emp_stamp$
BEGIN-- Check that empname and salary are givenIF NEW.empname ISNULLTHEN
        RAISE EXCEPTION 'empname cannot be null';ENDIF;IF NEW.salary ISNULLTHEN
        RAISE EXCEPTION '% cannot have null salary',
        NEW.empname;ENDIF;-- Who works for us when they must pay for it?IF NEW.salary <0THEN
        RAISE EXCEPTION '% cannot have a negative salary',
        NEW.empname;ENDIF;-- Remember who changed the payroll when
    NEW.last_date :=current_timestamp;
    NEW.last_user :=current_user;RETURN NEW;END;
$emp_stamp$ LANGUAGE plpgsql;

这里使用了

new

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

增加触发器

创建一个

before

触发器，在数据真正修改前做校验，并修改其中的内容，在

insert

和

update

动作进行触发。

CREATETRIGGER emp_stamp BEFORE INSERTORUPDATEON emp
FOR EACH ROWEXECUTEFUNCTION emp_stamp();

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

触发结果

自动赋值

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

postgres=> insert into emp(empname,salary) values('alex',4000);
INSERT 01postgres=>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

触发器中抛出异常后，事务也就会被abort掉。

总结

本文主要分享了触发器的创建，删除，以及触发器函数定义和系统变量，触发器中的系统变量可以帮助我们获取触发器当前的环境信息。

适当的使用触发器可以将应用变得简单，现在也流行减少函数和存储过程，为了更好的移植性和维护性。

结尾

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！