时间戳

首页 > 解决方案 > T-SQL 我的动态日志记录触发器正在工作 - 请解释为什么它不好用？

sql-server - T-SQL 我的动态日志记录触发器正在工作 - 请解释为什么它不好用？

问题描述

我昨天发布了一个关于我的动态触发器的问题，结果我的上一个问题是由于在我的unpivot. 话虽如此，我的创作正在工作！（邪恶的笑）

你们中的很多人表示担心这可能对我的数据库的整体健康状况不利，我很好奇为什么会这样……请在查看我的想法后详细说明。

这是一个可以放置在任何服务器上的任何表上的触发器（一旦你准备好必要的东西）。您要么需要遵循以下模型，要么根据自己的喜好对其进行调整。（即您自己的日志表、您自己跟踪更改者的方式等）

所有你需要的：触发器、一个日志表（见下面我的）和表中的一个字段，用于跟踪进行更改的用户（在我们的例子中，所有连接都通过一个集中的 SQL 配置文件，所以这是我们可以做到的唯一方法跟踪哪个用户进行了更改（通过在更新时将其作为参数传递）。

tblChangeLog：

触发器：（尚未添加删除/插入，可能仅用于更新）...

CREATE TRIGGER (your name here) ON (your table here)

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

DECLARE @tableName  sysname
DECLARE @tableId    INT
DECLARE @activity   VARCHAR(50)
DECLARE @sql        nvarchar(MAX)

-- DETECT AN UPDATE (Records present in both inserted and deleted)
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    BEGIN

        SET @activity = 'UPDATE'

        -- Gets TableName and TableId
        SELECT @tableName = OBJECT_NAME(parent_object_id)
            , @tableId = parent_object_id
        FROM sys.objects 
        WHERE sys.objects.name = OBJECT_NAME(@@PROCID)

        -- Get the user who made the change
        DECLARE @LastUpdUser VARCHAR(50)
        SELECT @LastUpdUser = LastUpdUser FROM inserted

        -- Stores possible column names
        CREATE TABLE #Columns (
            name varchar(100)
        )

        -- Stores only updated columns
        CREATE TABLE #Changes (
            Id              sql_variant,
            FieldName       sysname,
            FieldValue_OLD  sql_variant,
            FieldValue_NEW  sql_variant,
            DateChanged     datetime DEFAULT (GETDATE()),
            LastUpdUser     varchar(50),
            GroupNumber     int
        )

        -- Gathers names of all possible updated columns (excluding generic)
        INSERT INTO #columns
        SELECT Name
        FROM sys.columns
        WHERE object_id = @tableId
            AND Name NOT IN ('LastUpdUser', 'LastUpdDate', 'CreatedByUser', 'CreatedDate', 'ConcurrencyId')

        -- Builds 2 dynamic strings of columns to use in pivot
        DECLARE @castFields nvarchar(max) -- List of columns being cast to sql_variant
        DECLARE @listOfFields nvarchar(max) -- List of columns for unpivot
        SELECT @castFields = COALESCE(@castFields + ', ', '') + ('CAST(' + QUOTENAME(Name) + ' AS sql_variant) [' + Name + ']') FROM #columns
        SELECT @listOfFields = COALESCE(@listOfFields + ', ', '') + QUOTENAME(Name) FROM #columns WHERE Name <> 'Id'

        -- Inserting deleted/inserted data into temp tables
        SELECT * into #deleted FROM deleted
        SELECT * into #inserted FROM inserted

        SELECT @sql = ';WITH unpvt_deleted AS (
                SELECT Id, FieldName, FieldValue
                FROM
                    (SELECT ' + @castFields + '
                    FROM #deleted) p
                UNPIVOT
                    (FieldValue FOR FieldName IN
                        (' + @listOfFields + ')
                ) AS deleted_unpivot
            ),

            unpvt_inserted AS (
                SELECT Id, FieldName, FieldValue
                FROM
                    (SELECT ' + @castFields + '
                     FROM #inserted) p
                UNPIVOT
                    (FieldValue FOR FieldName IN
                        (' + @listOfFields + ')
                ) AS inserted_unpivot
            )

            INSERT INTO #Changes (Id, FieldName, FieldValue_OLD, FieldValue_NEW, LastUpdUser, GroupNumber)
            SELECT COALESCE(D.Id, I.Id) Id
                , COALESCE(D.FieldName, I.FieldName) FieldName
                , D.FieldValue AS FieldValue_OLD
                , I.FieldValue AS FieldValue_NEW
                , ''' + @LastUpdUser + '''
                , DENSE_RANK() OVER(ORDER BY I.Id) AS GroupNumber
            FROM unpvt_deleted D
                FULL OUTER JOIN unpvt_inserted I ON D.Id = I.Id AND D.FieldName = I.FieldName
            WHERE D.FieldValue <> I.FieldValue

            DECLARE @i INT = 1
            DECLARE @lastGroup INT
            SELECT @lastGroup = MAX(GroupNumber) FROM #Changes

            WHILE @i <= @lastGroup
            BEGIN

                DECLARE @Changes VARCHAR(MAX)
                SELECT @Changes = COALESCE(@Changes + ''; '', '''')
                    + UPPER(CAST(FieldName AS VARCHAR)) + '': ''
                    + '''''''' + CAST(FieldValue_OLD AS VARCHAR) + '''''' to ''
                    + '''''''' + CAST(FieldValue_NEW AS VARCHAR) + ''''''''
                FROM #Changes
                WHERE GroupNumber = @i
                ORDER BY GroupNumber

                INSERT INTO tblChangeLog (Message, TableName, PrimaryKey, Activity, CreatedByUser, CreatedDate)
                SELECT Distinct @Changes, ''' + @tableName + ''', CONVERT(VARCHAR, Id), ''' + @activity + ''', LastUpdUser, DateChanged
                FROM #Changes
                WHERE GroupNumber = @i

                SET @Changes = NULL
                SET @i += 1

            END         

            DROP TABLE #Changes
            DROP TABLE #columns
            DROP TABLE #deleted
            DROP TABLE #inserted'

        exec sp_executesql @sql

    END

END

为了使其更加通用，我的 DBA 提出了一个漂亮的脚本和虚拟表，它将这个触发器作为一行存储在虚拟表中。该脚本使用游标查找数据库中的每个表，并为每个表动态创建触发器。我们也对此进行了测试，它就像一种魅力，此时无需在此处发布。

标签： sql-servertsqltriggersdynamic-sql

解决方案

我注意到的一些事情：

  1. inserted或中可以有多个记录deleted。Sql Server 只会为一个语句调用一次触发器，即使它会影响许多行。应用程序可能是在极不可能的情况下构建的，但我已经看到这种情况多次在触发器中咬人。
  2. inserted在处理时间和处理时间之间不保留订单deleted。
  3. 我会sq_executesql为LastUpdUser. 我知道您不太可能遇到由组织分配的用户名引起的任何问题，但我总是对尽可能多地使用参数感觉更好，这可能在这里。也是如此@activity。
  4. 表现。您对每一次更改都执行此操作，并且涉及大量字符串处理。Sql Server 中的字符串处理非常昂贵，因此这可能会产生有意义的性能损失。
  5. 为什么？此功能已内置在 Sql Server中，并且从 Sql Server 2016 sp2 开始，它包含在 Standard Edition 中（无需为此获取 Enterprise）。

推荐阅读