数据库

Postgres 触发器

在表事件上自动执行 SQL。

在 Postgres 中，触发器会在表事件（例如 INSERT、UPDATE、DELETE 或 TRUNCATE 操作）上自动执行一组操作。

创建触发器#

创建触发器涉及 2 个部分

  1. 一个 函数，它将被执行（称为触发器函数）
  2. 实际的触发器对象，带有关于何时运行触发器的参数。

触发器的示例是

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

触发器函数#

触发器函数是 Postgres 在触发器触发时执行的用户定义 函数。

示例触发器函数#

这是一个示例，每当更新员工的工资时，都会更新 salary_log

1
-- Example: Update salary_log when salary is updated
2
create function update_salary_log()
3
returns trigger
4
language plpgsql
5
as $$
6
begin
7
  insert into salary_log(employee_id, old_salary, new_salary)
8
  values (new.id, old.salary, new.salary);
9
  return new;
10
end;
11
$$;
12
13
create trigger salary_update_trigger
14
after update on employees
15
for each row
16
execute 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：触发器正在触发的表的模式。
  • TG_ARGV：创建触发器时提供的字符串参数的数组。
  • TG_NARGS：TG_ARGV 数组中的参数数量。

触发器类型#

有两种类型的触发器，BEFORE 和 AFTER

在进行更改之前触发#

在触发事件之前执行。

1
create trigger before_insert_trigger
2
before insert on orders
3
for each row
4
execute function before_insert_function();

在进行更改之后触发#

在触发事件之后执行。

1
create trigger after_delete_trigger
2
after delete on customers
3
for each row
4
execute function after_delete_function();

执行频率#

有两种可用的触发器执行选项

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

删除触发器#

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

1
drop trigger "trigger_name" on "table_name";

如果您的触发器位于受限制的模式中，由于权限限制，您将无法删除它。 在这些情况下，您可以使用 CASCADE 子句删除它所依赖的函数，以自动删除所有调用它的触发器

1
drop function if exists restricted_schema.function_name() cascade;

确保在删除函数之前进行备份，以防您计划稍后重新创建它。

资源#