List all DDL Triggers modified in last N days in a SQL Server Database

To list all DDL triggers that were changed recently in a database, for example in the last 10 days, run the following script:

USE Database_Name
SELECT * FROM sys.triggers WHERE parent_class=0
AND DATEDIFF(D,modify_date, GETDATE()) < 10

This query will return many information about DDL triggers which were created in last 10 days in the selected database, like trigger name, trigger type (TR for SQL DDL Triggers, TA for Assembly (CLR) DDL Triggers), create and modified date for the trigger.

Following script will list all DDL triggers which were created in last 10 days in a database, no matter if they were modified after that or not:

USE Database_Name
SELECT * FROM sys.triggers WHERE parent_class=0
AND DATEDIFF(D,create_date, GETDATE()) < 10

This query will return many information about DDL triggers which were created in last 10 days in the selected database, like trigger name, trigger type (TR for SQL DDL Triggers, TA for Assembly (CLR) DDL Triggers), create and modified date for the trigger.

For DDL Triggers that were never modified after creation, the modified date and create date are the same.

To see how to disable and enable triggers in SQL Server, and how to get a list of triggers that are disabled or enabled read related article:
Disable and enable triggers in SQL Server

To see how to list all DML Triggers modified in last N days in a SQL Server Database:
List all DML Triggers modified in last N days in a SQL Server Database

To see how to list all Triggers in a SQL Server Database:
How to list all Triggers in a SQL Server Database

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">