当记录被修改时自动更新last_modified字段的触发器

想象一下，你在开发一个管理学生和课程的应用，你有个students表。这个表里有个last_modified字段，每次只要有数据被改（比如学生名字或者年龄被改），这个字段就得自动更新。

与其每次在SQL语句里手动写last_modified的更新，不如直接搞个trigger帮你自动搞定。

students表结构

先来建个students表，后面例子都用它。这个表存学生的基本信息：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- 学生唯一标识
    name VARCHAR(100) NOT NULL,   -- 学生名字
    age INT,                      -- 学生年龄
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- 最后修改时间
);

• last_modified字段在插入新记录时会自动填当前时间（NOW()）。
• 这个字段会在学生数据被改动时自动刷新。

往表里插点测试数据：

INSERT INTO students (name, age)
VALUES 
    ('奥托 林', 20),
    ('玛丽亚 奇', 22),
    ('亚历克斯 松', 19);

现在表里的数据大概长这样：

写个自动更新last_modified的function

PL/pgSQL里的function会被trigger调用，帮你自动更新last_modified字段。每次数据要改之前，这个function会自动跑一遍。

来写个update_last_modified函数：

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- 把last_modified字段更新成当前时间
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEW是个特殊变量，存着改完之后的新数据。
• 我们把NEW.last_modified设成NOW()（当前时间）。
• function要返回更新后的NEW，trigger才能正常用。

创建trigger

现在来建个trigger，每次students表有记录被update时自动调用update_last_modified函数。

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

这里发生了啥：

• BEFORE UPDATE表示trigger会在UPDATE操作执行前触发。
• FOR EACH ROW说明每条被改的记录都会触发一次。
• EXECUTE FUNCTION update_last_modified()就是调用我们刚写的function。

测试trigger

来看看trigger到底好不好使。先查下students表里的数据：

SELECT * FROM students;

结果：

现在把student_id = 1的学生年龄改一下：

UPDATE students
SET age = 21
WHERE student_id = 1;

再查一次表：

SELECT * FROM students;

预期结果：

注意看：student_id = 1的last_modified变成了当前时间，其他记录没变。

扩展trigger逻辑

假如你现在想让last_modified字段只在某些特定字段被改时才更新。比如只有学生名字或年龄变了才触发，其他字段变了就不管。

这时候可以在trigger定义里加个WHEN条件。

来写个带条件的新trigger：

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

这里：

• WHEN条件会检查name和age这两个字段，旧值（OLD）和新值（NEW）是不是不一样。
• 如果这俩字段都没变，trigger就不会触发。

再试着改下表里的数据，看看新逻辑是不是生效了。

用trigger的建议

1. 别滥用trigger。 虽然trigger很方便，但会让数据库逻辑变复杂，调试起来也麻烦。
2. 一定要写清楚trigger是干嘛用的，啥场景下会触发。
3. 用WHEN条件，避免trigger被无意义地触发。
4. 记住，trigger可能会影响数据库性能，尤其是表很大的时候。

用trigger时常见的坑

数据没改对。 比如你忘了给NEW赋值，结果返回的还是原来的数据，啥都没变。

条件写错。 比如你忘了加WHEN，trigger每次都触发，哪怕其实啥都没改。

递归问题。 如果trigger调用的function又触发了trigger，可能会死循环。PostgreSQL有递归保护，但最好别这么搞。

这个例子说明了trigger怎么帮你自动更新数据。在实际项目里，这种技巧经常用来做变更日志、保证数据一致性、自动化一些重复操作。