主页 / dba / 问题 / 27255
Accepted
EngineeringSQL
Asked: 2012-10-20 07:46:26 +0800 CST

何时禁用和重新启用表触发器？

  • 772

我需要知道 SQL Server 是否本机跟踪表触发器的启用/禁用历史记录。

我查看了系统视图：
• [sys].[triggers] 包含一个 modify_date 字段
• [sys].[trigger_events] 专注于触发器 INSERT/UPDATE/DELETE 事件

您能否推荐任何其他有关触发历史的信息来源？

sql-server trigger

3 个回答

  1. Best Answer

    SQL Server 不跟踪此信息（我有一半希望在默认跟踪中看到它，但它也不存在）。启用/禁用将更新 modify_date，但您无法将其与重命名或代码修改区分开来。此外，它只会显示最后一次更改发生的时间。如果您想要任何其他跟踪（例如是谁做的），则需要实施审计或跟踪。我想也许也可以使用 DDL 触发器，但这似乎是故意的功能差距：

    • http://connect.microsoft.com/SQLServer/feedback/details/509242/fire-a-ddl-trigger-when-the-new-syntax-disable-trigger-is-executed（弃）
    • https://connect.microsoft.com/SQLServer/feedback/details/434951/ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event（弃）
    • 执行较新的语法 DISABLE TRIGGER 时触发 DDL TRIGGER

    以下是您如何使用审计来捕获这些信息：

    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

    现在去禁用你的触发器，然后运行：

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

    如果由于版本或其他问题而无法使用审核，则可以使用服务器端跟踪以相对便宜的方式执行此操作。只需捕获SQL:BatchCompleted并选择过滤TextData LIKE '%disable%trigger%'（您必须测试在您的场景中是否更好地支付过滤器的成本以避免收集太多，或者只是收集更多并稍后减少它。过滤器可能非常昂贵，但这取决于在系统上。）

    我相信还有一种方法可以通过扩展事件来做到这一点。但是 XEvents 和审计都需要 2008+ 并且您没有指定版本...

    更好的是，为绕过变更管理的用户删除修改触发器的能力。理想情况下，您应该能够在不查看数据库的情况下确定谁启用或禁用了触发器，因为没有人应该在没有记录的情况下这样做。

    • 11

  2. 目前，只有 SQL Server Enterprise Edition 和 SQL Server Developer Edition 支持 Audit 功能，以便跟踪触发器的启用和禁用要捕获这些事件，您需要首先创建一个 Server audit 规范 - SQL Server Audit 对象收集服务器或数据库 -级别动作和动作组：

    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);

    下一步是在数据库级别创建数据库审计规范。在我们的案例中，我们需要捕获的审计组是 SCHEMA_OBJECT_CHANGE_GROUP - 没有专门捕获启用/禁用触发事件的审计组

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

    查询先前创建的审计，使用 LIKE 运算符将捕获的条目缩小到与启用/禁用触发器相关的条目：

    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;

    结果将显示谁禁用/启用触发器以及何时

    在此处输入图像描述 虽然我们描述的解决方案仅适用于 SQL Server Enterprise Edition 和 SQL Server Developer Edition 用户，但 SQL Server Database Audit 的实现相当简单，并且可以帮助跟踪触发器何时被禁用/启用

    但是，即使是在安装“审计”之前执行的操作，也可以捕获有关启用/禁用触发器的审计信息。您可以在如何审核您的审核 - 跟踪何时禁用触发器在线文章中阅读有关此内容的更多信息

    免责声明：我在 ApexSQL 担任产品支持工程师

    • 6

  3. 此类操作可能会记录在默认管理跟踪中。该链接包含如何查看它的说明。

    如果它存储在默认位置，您可以使用：

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

相关问题