使用触发器

概述

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

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

创建触发器

触发器的创建语法如下：

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER} { INSERT OR UPDATE OR DELETE OR TRUNCATE }
   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 logic
   raise notice 'trigger excute';
END;
$$

1.函数说明

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

1.函数参数

在系统调用触发器函数时，会传入默认的参数，参数类型为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）中可以访问的特殊变量。它们提供了关于触发器的元信息和触发触发器的事件的上下文。以下是这些变量的详细解释：

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 触发器时非常有用，因为它们提供了关于触发器和触发触发器的事件的上下文信息。

删除触发器

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

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 given
    IF NEW.empname IS NULL THEN
        RAISE EXCEPTION 'empname cannot be null';
    END IF;


    IF NEW.salary IS NULL THEN
        RAISE EXCEPTION '% cannot have null salary',
        NEW.empname;
    END IF;


    -- Who works for us when they must pay for it?
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '% cannot have a negative salary',
        NEW.empname;
    END IF;


    -- 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
动作进行触发。

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

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

总结

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

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