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)