## 1. 创建触发函数

### 1.1 创建简单触发函数

“`sql
CREATE OR REPLACE FUNCTION fgedu_log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ‘INSERT’ THEN
INSERT INTO fgedu_student_log (action, student_id, name, age, major, change_time)
VALUES (‘INSERT’, NEW.id, NEW.name, NEW.age, NEW.major, CURRENT_TIMESTAMP);
ELSIF TG_OP = ‘UPDATE’ THEN
INSERT INTO fgedu_student_log (action, student_id, name, age, major, change_time)
VALUES (‘UPDATE’, NEW.id, NEW.name, NEW.age, NEW.major, CURRENT_TIMESTAMP);
ELSIF TG_OP = ‘DELETE’ THEN
INSERT INTO fgedu_student_log (action, student_id, name, age, major, change_time)
VALUES (‘DELETE’, OLD.id, OLD.name, OLD.age, OLD.major, CURRENT_TIMESTAMP);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
“`

### 1.2 创建带条件的触发函数

“`sql
CREATE OR REPLACE FUNCTION fgedu_check_student_age()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.age < 18 THEN RAISE EXCEPTION 'Student age must be at least 18'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; ``` ## 2. 创建触发器 ### 2.1 创建BEFORE触发器 ```sql CREATE TRIGGER fgedu_check_student_age_trigger BEFORE INSERT OR UPDATE ON fgedu_student FOR EACH ROW EXECUTE FUNCTION fgedu_check_student_age(); ``` ### 2.2 创建AFTER触发器 ```sql CREATE TRIGGER fgedu_log_student_changes_trigger AFTER INSERT OR UPDATE OR DELETE ON fgedu_student FOR EACH ROW EXECUTE FUNCTION fgedu_log_student_changes(); ``` ### 2.3 创建INSTEAD OF触发器 ```sql CREATE TRIGGER fgedu_instead_of_insert_trigger INSTEAD OF INSERT ON fgedu_student_view FOR EACH ROW EXECUTE FUNCTION fgedu_instead_of_insert(); ``` ### 2.4 创建语句级触发器 ```sql CREATE TRIGGER fgedu_log_student_statement_trigger AFTER INSERT OR UPDATE OR DELETE ON fgedu_student FOR EACH STATEMENT EXECUTE FUNCTION fgedu_log_student_statement(); ``` ## 3. 创建触发器表 ```sql CREATE TABLE fgedu_student_log ( id SERIAL PRIMARY KEY, action VARCHAR(10) NOT NULL, student_id INTEGER NOT NULL, name VARCHAR(50) NOT NULL, age INTEGER NOT NULL, major VARCHAR(50) NOT NULL, change_time TIMESTAMP NOT NULL ); ```

## 1. 查看触发器

### 1.1 查看所有触发器

“`sql
\dtriggers
“`

### 1.2 查看特定表的触发器

“`sql
\d+ fgedu_student
“`

### 1.3 查看触发器详细信息

“`sql
SELECT * FROM pg_trigger WHERE tgrelid = ‘fgedu_student’::regclass;
“`

## 2. 修改触发器

### 2.1 禁用触发器

“`sql
ALTER TABLE fgedu_student DISABLE TRIGGER fgedu_check_student_age_trigger;
“`

### 2.2 启用触发器

“`sql
ALTER TABLE fgedu_student ENABLE TRIGGER fgedu_check_student_age_trigger;
“`

### 2.3 禁用所有触发器

“`sql
ALTER TABLE fgedu_student DISABLE TRIGGER ALL;
“`

### 2.4 启用所有触发器

“`sql
ALTER TABLE fgedu_student ENABLE TRIGGER ALL;
“`

## 3. 删除触发器

### 3.1 删除特定触发器

“`sql
DROP TRIGGER IF EXISTS fgedu_check_student_age_trigger ON fgedu_student;
“`

### 3.2 删除触发函数

“`sql
DROP FUNCTION IF EXISTS fgedu_check_student_age();
“`

## 1. 与应用程序集成

### 1.1 应用程序中的触发器使用
– 应用程序不需要直接调用触发器
– 触发器在数据库操作时自动执行
– 应用程序需要处理触发器可能抛出的异常

### 1.2 触发器与业务逻辑
– 触发器适合处理数据库级别的业务逻辑
– 应用程序适合处理应用级别的业务逻辑
– 合理划分触发器和应用程序的职责

## 2. 与其他数据库对象集成

### 2.1 触发器与约束
– 触发器可以实现复杂的约束逻辑
– 简单的约束应使用数据库内置约束
– 触发器可以补充约束的不足

### 2.2 触发器与存储过程
– 触发器可以调用存储过程
– 复杂的业务逻辑可以封装在存储过程中
– 触发器负责触发时机的控制

### 2.3 触发器与视图
– INSTEAD OF触发器可以使视图可更新
– 触发器可以实现视图的复杂更新逻辑
– 视图可以简化数据访问

## 3. 触发器的事务处理

### 3.1 触发器与事务
– 触发器在触发事件的事务中执行
– 触发器中的错误会导致整个事务回滚
– 触发器可以使用事务控制语句

### 3.2 事务隔离级别
– 触发器在触发事件的事务隔离级别下执行
– 不同的隔离级别会影响触发器的行为
– 应根据业务需求选择合适的隔离级别

import psycopg2
from psycopg2 import pool

# 数据库连接参数
DB_PARAMS = {
“fgedu.net.cn”: “localfgedu.net.cn”,
“port”: 5432,
“fgedudb”: “fgedudb”,
“fgedu”: “fgedu”,
“password”: “fgedu_password”
}

# 创建连接池
postgres_pool = None

def init_pool():
global postgres_pool
postgres_pool = pool.ThreadedConnectionPool(
minconn=5,
maxconn=20,
**DB_PARAMS
)
print(“连接池初始化成功”)

def get_connection():
“””从连接池获取连接”””
return postgres_pool.getconn()

def release_connection(conn):
“””将连接归还到连接池”””
postgres_pool.putconn(conn)

def execute_query(query, params=None):
“””执行SQL查询”””
conn = None
try:
conn = get_connection()
with conn.cursor() as cur:
cur.execute(query, params)
if query.strip().upper().startswith(“SELECT”):
result = cur.fetchall()
conn.commit()
return result
else:
conn.commit()
return cur.rowcount
except Exception as e:
if conn:
conn.rollback()
print(f”执行查询失败: {e}”)
raise
finally:
if conn:
release_connection(conn)

def create_trigger_infrastructure():
“””创建触发器基础设施”””
# 创建日志表
create_log_table = “””
CREATE TABLE fgedu_IF NOT EXISTS fgedu_student_log (
id SERIAL PRIMARY KEY,
action VARCHAR(10) NOT NULL,
student_id INTEGER NOT NULL,
name VARCHAR(50) NOT NULL,
age INTEGER NOT NULL,
major VARCHAR(50) NOT NULL,
change_time TIMESTAMP NOT NULL
)
“””
execute_query(create_log_table)
print(“日志表创建成功”)

# 创建触发函数
create_trigger_function = “””
CREATE OR REPLACE FUNCTION fgedu_log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ‘INSERT’ THEN
INSERT INTO fgedu_student_log (action, student_id, name, age, major, change_time)
VALUES (‘INSERT’, NEW.id, NEW.name, NEW.age, NEW.major, CURRENT_TIMESTAMP);
ELSIF TG_OP = ‘UPDATE’ THEN
INSERT INTO fgedu_student_log (action, student_id, name, age, major, change_time)
VALUES (‘UPDATE’, NEW.id, NEW.name, NEW.age, NEW.major, CURRENT_TIMESTAMP);
ELSIF TG_OP = ‘DELETE’ THEN
INSERT INTO fgedu_student_log (action, student_id, name, age, major, change_time)
VALUES (‘DELETE’, OLD.id, OLD.name, OLD.age, OLD.major, CURRENT_TIMESTAMP);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
“””
execute_query(create_trigger_function)
print(“触发函数创建成功”)

# 创建触发器
create_trigger = “””
CREATE TRIGGER fgedu_log_student_changes_trigger
AFTER INSERT OR UPDATE OR DELETE ON fgedu_student
FOR EACH ROW
EXECUTE FUNCTION fgedu_log_student_changes();
“””
execute_query(create_trigger)
print(“触发器创建成功”)

# 创建年龄检查触发函数
create_age_check_function = “””
CREATE OR REPLACE FUNCTION fgedu_check_student_age()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.age < 18 THEN RAISE EXCEPTION 'Student age must be at least 18'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; """ execute_query(create_age_check_function) print("年龄检查触发函数创建成功") # 创建年龄检查触发器 create_age_check_trigger = """ CREATE TRIGGER fgedu_check_student_age_trigger BEFORE INSERT OR UPDATE ON fgedu_student FOR EACH ROW EXECUTE FUNCTION fgedu_check_student_age(); """ execute_query(create_age_check_trigger) print("年龄检查触发器创建成功") def test_triggers(): """测试触发器""" # 测试插入操作 print("\n测试插入操作:") try: execute_query("INSERT INTO fgedu_student (name, age, gender, major) VALUES (%s, %s, %s, %s)", ("赵六", 22, "男", "计算机科学")) print("插入成功") except Exception as e: print(f"插入失败: {e}") # 测试年龄检查触发器 print("\n测试年龄检查触发器:") try: execute_query("INSERT INTO fgedu_student (name, age, gender, major) VALUES (%s, %s, %s, %s)", ("钱七", 17, "女", "软件工程")) print("插入成功") except Exception as e: print(f"插入失败: {e}") # 测试更新操作 print("\n测试更新操作:") execute_query("UPDATE fgedu_student SET age = %s WHERE name = %s", (23, "赵六")) print("更新成功") # 测试删除操作 print("\n测试删除操作:") execute_query("DELETE FROM fgedu_student WHERE name = %s", ("赵六",)) print("删除成功") # 查看日志 print("\n查看操作日志:") logs = execute_query("SELECT * FROM fgedu_student_log ORDER BY change_time DESC") for log in logs: print(log) def main(): """主函数""" # 初始化连接池 init_pool() try: # 创建触发器基础设施 create_trigger_infrastructure() # 测试触发器 test_triggers() finally: # 关闭连接池 postgres_pool.closeall() print("\n连接池关闭成功") if __name__ == "__main__": main()

测试插入操作:
插入成功

测试年龄检查触发器:
插入失败: Student age must be at least 18

测试更新操作:
更新成功

测试删除操作:
删除成功

查看操作日志:
(3, ‘DELETE’, 4, ‘赵六’, 23, ‘计算机科学’, datetime.datetime(2026, 4, 2, 10, 0, 0, 345678))
(2, ‘UPDATE’, 4, ‘赵六’, 23, ‘计算机科学’, datetime.datetime(2026, 4, 2, 10, 0, 0, 234567))
(1, ‘INSERT’, 4, ‘赵六’, 22, ‘计算机科学’, datetime.datetime(2026, 4, 2, 10, 0, 0, 123456))

连接池关闭成功

## 1. 数据验证

### 1.1 年龄验证

“`sql
CREATE OR REPLACE FUNCTION fgedu_check_age()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.age < 18 THEN RAISE EXCEPTION 'Age must be at least 18'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER fgedu_check_age_trigger BEFORE INSERT OR UPDATE ON fgedu_student FOR EACH ROW EXECUTE FUNCTION fgedu_check_age(); ``` ### 1.2 数据完整性验证 ```sql CREATE OR REPLACE FUNCTION fgedu_check_enrollment() RETURNS TRIGGER AS $$ DECLARE course_count INTEGER; BEGIN -- 检查学生是否已经选了该课程 SELECT COUNT(*) INTO course_count FROM fgedu_enrollment WHERE student_id = NEW.student_id AND course_id = NEW.course_id; IF course_count > 0 THEN
RAISE EXCEPTION ‘Student already enrolled in this course’;
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_check_enrollment_trigger
BEFORE INSERT ON fgedu_enrollment
FOR EACH ROW
EXECUTE FUNCTION fgedu_check_enrollment();
“`

## 2. 日志记录

### 2.1 操作日志

“`sql
CREATE OR REPLACE FUNCTION fgedu_log_changes()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO fgedu_audit_log (
table_name,
operation,
old_data,
new_data,
change_time,
fgedu_id
) VALUES (
TG_TABLE_NAME,
TG_OP,
row_to_json(OLD),
row_to_json(NEW),
CURRENT_TIMESTAMP,
current_fgedu
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_log_student_changes
AFTER INSERT OR UPDATE OR DELETE ON fgedu_student
FOR EACH ROW
EXECUTE FUNCTION fgedu_log_changes();
“`

## 3. 数据同步

### 3.1 主从表同步

“`sql
CREATE OR REPLACE FUNCTION fgedu_sync_student_info()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ‘INSERT’ THEN
INSERT INTO fgedu_student_info (student_id, name, age, major)
VALUES (NEW.id, NEW.name, NEW.age, NEW.major);
ELSIF TG_OP = ‘UPDATE’ THEN
UPDATE fgedu_student_info
SET name = NEW.name, age = NEW.age, major = NEW.major
WHERE student_id = NEW.id;
ELSIF TG_OP = ‘DELETE’ THEN
DELETE FROM fgedu_student_info
WHERE student_id = OLD.id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_sync_student_info_trigger
AFTER INSERT OR UPDATE OR DELETE ON fgedu_student
FOR EACH ROW
EXECUTE FUNCTION fgedu_sync_student_info();
“`

## 4. 计算字段

### 4.1 自动计算GPA

“`sql
CREATE OR REPLACE FUNCTION fgedu_calculate_gpa()
RETURNS TRIGGER AS $$
DECLARE
avg_grade NUMERIC;
BEGIN
SELECT AVG(grade) INTO avg_grade
FROM fgedu_enrollment
WHERE student_id = NEW.student_id;

UPDATE fgedu_student
SET gpa = COALESCE(avg_grade, 0)
WHERE id = NEW.student_id;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_calculate_gpa_trigger
AFTER INSERT OR UPDATE ON fgedu_enrollment
FOR EACH ROW
EXECUTE FUNCTION fgedu_calculate_gpa();
“`

## 5. 视图更新

### 5.1 INSTEAD OF触发器

“`sql
CREATE OR REPLACE FUNCTION fgedu_update_student_view()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ‘INSERT’ THEN
INSERT INTO fgedu_student (name, age, gender, major)
VALUES (NEW.name, NEW.age, NEW.gender, NEW.major)
RETURNING id INTO NEW.id;
ELSIF TG_OP = ‘UPDATE’ THEN
UPDATE fgedu_student
SET name = NEW.name, age = NEW.age, gender = NEW.gender, major = NEW.major
WHERE id = OLD.id;
ELSIF TG_OP = ‘DELETE’ THEN
DELETE FROM fgedu_student
WHERE id = OLD.id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_update_student_view_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE ON fgedu_student_view
FOR EACH ROW
EXECUTE FUNCTION fgedu_update_student_view();
“`

## 1. 常见问题

### 1.1 触发器不执行
– 症状：触发器应该执行但没有执行
– 原因：触发器被禁用，触发条件不满足，触发器定义错误
– 解决方案：检查触发器状态，验证触发条件，检查触发器定义

### 1.2 触发器执行错误
– 症状：触发器执行时出现错误
– 原因：触发函数内部逻辑错误，权限问题，资源不足
– 解决方案：检查触发函数代码，确保用户有执行权限，检查系统资源

### 1.3 性能问题
– 症状：触发器导致操作缓慢
– 原因：触发函数逻辑复杂，触发器数量过多，触发级别不合适
– 解决方案：优化触发函数，减少触发器数量，选择合适的触发级别

### 1.4 循环触发
– 症状：触发器之间相互触发，导致无限循环
– 原因：触发器A修改表，触发触发器B，触发器B又修改表，触发触发器A
– 解决方案：在触发函数中添加条件，避免循环触发

### 1.5 数据不一致
– 症状：触发器执行后数据不一致
– 原因：触发器逻辑错误，事务处理不当，并发操作
– 解决方案：修复触发函数逻辑，确保事务一致性，处理并发操作

## 2. 排查方法

### 2.1 检查触发器状态

“`sql
— 查看触发器状态
SELECT tgname, tgenabled
FROM pg_trigger
WHERE tgrelid = ‘fgedu_student’::regclass;

— 启用禁用的触发器
ALTER TABLE fgedu_student ENABLE TRIGGER fgedu_check_student_age_trigger;
“`

### 2.2 检查触发函数

“`sql
— 查看触发函数定义
\df fgedu_log_student_changes

— 测试触发函数
SELECT fgedu_log_student_changes();
“`

### 2.3 检查触发器执行日志

“`sql
— 开启详细日志
ALTER SYSTEM SET log_statement = ‘all’;
SELECT pg_reload_conf();

— 查看日志
SELECT * FROM pg_log;
“`

### 2.4 调试触发函数

“`sql
— 在触发函数中添加日志
CREATE OR REPLACE FUNCTION fgedu_log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE ‘Trigger executed: %’, TG_OP;
— 其他逻辑
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
“`

### 2.5 性能分析

“`sql
— 分析触发器执行时间
EXPLAIN ANALYZE INSERT INTO fgedu_student (name, age, gender, major) VALUES (‘测试’, 20, ‘男’, ‘计算机科学’);

— 监控触发器调用次数
SELECT * FROM pg_stat_fgedu_functions WHERE funcname = ‘fgedu_log_student_changes’;
“`

## 3. 解决方案

### 3.1 触发器不执行
– 启用禁用的触发器
– 验证触发条件
– 检查触发器定义
– 重启数据库服务

### 3.2 触发器执行错误
– 修复触发函数逻辑
– 确保用户有执行权限
– 检查系统资源
– 增加错误处理

### 3.3 性能问题
– 优化触发函数逻辑
– 减少触发器数量
– 选择合适的触发级别
– 使用语句级触发器

### 3.4 循环触发
– 在触发函数中添加条件
– 使用标志变量
– 禁用递归触发
– 重构触发器逻辑

### 3.5 数据不一致
– 修复触发函数逻辑
– 确保事务一致性
– 处理并发操作
– 增加数据验证