I am trying to attach a trigger to the sys table
sys.dm_db_missing_index_details with the following query:
CREATE TRIGGER indexBackup ON sys.dm_db_missing_index_details AFTER INSERT,DELETE,UPDATE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here EXEC PerformanceTest.[dbo].[usp_persist_missing_index_DMV_data] END GO
I am getting the following error:
sys.dm_db_missing_index_detailsdoes not exist or is invalid for this operation.
Is it allowed to attach a trigger to a sys view or am i missing something?
No triggers on system DMVs, sorry.
Besides, you wouldn’t really want to do this: that DMV is updated every time a query is run with a missing index request. That could be hundreds or thousands of times per second – and performance matters there.
Instead, consider running an Agent job every 5-15 minutes to capture the data you’re looking for.