如何在SQL Server中捕获ADF管道修改表的用户名及时间戳？

针对ADF管道操作SQL Server表时无法通过触发器捕获操作元数据的问题，提供以下几种可行方案：

方案一：ADF管道直接传递元数据到SQL操作 #

这种方式最直接，在ADF执行SQL更新时，主动将管道运行信息作为参数传入，同步更新业务表或写入审计表。

步骤： #

1. 添加审计字段/创建审计表

   • 若要在业务表中直接记录，添加字段：LastUpdatedBy（NVARCHAR(100)）、LastUpdatedTimestamp（DATETIME）、PipelineRunId（NVARCHAR(50)）
   • 或单独创建审计表，示例结构：

     CREATE TABLE TableAudit (
         AuditId INT IDENTITY(1,1) PRIMARY KEY,
         TableName NVARCHAR(100) NOT NULL,
         OperationType NVARCHAR(20) NOT NULL,
         PipelineRunId NVARCHAR(50),
         RunTimestamp DATETIME NOT NULL,
         ExecutedBy NVARCHAR(100) NOT NULL,
         ChangeTime DATETIME DEFAULT GETUTCDATE()
     )
     

2. 在ADF中获取元数据
   ADF提供系统变量可直接获取所需信息：

   • 管道运行时间戳：@pipeline().TriggerTime（触发时间）或@utcNow()（当前时间）
   • 执行用户名：
     • 手动触发管道：@pipeline().TriggeredBy.Name（触发者的Azure AD用户名）
     • 服务主体/自动化触发：使用ADF连接SQL Server的认证用户名（如配置的服务主体名称，或从密钥库读取）
   • 管道运行ID：@pipeline().RunId

3. 通过存储过程执行更新+审计
   创建包含元数据参数的存储过程，在ADF中调用：

   CREATE PROCEDURE UpdateCustomerTable
       @CustomerId INT,
       @NewEmail NVARCHAR(100),
       @PipelineRunId NVARCHAR(50),
       @RunTimestamp DATETIME,
       @ExecutedBy NVARCHAR(100)
   AS
   BEGIN
       BEGIN TRANSACTION
       -- 更新业务表
       UPDATE Customers
       SET Email = @NewEmail,
           LastUpdatedBy = @ExecutedBy,
           LastUpdatedTimestamp = @RunTimestamp,
           PipelineRunId = @PipelineRunId
       WHERE CustomerId = @CustomerId;
   
       -- 写入审计表
       INSERT INTO TableAudit (TableName, OperationType, PipelineRunId, RunTimestamp, ExecutedBy)
       VALUES ('Customers', 'UPDATE', @PipelineRunId, @RunTimestamp, @ExecutedBy);
       COMMIT TRANSACTION
   END
   

   在ADF的「存储过程活动」中，将上述系统变量映射到存储过程参数即可。

方案二：利用SQL Server会话上下文增强触发器 #

如果想保留原有触发器逻辑，可通过ADF先设置SQL会话上下文，让触发器读取ADF的元数据。

步骤： #

1. ADF中设置会话上下文
   在执行更新SQL之前，先执行以下语句（可放在同一个「SQL脚本活动」中，用分号分隔）：

   -- 设置ADF元数据到会话上下文
   EXEC sp_set_session_context @key = 'ADF_PipelineRunId', @value = '@{pipeline().RunId}';
   EXEC sp_set_session_context @key = 'ADF_ExecutedBy', @value = '@{pipeline().TriggeredBy.Name}';
   EXEC sp_set_session_context @key = 'ADF_RunTimestamp', @value = '@{pipeline().TriggerTime}';
   
   -- 执行实际更新操作
   UPDATE Customers SET Email = 'new@example.com' WHERE CustomerId = 1;
   

2. 修改触发器读取会话上下文
   更新原有触发器，优先读取会话上下文的ADF信息，没有则使用默认的手动操作信息：

   ALTER TRIGGER Trigger_Customers_Update
   ON Customers
   AFTER UPDATE
   AS
   BEGIN
       SET NOCOUNT ON;
       DECLARE 
           @PipelineRunId NVARCHAR(50) = SESSION_CONTEXT(N'ADF_PipelineRunId'),
           @ExecutedBy NVARCHAR(100) = SESSION_CONTEXT(N'ADF_ExecutedBy'),
           @RunTimestamp DATETIME = SESSION_CONTEXT(N'ADF_RunTimestamp');
   
       -- 手动操作时回退到默认值
       SET @ExecutedBy = ISNULL(@ExecutedBy, SUSER_NAME());
       SET @RunTimestamp = ISNULL(@RunTimestamp, GETUTCDATE());
   
       -- 插入审计记录
       INSERT INTO TableAudit (TableName, OperationType, PipelineRunId, RunTimestamp, ExecutedBy)
       SELECT 
           'Customers', 'UPDATE', @PipelineRunId, @RunTimestamp, @ExecutedBy
       FROM inserted;
   END
   

方案三：ADF监控日志+SQL变更追踪（非实时） #

如果不需要实时审计，可开启SQL Server的变更追踪记录数据变更，再从ADF的运行日志（可通过Azure Monitor导出到存储或Log Analytics）中匹配对应管道的运行信息，关联得到完整审计数据。这种方式适合批量审计场景，但配置相对复杂。

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