PostgreSQL 触发器 #
触发器概述 #
触发器是在特定事件发生时自动执行的函数。
text
┌─────────────────────────────────────────────────────────────┐
│ 触发器类型 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 触发时机： │
│ ├── BEFORE - 事件发生前执行 │
│ ├── AFTER - 事件发生后执行 │
│ └── INSTEAD OF - 替代事件执行（用于视图） │
│ │
│ 触发事件： │
│ ├── INSERT │
│ ├── UPDATE │
│ ├── DELETE │
│ └── TRUNCATE │
│ │
│ 触发范围： │
│ ├── FOR EACH ROW - 每行触发 │
│ └── FOR EACH STATEMENT - 每条语句触发 │
│ │
└─────────────────────────────────────────────────────────────┘
创建触发器 #
基本触发器 #
sql
-- 创建示例表
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 创建触发器
CREATE TRIGGER trg_employees_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
-- 测试
INSERT INTO employees (name, salary) VALUES ('Alice', 80000);
SELECT * FROM employees;
-- updated_at: 2026-03-29 10:00:00
UPDATE employees SET salary = 85000 WHERE name = 'Alice';
SELECT * FROM employees;
-- updated_at: 2026-03-29 10:01:00 (自动更新)
BEFORE 触发器 #
sql
-- 数据验证触发器
CREATE OR REPLACE FUNCTION validate_salary()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.salary < 0 THEN
RAISE EXCEPTION 'Salary cannot be negative';
END IF;
IF NEW.salary > 1000000 THEN
RAISE EXCEPTION 'Salary exceeds maximum limit';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_employees_validate_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_salary();
-- 测试
INSERT INTO employees (name, salary) VALUES ('Bob', -1000);
-- ERROR: Salary cannot be negative
AFTER 触发器 #
sql
-- 创建审计日志表
CREATE TABLE employee_audit (
id SERIAL PRIMARY KEY,
employee_id INTEGER,
action VARCHAR(10),
old_data JSONB,
new_data JSONB,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
changed_by VARCHAR(100) DEFAULT CURRENT_USER
);
-- 创建审计触发器
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO employee_audit (employee_id, action, new_data)
VALUES (NEW.id, 'INSERT', to_jsonb(NEW));
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO employee_audit (employee_id, action, old_data, new_data)
VALUES (NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO employee_audit (employee_id, action, old_data)
VALUES (OLD.id, 'DELETE', to_jsonb(OLD));
END IF;
RETURN NULL; -- AFTER 触发器返回值被忽略
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_employee_changes();
-- 测试
INSERT INTO employees (name, salary) VALUES ('Charlie', 70000);
UPDATE employees SET salary = 75000 WHERE name = 'Charlie';
DELETE FROM employees WHERE name = 'Charlie';
SELECT * FROM employee_audit;
INSTEAD OF 触发器 #
sql
-- 创建视图
CREATE VIEW employee_view AS
SELECT
id,
name,
salary,
salary * 12 AS annual_salary
FROM employees;
-- 创建 INSTEAD OF 触发器
CREATE OR REPLACE FUNCTION update_employee_view()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO employees (name, salary)
VALUES (NEW.name, NEW.salary);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE employees SET
name = NEW.name,
salary = NEW.salary
WHERE id = OLD.id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
DELETE FROM employees WHERE id = OLD.id;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_employee_view
INSTEAD OF INSERT OR UPDATE OR DELETE ON employee_view
FOR EACH ROW
EXECUTE FUNCTION update_employee_view();
-- 现在可以通过视图更新
INSERT INTO employee_view (name, salary) VALUES ('Diana', 60000);
触发器变量 #
text
┌─────────────────┬─────────────────────────────────────────────┐
│ 变量 │ 说明 │
├─────────────────┼─────────────────────────────────────────────┤
│ NEW │ 新行的数据（INSERT/UPDATE） │
│ OLD │ 旧行的数据（UPDATE/DELETE） │
│ TG_NAME │ 触发器名称 │
│ TG_WHEN │ 触发时机（BEFORE/AFTER/INSTEAD OF） │
│ TG_OP │ 触发操作（INSERT/UPDATE/DELETE/TRUNCATE） │
│ TG_TABLE_NAME │ 表名 │
│ TG_TABLE_SCHEMA │ 模式名 │
│ TG_ARGV │ 触发器参数数组 │
│ TG_NARGS │ 参数数量 │
└─────────────────┴─────────────────────────────────────────────┘
使用触发器变量 #
sql
CREATE OR REPLACE FUNCTION log_trigger_info()
RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'Trigger: %, Operation: %, Table: %',
TG_NAME, TG_OP, TG_TABLE_NAME;
IF TG_OP = 'UPDATE' THEN
RAISE NOTICE 'Old salary: %, New salary: %', OLD.salary, NEW.salary;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
使用触发器参数 #
sql
CREATE OR REPLACE FUNCTION check_value_with_param()
RETURNS TRIGGER AS $$
DECLARE
min_value DECIMAL;
BEGIN
min_value := TG_ARGV[0]::DECIMAL;
IF NEW.salary < min_value THEN
RAISE EXCEPTION 'Salary must be at least %', min_value;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 创建带参数的触发器
CREATE TRIGGER trg_employees_min_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_value_with_param(10000);
语句级触发器 #
sql
-- 语句级触发器（每条 SQL 语句触发一次）
CREATE OR REPLACE FUNCTION log_statement()
RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'Statement % executed on %', TG_OP, TG_TABLE_NAME;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_employees_statement
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION log_statement();
-- 执行批量操作
INSERT INTO employees (name, salary) VALUES
('User1', 50000),
('User2', 60000),
('User3', 70000);
-- 只触发一次
条件触发器 #
sql
-- 带条件的触发器
CREATE TRIGGER trg_employees_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION audit_employee_changes();
-- 只在薪资变化时触发
常见应用场景 #
自动生成计算列 #
sql
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
quantity INTEGER,
unit_price DECIMAL(10, 2),
total_price DECIMAL(10, 2)
);
CREATE OR REPLACE FUNCTION calculate_total()
RETURNS TRIGGER AS $$
BEGIN
NEW.total_price := NEW.quantity * NEW.unit_price;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_orders_calculate_total
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION calculate_total();
INSERT INTO orders (quantity, unit_price) VALUES (10, 25.00);
SELECT * FROM orders; -- total_price 自动计算为 250.00
维护派生数据 #
sql
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
employee_count INTEGER DEFAULT 0,
total_salary DECIMAL(15, 2) DEFAULT 0
);
CREATE OR REPLACE FUNCTION update_department_stats()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE departments SET
employee_count = employee_count + 1,
total_salary = total_salary + NEW.salary
WHERE id = NEW.department_id;
ELSIF TG_OP = 'UPDATE' THEN
IF OLD.department_id = NEW.department_id THEN
UPDATE departments SET
total_salary = total_salary - OLD.salary + NEW.salary
WHERE id = NEW.department_id;
ELSE
UPDATE departments SET
employee_count = employee_count - 1,
total_salary = total_salary - OLD.salary
WHERE id = OLD.department_id;
UPDATE departments SET
employee_count = employee_count + 1,
total_salary = total_salary + NEW.salary
WHERE id = NEW.department_id;
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE departments SET
employee_count = employee_count - 1,
total_salary = total_salary - OLD.salary
WHERE id = OLD.department_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
软删除实现 #
sql
ALTER TABLE employees ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE employees ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
CREATE OR REPLACE FUNCTION soft_delete()
RETURNS TRIGGER AS $$
BEGIN
NEW.deleted_at := CURRENT_TIMESTAMP;
NEW.is_deleted := TRUE;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_employees_soft_delete
BEFORE UPDATE OF is_deleted ON employees
FOR EACH ROW
WHEN (NEW.is_deleted = TRUE AND OLD.is_deleted = FALSE)
EXECUTE FUNCTION soft_delete();
数据同步 #
sql
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER,
total DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_history (
id SERIAL PRIMARY KEY,
order_id INTEGER,
status VARCHAR(20),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE OR REPLACE FUNCTION sync_order_history()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO order_history (order_id, status)
VALUES (NEW.id, NEW.status);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_orders_history
AFTER INSERT OR UPDATE OF status ON orders
FOR EACH ROW
EXECUTE FUNCTION sync_order_history();
管理触发器 #
查看触发器 #
sql
-- 查看表的所有触发器
SELECT
tgname AS trigger_name,
proname AS function_name,
tgenabled AS enabled
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE tgrelid = 'employees'::regclass;
-- 使用 psql 命令
\dS+ employees
禁用和启用触发器 #
sql
-- 禁用特定触发器
ALTER TABLE employees DISABLE TRIGGER trg_employees_audit;
-- 禁用所有触发器
ALTER TABLE employees DISABLE TRIGGER ALL;
-- 启用触发器
ALTER TABLE employees ENABLE TRIGGER trg_employees_audit;
ALTER TABLE employees ENABLE TRIGGER ALL;
-- 启用副本触发器
ALTER TABLE employees ENABLE REPLICA TRIGGER trg_employees_audit;
删除触发器 #
sql
-- 删除触发器
DROP TRIGGER trg_employees_audit ON employees;
-- 如果存在则删除
DROP TRIGGER IF EXISTS trg_employees_audit ON employees;
学习路径 #
text
高级阶段
├── 索引
├── 视图
├── 存储过程
├── 触发器（本文）
└── 事务与锁
下一步 #
掌握了触发器后，接下来学习 事务与锁，了解并发控制机制！
最后更新：2026-03-29