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?

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

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.

Solarwinds

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)

Solarwinds
, ,
Previous Post
How to Use Multiple Hints Together for a Query? – Interview Question of the Week #241
Next Post
Where is Table Variable Created? – Interview Question of the Week #243

Related Posts

1 Comment. Leave new

  • I do agree with you that triggers are risky as there are a lot of cases where you can end up with undesirable results and it can be alarming when you see 2 things in the messages window about rows being updated. Nothing more scary than doing an update on a single row and seeing a notice that the entire table was modified! We ran into that one where a trigger was dumping the entire table to an auditable version of the table every time something changed in the table. it was a small table, but a 100 row table that has 10 updates happen on it, has 1000 rows in the audit table when only 10 need to be stored.
    That being said, we run service broker where I work and we need data to be synced between very specific tables on 2 different SQL instances. We use service broker for this and triggers on the source tables. When a row is inserted,updated or deleted on some very specific tables, the data gets synced across to the second system. I am not sure how we could do this without a trigger. Thoughts?

    Reply

Leave a Reply

Menu