SQL Server 2014：如何查询触发器的最后执行时间？

嘿，在SQL Server 2014里，系统本身并没有直接提供一个内置的系统视图或函数来直接查询触发器的最后执行时间，但咱们可以通过几种变通方案来实现这个需求，下面给你详细拆解：

方法一：给触发器添加自定义日志记录（最可靠的方案） #

这是最稳妥的方式，因为它会主动记录每次触发器的执行信息，不受缓存或追踪工具的限制。步骤如下：

1. 首先创建一个专门的触发器执行日志表：

CREATE TABLE TriggerExecutionLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    TriggerName NVARCHAR(128),
    ExecutionTime DATETIME DEFAULT GETDATE(),
    AffectedTable NVARCHAR(128),
    ExecutionUser NVARCHAR(128) DEFAULT SUSER_SNAME()
);

2. 修改目标触发器，在其逻辑中插入一条日志记录：

ALTER TRIGGER [dbo].[YourTriggerName]
ON [dbo].[YourTableName]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- 原触发器逻辑保持不变
    -- ...

    -- 添加日志记录
    INSERT INTO TriggerExecutionLog (TriggerName, AffectedTable)
    VALUES (OBJECT_NAME(@@PROCID), OBJECT_NAME(parent_id));
END

之后要查看触发器最后执行时间，直接查询这个日志表就行：

SELECT TOP 1 TriggerName, ExecutionTime
FROM TriggerExecutionLog
WHERE TriggerName = 'YourTriggerName'
ORDER BY ExecutionTime DESC;

优点：数据持久化，不会丢失，能看到所有执行历史；缺点：需要修改现有触发器，对业务有轻微侵入。

方法二：使用Extended Events追踪触发器执行 #

SQL Server 2014支持Extended Events（比老的SQL Server Profiler更轻量、高效），可以用来捕获触发器的执行事件：

1. 创建一个Extended Events会话，追踪sql_statement_completed事件，过滤对象类型为触发器：

CREATE EVENT SESSION [TriggerExecutionTracking] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text, sqlserver.username)
    WHERE object_type = 8272) -- 8272对应触发器的对象类型码
ADD TARGET package0.event_file(SET filename=N'TriggerExecution.xel')
WITH (STARTUP_STATE=OFF);

2. 启动会话后，触发器执行的信息会被记录到指定的.xel文件中。你可以用以下查询解析日志文件：

SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS ExecutionTime,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText,
    event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(128)') AS ExecutionUser
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('TriggerExecution*.xel', NULL, NULL, NULL)
) AS x
ORDER BY ExecutionTime DESC;

优点：不需要修改触发器，对业务无侵入；缺点：会话不启动就没有数据，日志文件需要定期管理，避免占用过多磁盘空间。

方法三：通过查询执行计划缓存间接获取（局限性较大） #

SQL Server会将最近执行的查询计划缓存起来，我们可以通过系统视图关联查询触发器的执行时间，但这个方法依赖缓存，如果缓存被清空（比如重启服务、内存不足），数据就会丢失：

SELECT 
    t.name AS TriggerName,
    MAX(qs.last_execution_time) AS LastExecutionTime
FROM sys.triggers t
JOIN sys.dm_exec_query_stats qs ON OBJECT_ID(t.object_id) = qs.object_id
JOIN sys.dm_exec_sql_text(qs.sql_handle) st ON qs.sql_handle = st.sql_handle
WHERE t.name = 'YourTriggerName'
GROUP BY t.name;

优点：无需修改触发器或创建额外对象；缺点：数据不可靠，缓存失效后就无法获取历史执行时间。

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