数据库

Postgres 触发器

在表事件上自动执行SQL。

在PostgreSQL中，触发器(trigger)会在表事件（如INSERT、UPDATE、DELETE或TRUNCATE操作）发生时自动执行一系列操作。

创建触发器

创建触发器包含两个部分：

  1. 一个将被执行的函数（称为触发器函数）
  2. 实际的触发器对象，包含触发器何时运行的参数

以下是一个触发器示例：

1
2
3
4
create trigger "trigger_name"after insert on "table_name"for each rowexecute function trigger_function();

触发器函数

触发器函数是用户定义的函数，当触发器被触发时由PostgreSQL执行。

触发器函数示例

这里有一个示例，当员工薪资更新时会更新salary_log表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 示例：当薪资更新时更新salary_logcreate function update_salary_log()returns triggerlanguage plpgsqlas $$begin  insert into salary_log(employee_id, old_salary, new_salary)  values (new.id, old.salary, new.salary);  return new;end;$$;create trigger salary_update_triggerafter update on employeesfor each rowexecute function update_salary_log();

触发器变量

触发器函数可以访问多个特殊变量，这些变量提供了有关触发器事件上下文和被修改数据的信息。在上面的示例中，您可以看到插入到薪资日志中的值是 old.salary 和 new.salary —— 这里 old 表示修改前的值，new 表示更新后的值。

以下是触发器函数中可用的关键变量和选项：

  • TG_NAME: 当前触发器的名称。
  • TG_WHEN: 触发器事件的时机 (BEFORE 或 AFTER)。
  • TG_OP: 触发事件的操作类型 (INSERT、UPDATE、DELETE 或 TRUNCATE)。
  • OLD: 记录变量，在 UPDATE 和 DELETE 触发器中保存旧行数据。
  • NEW: 记录变量，在 UPDATE 和 INSERT 触发器中保存新行数据。
  • TG_LEVEL: 触发器级别 (ROW 或 STATEMENT)，指示是行级还是语句级触发器。
  • TG_RELID: 触发器所在表的对象ID。
  • TG_TABLE_NAME: 触发器所在表的名称。
  • TG_TABLE_SCHEMA: 触发器所在表的模式(schema)。
  • TG_ARGV: 创建触发器时提供的字符串参数数组。
  • TG_NARGS: TG_ARGV 数组中的参数数量。

触发器类型

触发器有两种类型：BEFORE 和 AFTER:

在变更前触发

在触发事件执行前运行。

1
2
3
4
create trigger before_insert_triggerbefore insert on ordersfor each rowexecute function before_insert_function();

在变更后触发

在触发事件执行后运行。

1
2
3
4
create trigger after_delete_triggerafter delete on customersfor each rowexecute function after_delete_function();

执行频率

触发器有两种执行方式可选：

  • for each row：指定触发器函数应对每个受影响的行执行一次
  • for each statement：触发器对整个操作执行一次（例如插入操作执行一次）。当单个SQL语句影响多行时，这种方式比for each row更高效，因为它允许您一次性对多行数据进行计算或更新

删除触发器

可以使用drop trigger命令删除触发器：

1
drop trigger "trigger_name" on "table_name";

相关资源