PostgreSQL - BEFORE 触发器比 AFTER 触发器更有效吗

在 PostgreSQL 数据库中，触发器是一种强大的机制，用于在表上执行自定义的操作。在触发器中，BEFORE 和 AFTER 是两种常见的执行时间选项。BEFORE 触发器在执行被触发的操作之前执行，而 AFTER 触发器在执行被触发的操作之后执行。然而，要确定哪种触发器更有效，需要考虑多个因素。

sql
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

sql
CREATE OR REPLACE FUNCTION update_student_count()
    RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE student_count SET count = count + 1;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE student_count SET count = count + 1;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_student_count_trigger
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_student_count();

sql
CREATE OR REPLACE FUNCTION log_student_operation()
    RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        RAISE NOTICE 'Inserted student: %', NEW.name;
    ELSIF TG_OP = 'UPDATE' THEN
        RAISE NOTICE 'Updated student: %', NEW.name;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER log_student_operation_trigger
AFTER INSERT OR UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_operation();

sql
INSERT INTO students (name, age) VALUES ('John', 20);
-- 学生总数将自动增加，并且将显示一条日志：Inserted student: John
UPDATE students SET age = 21 WHERE name = 'John';
-- 学生总数将再次自动增加，并且将显示一条日志：Updated student: John