如何实现数据库对象变更日志记录 存储过程修改时留存新旧代码

要捕获存储过程、表、视图等数据库对象的增删改操作，你需要使用DDL触发器，这和捕获DML行变更的表级触发器逻辑类似，但作用在数据库/服务器级别，专门响应CREATE/ALTER/DROP这类数据定义操作。

核心实现逻辑 #

• DDL触发器会在指定的DDL操作执行时触发，可获取到事件的完整元数据：操作账号、操作时间、操作类型、变更对象名、变更前后的对象定义代码
• 一般会先创建独立的DDL变更日志表，触发器触发时将捕获到的信息写入日志表留存

主流数据库实现示例 #

1. SQL Server #

第一步：创建DDL变更日志表

CREATE TABLE DDL_Change_Log (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventTime DATETIME DEFAULT GETDATE(),
    LoginName NVARCHAR(100),
    EventType NVARCHAR(100),
    ObjectName NVARCHAR(200),
    ObjectType NVARCHAR(100),
    OldDefinition NVARCHAR(MAX),
    NewDefinition NVARCHAR(MAX),
    ExecuteSQL NVARCHAR(MAX)
);

第二步：创建数据库级DDL触发器

CREATE TRIGGER TR_DDL_Capture_All_Changes
ON DATABASE
-- 按需添加需要监控的DDL事件，这里以存储过程、表、视图为例
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
BEGIN
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @ObjectName NVARCHAR(200) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(200)');
    DECLARE @ObjectType NVARCHAR(100) = @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)');
    DECLARE @EventType NVARCHAR(100) = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)');
    DECLARE @ExecSQL NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
    DECLARE @OldDef NVARCHAR(MAX) = NULL;
    DECLARE @NewDef NVARCHAR(MAX) = @ExecSQL;

    -- ALTER操作时提前获取变更前的对象定义
    IF @EventType LIKE 'ALTER_%'
    BEGIN
        SET @OldDef = OBJECT_DEFINITION(OBJECT_ID(@ObjectName));
    END
    -- DROP操作时获取删除前的对象定义
    IF @EventType LIKE 'DROP_%'
    BEGIN
        SET @OldDef = OBJECT_DEFINITION(OBJECT_ID(@ObjectName));
        SET @NewDef = NULL;
    END

    INSERT INTO DDL_Change_Log(LoginName, EventType, ObjectName, ObjectType, OldDefinition, NewDefinition, ExecuteSQL)
    VALUES(ORIGINAL_LOGIN(), @EventType, @ObjectName, @ObjectType, @OldDef, @NewDef, @ExecSQL);
END;

2. MySQL #

MySQL使用schema级别的DDL触发器实现，需要提前开启事件调度器：

-- 1. 创建日志表
CREATE TABLE ddl_change_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    event_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    operate_user VARCHAR(100),
    event_type VARCHAR(100),
    object_name VARCHAR(200),
    object_type VARCHAR(100),
    old_definition TEXT,
    new_definition TEXT
);

-- 2. 示例：捕获存储过程变更的触发器
DELIMITER //
CREATE TRIGGER tr_capture_procedure_change
BEFORE ALTER ON *.*
FOR EACH STATEMENT
BEGIN
    DECLARE old_def TEXT;
    -- 先获取变更前的存储过程定义
    SELECT ROUTINE_DEFINITION INTO old_def 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_SCHEMA = DATABASE() AND ROUTINE_NAME = NEW.ROUTINE_NAME AND ROUTINE_TYPE = 'PROCEDURE';
    
    INSERT INTO ddl_change_log(operate_user, event_type, object_name, object_type, old_definition, new_definition)
    VALUES(CURRENT_USER(), 'ALTER_PROCEDURE', NEW.ROUTINE_NAME, 'PROCEDURE', old_def, NEW.ROUTINE_DEFINITION);
END //
DELIMITER ;

3. PostgreSQL #

PostgreSQL使用事件触发器实现DDL捕获：

-- 1. 创建日志表
CREATE TABLE ddl_change_log (
    log_id SERIAL PRIMARY KEY,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    operate_user TEXT,
    event_type TEXT,
    object_name TEXT,
    object_type TEXT,
    old_definition TEXT,
    new_definition TEXT
);

-- 2. 创建触发器处理函数
CREATE OR REPLACE FUNCTION record_ddl_change()
RETURNS event_trigger AS $$
DECLARE
    obj record;
    old_def TEXT;
    new_def TEXT;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        -- 针对存储过程/函数获取定义
        IF obj.object_type = 'procedure' OR obj.object_type = 'function' THEN
            SELECT prosrc INTO old_def FROM pg_proc WHERE oid = obj.objid;
            new_def := pg_get_functiondef(obj.objid);
        END IF;
        INSERT INTO ddl_change_log(operate_user, event_type, object_name, object_type, old_definition, new_definition)
        VALUES(current_user, obj.command_tag, obj.object_identity, obj.object_type, old_def, new_def);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 3. 绑定事件触发器
CREATE EVENT TRIGGER tr_ddl_log ON ddl_command_end
EXECUTE FUNCTION record_ddl_change();

注意事项 #

• 仅监控需要的DDL事件即可，过度监控会增加数据库额外性能开销
• DDL变更日志表需要设置严格的权限，仅管理员可读写，避免变更人篡改日志记录
• DROP类操作的触发器需要设置为BEFORE触发，否则对象删除后无法获取旧的定义代码

内容的提问来源于stack exchange，提问作者DarthVader8848