Question :
SQL SERVER 2014 – Is there a way to know last time of trigger execution ?
Answer :
If your trigger is in cache you can use: sys.dm_exec_trigger_stats
Returns aggregate performance statistics for cached triggers. The view contains one row per trigger, and the lifetime of the row is as long as the trigger remains cached. When a trigger is removed from the cache, the corresponding row is eliminated from this view.
SELECT d.object_id,
d.database_id,
DB_NAME(database_id) AS 'database_name',
OBJECT_NAME(object_id, database_id) AS 'trigger_name',
d.cached_time,
d.last_execution_time,
d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time,
d.execution_count
FROM sys.dm_exec_trigger_stats AS d
ORDER BY [total_worker_time] DESC;
+------------+-------------+---------------+----------------------+-------------------------+-------------------------+--------------------+------------------+-------------------+-----------------+
| object_id | database_id | database_name | trigger_name | cached_time | last_execution_time | total_elapsed_time | avg_elapsed_time | last_elapsed_time | execution_count |
+------------+-------------+---------------+----------------------+-------------------------+-------------------------+--------------------+------------------+-------------------+-----------------+
| 1872725724 | 7 | dwsystem | DWUserSettings_Mod | 2018-02-08 08:58:26.660 | 2018-02-08 17:24:58.600 | 20987 | 677 | 495 | 31 |
+------------+-------------+---------------+----------------------+-------------------------+-------------------------+--------------------+------------------+-------------------+-----------------+
| 1648724926 | 7 | dwsystem | DWWorkFlow_Mod | 2018-02-08 07:22:01.247 | 2018-02-08 23:22:01.320 | 17030 | 946 | 640 | 18 |
+------------+-------------+---------------+----------------------+-------------------------+-------------------------+--------------------+------------------+-------------------+-----------------+
| 1808725496 | 7 | dwsystem | DWWorkflowStatus_Mod | 2018-02-08 07:22:23.337 | 2018-02-08 23:22:23.933 | 9409 | 522 | 710 | 18 |
+------------+-------------+---------------+----------------------+-------------------------+-------------------------+--------------------+------------------+-------------------+-----------------+
Permissions
On SQL Server, requires VIEW SERVER STATE permission. On SQL Database
Premium Tiers, requires the VIEW SERVER STATE permission in the
database. On SQL Database Standard and Basic Tiers, requires the
Server admin or an Azure Active Directory admin account.
If you can modify the trigger then you could use a TriggerLog table and add a record (or update always the same), every time trigger is fired.
Let me thank Sean Gallardy – Microsoft for providing me next example:
CREATE DATABASE TrgTest;
GO
USE TrgTest;
GO
CREATE TABLE Test
(
ID INT IDENTITY(1,1)
);
GO
CREATE TABLE Logging
(
SomeLoggingCrap VARCHAR(1000) NOT NULL
)
GO
CREATE TRIGGER KittensHateTriggers
ON dbo.Test
FOR INSERT
AS
BEGIN
INSERT INTO Logging(SomeLoggingCrap)
SELECT CONCAT('Logged #', ID) FROM inserted
END
GO
DBCC FREEPROCCACHE
INSERT INTO Test DEFAULT VALUES
SELECT *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
As Solomon Rutzky has pointed out on his comment, Logging table should store information relative to the execution time, procedure, etc.
CREATE TABLE Test
(
ID INT IDENTITY(1,1)
);
CREATE TABLE Logging
(
Id int IDENTITY PRIMARY KEY,
ProcName sysname NULL,
SysDate datetime2(7) NOT NULL
);
GO
CREATE TRIGGER KittensHateTriggers
ON dbo.Test
FOR INSERT
AS
BEGIN
INSERT INTO Logging(ProcName, SysDate)
SELECT so.[name],
SYSDATETIME()
FROM sys.objects so
WHERE so.[object_id] = @@PROCID;
END
GO
INSERT INTO Test DEFAULT VALUES;
GO
SELECT * FROM Logging;
GO
Returns:
+----+---------------------+---------------------+
| Id | ProcName | SysDate |
+----+---------------------+---------------------+
| 1 | KittensHateTriggers | 09/02/2018 22:39:13 |
+----+---------------------+---------------------+
dbfiddle here
And I highly recommend to have a look at Solomon’s answers, in this case in relation to the VIEW SERVER STATE permission needed to execute the first part of the answer.
Starting in SQL Server 2005, Microsoft provided a mechanism to infer permissions via a “proxy”. The proxy for the permissions is either a Login (for Server-level permissions) or a User (for Database-level permissions). In either case, the principal (server or database) is created from either an Asymmetric Key or Certificate, is granted the appropriate permissions, and then the permissions get inferred by signing one or more pieces of code using ADD SIGNATURE. By doing this, you are giving the code the permissions that you had granted to that server or database principal
You will need to execute this query to get last execution time
SELECT TOP 1 d.object_id, d.database_id, DB_NAME(database_id) AS 'database_name',
OBJECT_NAME(object_id, database_id) AS 'trigger_name', d.cached_time,
d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_trigger_stats AS d
WHERE OBJECT_NAME(object_id, database_id)='TriggerName'
ORDER BY [total_worker_time] DESC;
Still you haven’t receive trigger info, check this link sys.dm_exec_trigger_stats (Transact-SQL)
SELECT * FROM sys.dm_exec_trigger_stats query contains the cached time and the last execution time of the trigger.
If you got any permission errors: The user does not have permission to perform this action.
The message is very clear, you don’t have the permissions to query these information, It requires VIEW SERVER STATE permission on server.
By using SQL Profiler we can trace TRIGGER while execution