Skip to content

触发

Automate 操作 on 行更改使用 BEFORE, AFTER, and INSTEAD OF 触发.

你将学到

  • 什么触发是 and 如何它 fires automatically
  • BEFORE 对比 AFTER 触发 and 当到 use 每个
  • 触发事件: INSERT, UPDATE, DELETE
  • Using 触发 for auditing and derived 列
  • 为什么触发能 是双-edged sword

概念讲解

什么是触发器?

trigger 是一种 named procedure that the database executes automatically when a specific event (INSERT, UPDATE, or DELETE) occurs on a table. Unlike stored procedures that you call explicitly, triggers fire on their own — you do not invoke them in application code. They are attached to a table and an event.

BEFORE vs AFTER

  • BEFORE triggers run before the row is modified. They can validate data, modify the NEW row (e.g. set a default or computed column), or cancel the operation by raising an error. Use BEFORE for data transformation and validation.
  • AFTER triggers run after the row has been modified. They cannot change the row, but they can update other tables, write audit logs, or fire notifications. Use AFTER for side effects like auditing.

Trigger Events

A trigger can fire on one or more events: INSERT, UPDATE, or DELETE. For UPDATE, you can further restrict it to fire only when specific columns change: UPDATE OF price, stock. Inside the trigger, you can access the old and new row values via OLD and NEW (PostgreSQL/MySQL) or :OLD and :NEW (Oracle).

Auditing with Triggers

A common use case is an audit log: every time a row changes, insert a record into an audit table with the old values, the new values, who changed it, and when. This is implemented with an AFTER trigger on UPDATE and DELETE. It provides a full history of changes without changing application code.

The Double-Edged Sword

Triggers are powerful but dangerous. They run invisibly — application developers may not realize that an INSERT also updates three other tables. This makes debugging difficult, can cause performance issues (cascading triggers), and can lead to surprising behavior during bulk operations. Use triggers sparingly, document them well, and prefer explicit application logic when possible.

示例代码

              -- PostgreSQL: BEFORE trigger to set updated_at automatically
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- PostgreSQL: AFTER trigger for audit logging
CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.price IS DISTINCT FROM OLD.price THEN
    INSERT INTO price_audit (product_id, old_price, new_price, changed_at)
    VALUES (OLD.id, OLD.price, NEW.price, NOW());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_price
  AFTER UPDATE OF price ON products
  FOR EACH ROW
  EXECUTE FUNCTION log_price_change();
            

Two 触发: BEFORE UPDATE 触发那个 auto-设置 updated_at, and AFTER UPDATE 触发那个 logs price 更改到 audit 表仅 当 price actually 更改.

动手试一试

  • SQL Formatter

    格式触发函数 and CREATE TRIGGER 语句.

  • Code Diff

    Compare 触发定义当 修改 audit logic.

常见错误

错误写法

创建 cascading 触发那个 fire 每个 other in 循环

正确做法

触发 on 表那个更新表 B, 哪个有 触发那个更新表 , 能循环 infinitely. 设计触发 carefully and use 条件到 break cycles.

错误写法

Using 触发 for business logic 那个应该是 in application code

正确做法

触发是 不可见到 application developers and 困难到 测试. Use 它们 for 数据-level concerns (timestamps, audit logs) and keep business 规则 in application.

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误