PostgreSQL教程

查看所有触发器

-- 查看当前数据库中的所有触发器
SELECT 
    tgname AS trigger_name,
    relname AS table_name,
    tgtype AS trigger_type,
    tgenabled AS is_enabled,
    tgdeferrable AS is_deferrable,
    tginitdeferred AS is_initially_deferred,
    proname AS function_name
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE NOT tgisinternal  -- 排除系统内部触发器
ORDER BY relname, tgname;

查询结果包含以下主要列：

• trigger_name：触发器名称
• table_name：触发器关联的表名
• trigger_type：触发器类型编码
• is_enabled：触发器是否启用
• function_name：触发器函数名称

查看特定表的触发器

-- 查看特定表上的所有触发器
SELECT 
    tgname AS trigger_name,
    tgtype AS trigger_type,
    tgenabled AS is_enabled,
    tgdeferrable AS is_deferrable,
    tginitdeferred AS is_initially_deferred,
    proname AS function_name,
    pg_get_triggerdef(t.oid) AS trigger_definition
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE c.relname = 'users'  -- 指定表名
  AND NOT tgisinternal
ORDER BY tgname;

解析触发器类型

-- 查看触发器详细信息，包括事件和时机
SELECT 
    tgname AS trigger_name,
    relname AS table_name,
    CASE 
        WHEN (tgtype & 1) = 1 THEN 'ROW' 
        ELSE 'STATEMENT' 
    END AS trigger_level,
    CASE 
        WHEN (tgtype & 2) = 2 THEN 'BEFORE' 
        WHEN (tgtype & 64) = 64 THEN 'INSTEAD OF' 
        ELSE 'AFTER' 
    END AS trigger_timing,
    CASE 
        WHEN (tgtype & 4) = 4 THEN 'INSERT ' 
        ELSE '' 
    END ||
    CASE 
        WHEN (tgtype & 8) = 8 THEN 'DELETE ' 
        ELSE '' 
    END ||
    CASE 
        WHEN (tgtype & 16) = 16 THEN 'UPDATE ' 
        ELSE '' 
    END ||
    CASE 
        WHEN (tgtype & 32) = 32 THEN 'TRUNCATE ' 
        ELSE '' 
    END AS trigger_events,
    proname AS function_name,
    tgenabled AS is_enabled
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE NOT tgisinternal
ORDER BY relname, tgname;

查询information_schema.triggers表

-- 查看所有用户定义的触发器
SELECT 
    trigger_schema,
    trigger_name,
    event_manipulation,
    event_object_table,
    action_orientation,
    action_timing,
    action_condition,
    action_statement
FROM information_schema.triggers
WHERE trigger_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY trigger_schema, event_object_table, trigger_name;

-- 查看特定表的触发器
SELECT 
    trigger_name,
    event_manipulation,
    action_orientation,
    action_timing,
    action_condition,
    action_statement
FROM information_schema.triggers
WHERE event_object_table = 'users'
ORDER BY trigger_name;

-- 查看触发器的详细信息
SELECT 
    trigger_catalog,
    trigger_schema,
    trigger_name,
    event_manipulation,
    event_object_catalog,
    event_object_schema,
    event_object_table,
    action_order,
    action_condition,
    action_statement,
    action_orientation,
    action_timing,
    action_reference_old_table,
    action_reference_new_table,
    action_reference_old_row,
    action_reference_new_row,
    created
FROM information_schema.triggers
WHERE trigger_name = 'user_audit_trigger';

psql中的查看命令

-- 列出当前数据库中的所有触发器
\dT

-- 列出特定表的触发器
\dT users

-- 显示触发器的详细定义
SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname = 'user_audit_trigger';

-- 使用\d命令查看表信息，包括触发器
\d users

-- 列出所有触发器及其关联的函数
SELECT 
    t.tgname AS trigger_name,
    c.relname AS table_name,
    p.proname AS function_name,
    pg_get_triggerdef(t.oid, true) AS trigger_definition
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE NOT tgisinternal
ORDER BY c.relname, t.tgname;

查看触发器函数

-- 查看触发器函数的定义
SELECT pg_get_functiondef(oid) 
FROM pg_proc 
WHERE proname = 'audit_trigger_function';

-- 查看触发器函数的详细信息
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_function_result(p.oid) AS return_type,
    pg_get_functiondef(p.oid) AS function_definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.proname = 'audit_trigger_function';

-- 查看所有触发器函数
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prorettype = (SELECT oid FROM pg_type WHERE typname = 'trigger')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;

查询触发器依赖

-- 查看触发器依赖的函数
SELECT 
    t.tgname AS trigger_name,
    c.relname AS table_name,
    p.proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE NOT tgisinternal
ORDER BY c.relname, t.tgname;

-- 查看哪些对象依赖于触发器函数
SELECT 
    dependent_ns.nspname AS dependent_schema,
    dependent_class.relname AS dependent_object,
    dependent_proc.proname AS dependent_function
FROM pg_depend d
JOIN pg_proc p ON d.refobjid = p.oid
JOIN pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN pg_class dependent_class ON d.objid = dependent_class.oid
LEFT JOIN pg_namespace dependent_ns ON dependent_class.relnamespace = dependent_ns.oid
LEFT JOIN pg_proc dependent_proc ON d.objid = dependent_proc.oid
WHERE n.nspname = 'public' AND p.proname = 'audit_trigger_function';

-- 查看触发器的依赖关系详细信息
SELECT 
    t.tgname AS trigger_name,
    c.relname AS table_name,
    p.proname AS function_name,
    d.deptype AS dependency_type,
    CASE d.deptype
        WHEN 'n' THEN 'Normal'
        WHEN 'a' THEN 'Automatic'
        WHEN 'i' THEN 'Internal'
        WHEN 'P' THEN 'Pinned'
        WHEN 'e' THEN 'Extension'
    END AS dependency_description
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_proc p ON t.tgfoid = p.oid
JOIN pg_depend d ON t.oid = d.refobjid
WHERE NOT tgisinternal
ORDER BY c.relname, t.tgname;

在Python中查看触发器信息

import psycopg2

def get_trigger_info(connection, table_name=None):
    """
    获取PostgreSQL触发器信息
    """
    cursor = connection.cursor()
    
    try:
        if table_name:
            # 获取特定表的触发器信息
            query = """
                SELECT 
                    tgname AS trigger_name,
                    relname AS table_name,
                    proname AS function_name,
                    pg_get_triggerdef(t.oid) AS trigger_definition
                FROM pg_trigger t
                JOIN pg_class c ON t.tgrelid = c.oid
                JOIN pg_proc p ON t.tgfoid = p.oid
                WHERE c.relname = %s AND NOT tgisinternal
                ORDER BY tgname
            """
            cursor.execute(query, (table_name,))
        else:
            # 获取所有触发器信息
            query = """
                SELECT 
                    tgname AS trigger_name,
                    relname AS table_name,
                    proname AS function_name,
                    pg_get_triggerdef(t.oid) AS trigger_definition
                FROM pg_trigger t
                JOIN pg_class c ON t.tgrelid = c.oid
                JOIN pg_proc p ON t.tgfoid = p.oid
                WHERE NOT tgisinternal
                ORDER BY relname, tgname
            """
            cursor.execute(query)
        
        triggers = cursor.fetchall()
        
        print("触发器信息：")
        for trigger in triggers:
            print(f"触发器名称: {trigger[0]}")
            print(f"关联表名: {trigger[1]}")
            print(f"触发器函数: {trigger[2]}")
            print(f"触发器定义: {trigger[3]}")
            print("-" * 50)
            
        return triggers
        
    except Exception as e:
        print(f"获取触发器信息时出错: {e}")
        return []
        
    finally:
        cursor.close()

# 使用示例
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="username",
    password="password"
)

# 获取所有触发器信息
get_trigger_info(conn)

# 获取特定表的触发器信息
get_trigger_info(conn, "users")

conn.close()

在Java中查看触发器信息

import java.sql.*;

public class ViewPostgreSQLTriggers {
    public static void getTriggerInfo(Connection conn, String tableName) {
        try {
            String query;
            PreparedStatement stmt;
            
            if (tableName != null && !tableName.isEmpty()) {
                // 获取特定表的触发器信息
                query = """
                    SELECT 
                        tgname AS trigger_name,
                        relname AS table_name,
                        proname AS function_name,
                        pg_get_triggerdef(t.oid) AS trigger_definition
                    FROM pg_trigger t
                    JOIN pg_class c ON t.tgrelid = c.oid
                    JOIN pg_proc p ON t.tgfoid = p.oid
                    WHERE c.relname = ? AND NOT tgisinternal
                    ORDER BY tgname
                """;
                stmt = conn.prepareStatement(query);
                stmt.setString(1, tableName);
            } else {
                // 获取所有触发器信息
                query = """
                    SELECT 
                        tgname AS trigger_name,
                        relname AS table_name,
                        proname AS function_name,
                        pg_get_triggerdef(t.oid) AS trigger_definition
                    FROM pg_trigger t
                    JOIN pg_class c ON t.tgrelid = c.oid
                    JOIN pg_proc p ON t.tgfoid = p.oid
                    WHERE NOT tgisinternal
                    ORDER BY relname, tgname
                """;
                stmt = conn.prepareStatement(query);
            }
            
            ResultSet rs = stmt.executeQuery();
            
            System.out.println("触发器信息：");
            while (rs.next()) {
                System.out.println("触发器名称: " + rs.getString("trigger_name"));
                System.out.println("关联表名: " + rs.getString("table_name"));
                System.out.println("触发器函数: " + rs.getString("function_name"));
                System.out.println("触发器定义: " + rs.getString("trigger_definition"));
                System.out.println("----------------------------------------");
            }
            
            rs.close();
            stmt.close();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String user = "username";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // 获取所有触发器信息
            getTriggerInfo(conn, null);
            
            // 获取特定表的触发器信息
            getTriggerInfo(conn, "users");
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}