2

SQL SERVER 2014 - Is there a way to know last time of trigger execution ?

  • I think there's not, you need to implement some script to do that – Krismorte Feb 8 '18 at 21:55
  • 3
    Are you able to modify the trigger? – McNets Feb 8 '18 at 22:31
  • @krismorte - i concur with you – Prashant Feb 8 '18 at 22:40
10

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.

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

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

  • Very nice. Only things I would add are: 1) if the intention is merely to log that the Trigger fired and not what it was inserting / updating, an even simpler INSERT can be done into the Logging table. For example: insert name and time via SELECT so.[name], SYSDATETIME() FROM sys.objects so WHERE so.[object_id] = @@PROCID;. 2) Also, wouldn't hurt to mention Module Signing to get that permission (I will come up with a better / more direct example of giving a trigger VIEW STATE STATE since this seems to come up occasionally). – Solomon Rutzky Feb 9 '18 at 15:38
  • @SolomonRutzky thanks again, I've found this docs about Module signing very interesting, just two days ago I must grant VIEW SERVER STATE permission to one of my users and now I'll try with a signature. Excuse my answers but I'm not so fluent in English as I'd wish – McNets Feb 10 '18 at 9:18
  • Thanks for those edits. Looks good. I see a minor formatting issue with a results table that I'll try to fix in a moment. Also, I just posted what should be a better, more generic example using the connection property question from several days ago: Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level. And, please don't apologize for not speaking better English. You speak better English than I speak in any other language ;-). – Solomon Rutzky Feb 15 '18 at 22:07
  • @SolomonRutzky thanks for edit, and I'm sorry I've renamed you!! – McNets Feb 15 '18 at 22:24
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.