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

码农世界 2024-06-06 后端 94 次浏览 0个评论

使用触发器

专栏内容：

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 logic
   raise notice 'trigger excute';
END;
$$
```
      1. 函数说明
      - 触发器使用的函数没有参数，在系统自动调用时会传入默认的参数；
      - 返回值类型必须为 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 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掉。
        
        总结
        
        本文主要分享了触发器的创建，删除，以及触发器函数定义和系统变量，触发器中的系统变量可以帮助我们获取触发器当前的环境信息。
        
        适当的使用触发器可以将应用变得简单，现在也流行减少函数和存储过程，为了更好的移植性和维护性。
        
        结尾
        
        非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！
        
        作者邮箱：study@senllang.onaliyun.com
        如有错误或者疏漏欢迎指出，互相学习。
        
        注：未经同意，不得转载！