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 to List All DML Triggers Created or Modified in the Last N Days in SQL Server?

Answer: Before we talk more let us see the script to list all All DML Triggers Created or Modified in the Last N Days in SQL Server.

SELECT
o.name as [Trigger Name],
CASE WHEN o.type = 'TR' THEN 'SQL DML Trigger'
	 WHEN o.type = 'TA' THEN 'DML Assembly Trigger' END 
	 AS [Trigger Type],
sc.name AS [Schema_Name],
OBJECT_NAME(parent_object_id) as [Table Name],
o.create_date [Trigger Create Date], 
o.modify_date [Trigger Modified Date] 
FROM sys.objects o
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE (type = 'TR' OR type = 'TA')
AND ( DATEDIFF(D,create_date, GETDATE()) < 7 OR
	DATEDIFF(D,modify_date, GETDATE()) < 7) -- Last 7 days

The script above I am listing all the triggers which are created or modified in the last 7 days. You may find it very interesting that if the trigger is created and never modified the create date and modify date both will be the same.

I am personally very much against using the triggers and have not used triggers since I started my career. I have experienced enough time that triggers slow down the entire system so much that often it is impossible to bring the database performance back to the initial level.

I always suggest to my clients when I work with them on Comprehensive Database Performance Health Check that if there is a code which they have to move into a trigger, it is suggested that they put the same code inside the stored procedure or the logic which they are using to modify the data.

Here is a few additional blog post on the topic of SQL Server Performance Tuning which you may find helpful.

SQL SERVER – An Index Reduces Performance of SELECT Queries
Can an Index reduce the performance of SELECT Queries even though that index is not used anywhere? – Of course yes, you can understand that in the video above.

SQL SERVER – Performance Comparison IN vs OR
Question: Which is query runs faster – The query with IN operator or The query with OR operator

SQL SERVER – How to Optimize Your Server Performance by Reducing IO Waits?
SQL Server performance tuning is often considered as a complex subject and many DBAs and Developers often see SQL Server Engines as a black box.

Reference: Pinal Dave (https://blog.sqlauthority.com)