触发器与PL/pgSQL函数的交互：OLD、NEW、TG_OP

PostgreSQL里的触发器不仅能响应各种操作自动执行函数，还会把一些超好用的变量传进这些函数。正是靠这些变量，你能知道表里的数据操作前是什么样，操作后变成了啥，还有到底发生了什么操作。

• OLD — 包含操作前表中那一行的旧数据。用在UPDATE和DELETE触发器里，因为INSERT的时候根本没有“旧”的东西。
• NEW — 包含操作后表中那一行的新数据。用在INSERT和UPDATE触发器里。
• TG_OP — 里面是当前操作的文本信息：INSERT、UPDATE或者DELETE。

这些变量在和触发器绑定的函数里都是自动可用的。

光说理论就像SQL没加索引一样：又慢又无聊。所以我们直接上实战例子。

用OLD访问旧数据

假设我们有个表叫students。有人在里面改了学生的年龄（可能脑子里还觉得学生才20，其实已经25了）。

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

为了追踪发生了什么变化，我们先建个日志表：

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

接下来，写个函数专门记录这些变化。这里OLD就派上用场了：

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- 记录年龄变化
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然后我们来加个触发器：

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- 只有年龄真的变了才执行
EXECUTE FUNCTION log_student_changes();

现在加个学生，然后改下数据：

INSERT INTO students (name, age) VALUES ('爱丽丝', 20);

UPDATE students
SET age = 25
WHERE name = '爱丽丝';

-- 查查日志表：
SELECT * FROM student_changes;

你会看到日志表里记录了变化：年龄从20变成25。魔法？其实就是OLD。

用NEW获取新数据

现在假设我们想在添加新学生时，自动把他的ID和名字写进日志表（有点强迫症，但有时候还挺有用）：

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- 记录新学生的数据
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- 没有旧值，因为这是INSERT
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

再来加个新学生，看看效果：

INSERT INTO students (name, age) VALUES ('鲍勃', 22);

-- 查查日志：
SELECT * FROM student_changes;

你会发现日志里多了个新学生。数据安全又上了个台阶！

用TG_OP判断操作类型

那如果我们想要一个通用的日志触发器，能同时处理INSERT、UPDATE甚至DELETE咋办？这时候TG_OP就很有用了。

来写个万能函数：

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- 对于DELETE的AFTER触发器要返回NULL
END;
$$ LANGUAGE plpgsql;

再建个能响应三种操作的触发器：

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

加、改、删一个学生试试：

INSERT INTO students (name, age) VALUES ('查理', 30);
UPDATE students SET age = 31 WHERE name = '查理';
DELETE FROM students WHERE name = '查理';

-- 查查日志：
SELECT * FROM student_changes;

你能看到所有操作的日志——一个触发器，全部搞定！

用OLD、NEW、TG_OP时常见的坑

用触发器的时候，容易踩到这些常见的坑：

“为啥OLD在插入时用不了？” 这就是默认行为：INSERT没有旧数据。用NEW吧。

“如果NEW在删除时没用咋办？” 同理，这也是正常的：DELETE没有新数据。用OLD。

触发器逻辑导致死循环。 要小心别让触发器自己又触发自己。可以在WHEN块里写清楚条件，或者用TG_OP判断。