触发器语法
约 2043 字大约 7 分钟
2025-08-11
概述
触发器是数据库中的特殊存储过程，在特定的数据库事件发生时自动执行。PostgreSQL 支持多种类型的触发器来响应不同的数据操作。
基本语法
创建触发器
CREATE [ CONSTRAINT ] TRIGGER name
{ BEFORE | AFTER | INSTEAD OF }
{ event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments );删除触发器
DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ];修改触发器
ALTER TRIGGER name ON table_name RENAME TO new_name;
ALTER TRIGGER name ON table_name [ NO ] DEPENDS ON EXTENSION extension_name;触发器时机
BEFORE 触发器
在事件执行前触发，可以修改数据：
CREATE TRIGGER validate_data
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION validate_user_data();AFTER 触发器
在事件执行后触发，不能修改当前操作的数据：
CREATE TRIGGER log_user_changes
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_activity();INSTEAD OF 触发器
替代原始操作，仅用于视图：
CREATE TRIGGER update_user_view
INSTEAD OF UPDATE ON user_summary_view
FOR EACH ROW
EXECUTE FUNCTION handle_view_update();触发事件
INSERT 事件
-- 插入时触发
CREATE TRIGGER new_user_trigger
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION handle_new_user();UPDATE 事件
-- 更新时触发
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_time();
-- 特定列更新时触发
CREATE TRIGGER status_change
AFTER UPDATE OF status ON orders
FOR EACH ROW
EXECUTE FUNCTION handle_status_change();DELETE 事件
-- 删除时触发
CREATE TRIGGER cleanup_user_data
AFTER DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION cleanup_related_data();TRUNCATE 事件
-- 清空表时触发
CREATE TRIGGER prevent_truncate
BEFORE TRUNCATE ON important_table
FOR EACH STATEMENT
EXECUTE FUNCTION prevent_truncate_operation();触发器级别
FOR EACH ROW
对每个受影响的行都执行一次：
CREATE TRIGGER row_level_trigger
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION validate_product_data();FOR EACH STATEMENT
对每个SQL语句只执行一次：
CREATE TRIGGER statement_level_trigger
AFTER INSERT ON audit_log
FOR EACH STATEMENT
EXECUTE FUNCTION update_statistics();触发器函数
函数基本结构
CREATE OR REPLACE FUNCTION trigger_function()
RETURNS TRIGGER AS $$
BEGIN
-- 触发器逻辑
-- 返回值决定操作是否继续
RETURN NEW; -- 对于 INSERT/UPDATE
RETURN OLD; -- 对于 DELETE
RETURN NULL; -- 取消操作
END;
$$ LANGUAGE plpgsql;特殊变量
在触发器函数中可以使用以下特殊变量：
CREATE OR REPLACE FUNCTION example_trigger()
RETURNS TRIGGER AS $$
BEGIN
-- TG_OP: 操作类型 ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE')
IF TG_OP = 'INSERT' THEN
-- 处理插入
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
-- OLD: 更新前的行数据
-- NEW: 更新后的行数据
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
-- 处理删除
RETURN OLD;
END IF;
-- TG_WHEN: 触发时机 ('BEFORE', 'AFTER', 'INSTEAD OF')
-- TG_LEVEL: 触发级别 ('ROW', 'STATEMENT')
-- TG_TABLE_NAME: 表名
-- TG_TABLE_SCHEMA: 模式名
-- TG_RELID: 表的OID
-- TG_ARGV: 传递给触发器的参数数组
RETURN NULL;
END;
$$ LANGUAGE plpgsql;条件触发器
WHEN 条件
只有满足条件时才执行触发器：
-- 只有在价格变化时才触发
CREATE TRIGGER price_change_trigger
AFTER UPDATE ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION log_price_change();
-- 只对特定状态的记录触发
CREATE TRIGGER active_user_trigger
BEFORE UPDATE ON users
FOR EACH ROW
WHEN (NEW.status = 'active')
EXECUTE FUNCTION validate_active_user();约束触发器
延迟约束触发器
-- 创建延迟约束触发器
CREATE CONSTRAINT TRIGGER check_balance
AFTER INSERT OR UPDATE ON transactions
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION check_account_balance();
-- 在事务中控制约束检查
SET CONSTRAINTS check_balance IMMEDIATE;
SET CONSTRAINTS check_balance DEFERRED;实用示例
1. 自动更新时间戳
-- 创建更新时间戳的函数
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 为多个表创建时间戳触发器
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();2. 审计日志触发器
-- 创建审计日志表
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
operation CHAR(1) NOT NULL, -- 'I', 'U', 'D'
old_values JSONB,
new_values JSONB,
user_id UUID,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 审计触发器函数
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, operation, old_values, user_id)
VALUES (TG_TABLE_NAME, 'D', to_jsonb(OLD), current_setting('app.user_id', true)::UUID);
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation, old_values, new_values, user_id)
VALUES (TG_TABLE_NAME, 'U', to_jsonb(OLD), to_jsonb(NEW), current_setting('app.user_id', true)::UUID);
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, new_values, user_id)
VALUES (TG_TABLE_NAME, 'I', to_jsonb(NEW), current_setting('app.user_id', true)::UUID);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- 为表创建审计触发器
CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_trigger();3. 数据验证触发器
-- 数据验证函数
CREATE OR REPLACE FUNCTION validate_email()
RETURNS TRIGGER AS $$
BEGIN
-- 验证邮箱格式
IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
RAISE EXCEPTION 'Invalid email format: %', NEW.email;
END IF;
-- 检查邮箱唯一性
IF EXISTS (SELECT 1 FROM users WHERE email = NEW.email AND id != COALESCE(NEW.id, '00000000-0000-0000-0000-000000000000')) THEN
RAISE EXCEPTION 'Email already exists: %', NEW.email;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 创建验证触发器
CREATE TRIGGER validate_user_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION validate_email();4. 缓存维护触发器
-- 缓存更新函数
CREATE OR REPLACE FUNCTION invalidate_user_cache()
RETURNS TRIGGER AS $$
BEGIN
-- 使用 NOTIFY 通知应用层缓存失效
IF TG_OP = 'DELETE' THEN
PERFORM pg_notify('user_cache_invalidate', OLD.id::text);
RETURN OLD;
ELSE
PERFORM pg_notify('user_cache_invalidate', NEW.id::text);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
-- 创建缓存维护触发器
CREATE TRIGGER user_cache_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION invalidate_user_cache();5. 级联更新触发器
-- 级联更新函数
CREATE OR REPLACE FUNCTION cascade_user_updates()
RETURNS TRIGGER AS $$
BEGIN
-- 当用户邮箱变化时，更新相关表
IF OLD.email IS DISTINCT FROM NEW.email THEN
UPDATE orders SET customer_email = NEW.email
WHERE customer_id = NEW.id;
UPDATE notifications SET recipient_email = NEW.email
WHERE user_id = NEW.id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 创建级联更新触发器
CREATE TRIGGER user_cascade_updates
AFTER UPDATE ON users
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email)
EXECUTE FUNCTION cascade_user_updates();触发器管理
查看触发器信息
-- 查看所有触发器
SELECT
t.tgname AS trigger_name,
t.tgrelid::regclass AS table_name,
p.proname AS function_name,
CASE t.tgtype & 66
WHEN 2 THEN 'BEFORE'
WHEN 64 THEN 'INSTEAD OF'
ELSE 'AFTER'
END AS trigger_timing,
CASE t.tgtype & 28
WHEN 4 THEN 'INSERT'
WHEN 8 THEN 'DELETE'
WHEN 16 THEN 'UPDATE'
WHEN 12 THEN 'INSERT, DELETE'
WHEN 20 THEN 'INSERT, UPDATE'
WHEN 24 THEN 'DELETE, UPDATE'
WHEN 28 THEN 'INSERT, DELETE, UPDATE'
END AS trigger_events
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE NOT t.tgisinternal
ORDER BY t.tgrelid::regclass, t.tgname;启用/禁用触发器
-- 禁用特定触发器
ALTER TABLE users DISABLE TRIGGER user_audit;
-- 启用特定触发器
ALTER TABLE users ENABLE TRIGGER user_audit;
-- 禁用表的所有触发器
ALTER TABLE users DISABLE TRIGGER ALL;
-- 启用表的所有触发器
ALTER TABLE users ENABLE TRIGGER ALL;
-- 只禁用用户触发器（保留系统触发器）
ALTER TABLE users DISABLE TRIGGER USER;
-- 启用用户触发器
ALTER TABLE users ENABLE TRIGGER USER;触发器执行顺序
当同一事件有多个触发器时，执行顺序为：
- BEFORE 触发器按字母顺序执行
- AFTER 触发器按字母顺序执行
- CONSTRAINT 触发器在所有其他触发器之后执行
-- 控制触发器执行顺序的命名策略
CREATE TRIGGER a_first_trigger BEFORE UPDATE ON users ...;
CREATE TRIGGER b_second_trigger BEFORE UPDATE ON users ...;
CREATE TRIGGER z_last_trigger BEFORE UPDATE ON users ...;性能考虑
1. 避免复杂逻辑
-- 好的做法：简单快速的触发器
CREATE OR REPLACE FUNCTION simple_trigger()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 避免：复杂的业务逻辑
CREATE OR REPLACE FUNCTION complex_trigger()
RETURNS TRIGGER AS $$
BEGIN
-- 避免复杂查询、外部API调用等
-- 这些操作应该移到应用层
RETURN NEW;
END;
$$ LANGUAGE plpgsql;2. 使用条件触发器
-- 只在必要时触发
CREATE TRIGGER conditional_trigger
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION handle_status_change();3. 批量操作优化
-- 对于批量操作，使用语句级触发器
CREATE TRIGGER batch_update_trigger
AFTER UPDATE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION update_product_statistics();调试和测试
触发器调试
-- 在触发器中添加日志
CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER AS $$
BEGIN
-- 记录调试信息
RAISE NOTICE 'Trigger executed: % on % at %', TG_OP, TG_TABLE_NAME, now();
-- 记录变量值
RAISE NOTICE 'OLD: %, NEW: %', OLD, NEW;
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;触发器测试
-- 创建测试函数
CREATE OR REPLACE FUNCTION test_triggers()
RETURNS void AS $$
DECLARE
test_user users%ROWTYPE;
BEGIN
-- 测试 INSERT 触发器
INSERT INTO users (email, full_name)
VALUES ('test@example.com', 'Test User')
RETURNING * INTO test_user;
-- 验证触发器效果
ASSERT test_user.created_at IS NOT NULL, 'created_at should be set';
-- 测试 UPDATE 触发器
UPDATE users SET full_name = 'Updated Name'
WHERE id = test_user.id;
-- 清理测试数据
DELETE FROM users WHERE id = test_user.id;
RAISE NOTICE 'All trigger tests passed';
END;
$$ LANGUAGE plpgsql;最佳实践
- 保持简单: 触发器应该快速执行，避免复杂逻辑
- 错误处理: 包含适当的异常处理
- 事务安全: 考虑触发器在事务中的行为
- 性能监控: 定期检查触发器对性能的影响
- 文档记录: 详细记录触发器的用途和行为
- 测试覆盖: 确保触发器逻辑有完整的测试用例
版权所有
版权归属：Evoliant
许可证：MIT