In SQL 2008 and higher there is a new feature that can help audit triggers. By running the script below you can get some important information about triggers that are running or possibly just as important, analyzing triggers that have not ran. It can also help in understanding how long it takes a trigger to run (execution time) and how many times the trigger has executed.
[code language=”sql”]
SELECT
tr.object_id,
tr.database_id,
DB_NAME(database_id) AS ‘Name_of_Database’,
OBJECT_NAME(object_id, database_id) AS ‘trigger_name’,
tr.last_execution_time,
tr.total_elapsed_time/tr.execution_count AS [avg_time],
tr.execution_count
FROM sys.dm_exec_trigger_stats AS tr
–If you want all of the triggers then comment out this next line
WHERE OBJECT_NAME(object_id, database_id)=’Name_Of_Trigger’
[/code]
Looking for quality web hosting? Look no further than Arvixe Web Hosting!
