如何获取SQL触发器的触发来源（存储过程/SQL作业）？

针对你需要记录触发器触发来源（存储过程或SQL作业）的需求，以下几种方法可以实现：

1. 直接用系统函数获取存储过程调用方 #

如果触发操作来自存储过程，在触发器里调用OBJECT_NAME(@@PROCID)就能直接返回对应的存储过程名称。如果是直接执行的SQL语句，这个函数会返回NULL。

把这个字段加入你的日志插入逻辑即可，示例：

INSERT INTO product_change_log (
    productnr, old_length, new_length, log_date, log_time, [user], source_proc
)
SELECT
    inserted.productnr,
    deleted.length,
    inserted.length,
    GETDATE(),
    CONVERT(TIME, GETDATE()),
    ORIGINAL_LOGIN(),
    OBJECT_NAME(@@PROCID) -- 新增存储过程名称字段
FROM inserted
JOIN deleted ON inserted.productnr = deleted.productnr

2. 捕获SQL Server Agent作业信息 #

如果是SQL作业触发的修改，可以通过系统视图关联获取作业名：

• 作业执行时，会话的program_name通常格式为SQLAgent - TSQL JobStep (Job 0xXXXX... Step X)，可以解析这个字符串提取作业ID，再关联msdb库的作业表获取名称。
• 也可以通过当前会话的执行SQL文本，匹配作业步骤的命令内容。

示例代码片段（在触发器中使用）：

DECLARE @job_name NVARCHAR(128)
DECLARE @program_name NVARCHAR(128) = (SELECT program_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID)

-- 解析program_name提取作业ID
IF @program_name LIKE 'SQLAgent - TSQL JobStep%'
BEGIN
    DECLARE @job_hex NVARCHAR(50) = SUBSTRING(@program_name, CHARINDEX('Job ', @program_name) + 4, 36)
    SELECT @job_name = name FROM msdb.dbo.sysjobs WHERE CONVERT(NVARCHAR(50), job_id) = @job_hex
END

-- 插入日志时带上作业名
INSERT INTO product_change_log (
    productnr, old_length, new_length, log_date, log_time, [user], source_job
)
SELECT
    inserted.productnr,
    deleted.length,
    inserted.length,
    GETDATE(),
    CONVERT(TIME, GETDATE()),
    ORIGINAL_LOGIN(),
    @job_name
FROM inserted
JOIN deleted ON inserted.productnr = deleted.productnr

注意：这种方法对通用SQL语句的作业可能存在匹配不准的情况，建议结合实际场景调整。

3. 手动传递上下文信息 #

如果是自定义的存储过程或作业，可以在执行修改操作前，通过CONTEXT_INFO传递自定义标识，触发器里读取这个值作为来源：

存储过程中设置上下文： #

-- 把来源信息转成VARBINARY类型存入上下文
SET CONTEXT_INFO CAST('来源：存储过程PROD_UPDATE_PROC' AS VARBINARY(128))

-- 执行修改产品表的操作
UPDATE products SET length = ... WHERE productnr = 'APPLETREE'

-- 重置上下文（可选）
SET CONTEXT_INFO 0x00

触发器中读取上下文： #

INSERT INTO product_change_log (
    productnr, old_length, new_length, log_date, log_time, [user], trigger_source
)
SELECT
    inserted.productnr,
    deleted.length,
    inserted.length,
    GETDATE(),
    CONVERT(TIME, GETDATE()),
    ORIGINAL_LOGIN(),
    CAST(CONTEXT_INFO() AS VARCHAR(128)) -- 转成字符串读取来源
FROM inserted
JOIN deleted ON inserted.productnr = deleted.productnr

4. 用扩展事件补充追踪 #

如果上述方法无法满足需求，可以创建SQL Server扩展事件会话，追踪sql_statement_completed事件，同时捕获object_name（存储过程名）、session_id、start_time等信息，后续通过时间戳和会话ID与触发器日志关联，完整还原触发来源。

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