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

Question

我昨天发布了一个有关我的动态触发器的问题，事实证明我的最后一个问题是由于我的 unpivot中包含一个太多字段。话虽这么说，我的创作正在奏效！（邪恶的笑）

你们中的许多人都表示担心，这可能对我的数据库的整体健康不利，我很好奇为什么会...请在查看我提出的内容后详细说明。

这是一个触发器，可以将其放置在任何服务器上的任何表上（一旦您拥有必要的内容）。您要么需要遵循以下模型，要么将其调整为您的喜好。（即您自己的日志表，您自己的追踪方式，谁进行了更改等）

）

您需要的所有内容：触发器，一个记录表（请参见下面的我）以及表跟踪进行更改的用户的字段（在我们的情况下，所有连接都通过一个集中式的一个集中式SQL配置文件，因此这是我们跟踪哪个用户进行更改的唯一方法（通过在更新时将其作为参数传递））。

tblchangelog：

• id- int-不是null（识别（1，1））
• 消息 - varchar（max） - 不是null
• tablename -varchar（50） - 不是null
• primarykey -varchar（50） - 非null
• 活动-varchar（50） - 非null
• createbyuser -varchar（30） - 非null -default（'system'）
• createDate -dateTime-不是null -default（getDate（））

trigger ：（尚未添加删除/插入，可能只为更新执行此操作）...

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提出了一个漂亮的脚本和虚拟表，可以将此触发器存储在虚拟表中。脚本使用光标，并在数据库上找到每个表格，并为每个表格创建一个触发器。我们也已经测试过，它的工作就像魅力一样，目前不费心发布。

Answer 1

我注意到的一些事情：

1. inserted或deleted中可能有多个记录。SQL Server即使影响许多行，也只会调用一次触发器。在极不可能的情况下，可能会构建应用程序，但是我看到这又回到了很多次诱因的人。
2. 在处理inserted和deleted之间未保留订单。
3. 我将对LastUpdUser使用sq_executesql的参数名称。我知道您不太可能遇到由组织分配的用户名造成的任何问题，但是我总是在尽可能多地使用参数时感觉更好，并且在这里也可能会更好。@activity也是如此。
4. 性能。您是在上进行的，每个更改 ，它涉及大量的字符串处理。SQL Server中的字符串处理非常昂贵，因此这可能会造成有意义的性能惩罚。
5. 为什么？此功能是已经内置的SQL Server ，并且按照SQL Server 2016 SP2的标准版本包含在标准版中（不需要为此获得企业）。

Answer 2

被视为不良使用的主要原因是，您在每个交易中添加了大量的额外写入。当所有这些步骤不必要时

由于表结构相当静态，因此触发器应该同样静态。以动态方式为整个数据库创建触发器的想法是可以理解的，但是对这些触发器和表的支持应遵循适当的源控制。

当然，SQL注入是人们使用动态代码提及的东西。但是，如果您的列名称将创建SQL注入，则可能会有更大的问题。