How to List All DML Triggers Created or Modified in the Last N Days in SQL Server? – Interview Question of the Week #242

Question: How do you list DML triggers created or modified in the last N days? Query the current database’s sys.objects catalog, filter the two DML trigger types, and compare their create_date and modify_date with your cutoff.

DECLARE @Days int = 7;
DECLARE @Cutoff datetime = DATEADD(DAY, -@Days, GETDATE());
SELECT o.name AS [Trigger Name],
       CASE o.type WHEN 'TR' THEN 'SQL DML Trigger'
                   WHEN 'TA' THEN 'DML Assembly Trigger' END AS [Trigger Type],
       sc.name AS [Schema_Name],
       OBJECT_NAME(o.parent_object_id) AS [Table Name],
       o.create_date AS [Trigger Create Date],
       o.modify_date AS [Trigger Modified Date]
FROM sys.objects AS o
INNER JOIN sys.schemas AS sc ON o.schema_id = sc.schema_id
WHERE o.type IN ('TR', 'TA')
  AND (o.create_date >= @Cutoff OR o.modify_date >= @Cutoff)
ORDER BY o.modify_date DESC, o.name;

This keeps the original seven-day example and its table, schema and trigger-type columns. The original DATEDIFF(D, date, GETDATE()) < 7 counted midnight boundaries. The cutoff here is a rolling seven-day interval in server-local time. Change @Days for your requirement.

These are catalog dates, not the trigger’s last execution time. Matching create and modify dates are common for a newly created trigger, but they aren’t an audit history proving that nothing ever happened to it. Metadata visibility also matters: run with permission to see the objects you are investigating. This query lists table and view DML triggers, not server-level or database-level DDL triggers.

I have been very cautious about triggers throughout my career. During a performance health check, I want to know what additional work a simple data modification is quietly invoking. Expensive trigger code runs in the modifying transaction and can turn an ordinary update into a much larger job.

My preference is to put that business logic in the stored procedure or application path that modifies the data, where the work is explicit. That is a design preference, not a claim that every trigger is slow or unnecessary. When a trigger is required, review its set-based handling of multiple rows, its dependencies and its cost.

The script answers where to look; reviewing the trigger definition and its actual workload answers whether it is a problem. A recent modify date alone doesn’t establish a performance regression.

• An index reduces SELECT performance
• IN versus OR performance
• Reducing I/O waits

Microsoft: sys.objects documents the types and catalog dates.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.