Quando um gatilho de tabela foi desativado e reativado?

O SQL Server não rastreia essas informações (eu meio que esperava vê-las no rastreamento padrão, mas também não está lá). Ativar/desativar atualizará modify_date, mas você não poderá distinguir isso de uma renomeação ou modificação de código. Além disso, ele mostrará apenas quando ocorreu a última alteração. Se você quiser qualquer outro rastreamento (como quem fez isso), precisará implementar auditoria ou rastreamento. Achei que talvez um gatilho DDL também pudesse ser usado, mas isso parece uma lacuna de funcionalidade intencional:

• http://connect.microsoft.com/SQLServer/feedback/details/509242/fire-a-ddl-trigger-when-the-new-syntax-disable-trigger-is-executed(abandonado)
• https://connect.microsoft.com/SQLServer/feedback/details/434951/ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event(abandonado)
• Dispare um DDL TRIGGER quando a sintaxe mais recente DISABLE TRIGGER for executada

Veja como você pode capturar essas informações daqui para frente usando uma auditoria:

USE master;
GO

CREATE SERVER AUDIT MyAudit 
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB) 
  WITH (ON_FAILURE = CONTINUE); 
GO

ALTER SERVER AUDIT MyAudit WITH (STATE = ON); 
GO

USE [your_database];
GO

CREATE DATABASE AUDIT SPECIFICATION schema_db_change 
  FOR SERVER AUDIT MyAudit 
  ADD (SCHEMA_OBJECT_CHANGE_GROUP), 
  ADD (DATABASE_OBJECT_CHANGE_GROUP);

ALTER DATABASE AUDIT SPECIFICATION schema_db_change WITH (STATE = ON); 
GO

Agora vá e desative seu gatilho e execute:

SELECT event_time, succeeded, server_principal_name, [object_name], [statement] 
FROM sys.fn_get_audit_file ('C:\Temp\MyAudit*', NULL, NULL);

Se você não puder usar a auditoria devido à edição ou outros problemas, poderá fazer isso de forma relativamente barata usando um rastreamento do lado do servidor. Apenas capture SQL:BatchCompletede, opcionalmente, filtre TextData LIKE '%disable%trigger%'(Você terá que testar se é melhor no seu cenário pagar o custo do filtro para evitar coletar muito, ou apenas coletar mais e reduzi-lo mais tarde. Os filtros podem ser muito caros, mas depende no sistema.)

Tenho certeza de que também há uma maneira de fazer isso com eventos estendidos. Mas XEvents e audit requerem 2008+ e você não especificou a versão...

Melhor ainda, remova a capacidade de modificar gatilhos para usuários que ignoram o gerenciamento de alterações. Idealmente, você deve ser capaz de determinar quem ativou ou desativou um gatilho sem nunca olhar para o banco de dados, porque ninguém deveria fazer isso sem documentá-lo.

Atualmente, apenas o SQL Server Enterprise Edition e o SQL Server Developer Edition oferecem suporte ao recurso Audit para rastrear a ativação e desativação de gatilhos. ações de nível e grupos de ações:

USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'c:\audits\', MAXSIZE = 2 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

A próxima etapa é criar uma especificação de auditoria de banco de dados no nível do banco de dados. O grupo de auditoria que precisamos capturar em nosso caso é SCHEMA_OBJECT_CHANGE_GROUP - não há nenhum grupo de auditoria que capture exclusivamente ativar/desativar eventos de gatilho

USE [ACMEDB];
GO
CREATE DATABASE AUDIT SPECIFICATION schema_change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

Consulte a auditoria criada anteriormente, usando o operador LIKE para restringir as entradas capturadas àquelas relacionadas a habilitar/desabilitar gatilhos:

SELECT
       event_time AS [Time],
       server_principal_name AS [User],
       object_name AS [Object name],
       Statement
  FROM sys.fn_get_audit_file('c:\audits\ServerAudit*', NULL, NULL)
WHERE
       database_name
       =
       'ACMEDB'
   AND (
       Statement LIKE '%DISABLE%TRIGGER%'
    OR Statement LIKE '%ENABLE%TRIGGER%')ORDER BY
                                          [Time] DESC;

Os resultados mostrarão quem desativou/ativou um acionador e quando

Embora a solução que descrevemos seja aplicável apenas para usuários do SQL Server Enterprise Edition e do SQL Server Developer Edition, uma auditoria de banco de dados do SQL Server é bastante simples de ser implementada e pode ajudar no rastreamento quando os gatilhos são desabilitados/habilitados

No entanto, as informações de auditoria sobre ativação/desativação de gatilhos podem ser capturadas mesmo para as operações executadas antes da instalação da "auditoria" . Você pode ler mais sobre isso no artigo online How to audit your auditing – tracking when triggers are disabled

Isenção de responsabilidade: trabalho como engenheiro de suporte ao produto na ApexSQL

Essa operação provavelmente será registrada no rastreamento administrativo padrão . O link contém instruções sobre como visualizá-lo.

Se estiver armazenado no local padrão, você pode usar:

SELECT * 
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\log.trc', default)
GO