如何为SQL Server数据库中的所有表创建触发器 [英] How to create a trigger for all tables in a SQL Server database

问题描述

我的数据库的所有表中都有一列 LastUpdate ，我想说插入更新 LastUpdate = getdate（）

I have a column LastUpdate in all tables of my database and I want to say "on insert of update LastUpdate = getdate()"

我可以使用触发器来执行此操作，但是我发现很难为数据库的每个表编写数百个触发器。
-如何动态创建影响所有表的触发器？
-如何为每个表动态创建触发器？

I can do this with a trigger but I find it' hard to write hundreds triggers for each table of the database. - How do I dynamically create a trigger that affect all tables? - How do I dynamically create triggers for each table ?

推荐答案

不可能触发一个触发器当任何表被更新时。

It is not possible to have a trigger that fires when any table is updated.

您可以动态生成SQL Required，如下所示：

You could generate the SQL Required dynamically, the following:

SELECT  N'
            CREATE TRIGGER trg_' + t.Name + '_Update ON ' + ObjectName + '
            AFTER UPDATE 
            AS 
            BEGIN
                UPDATE  t
                SET LastUpdate = GETDATE()
                FROM ' + o.ObjectName + ' AS t
                        INNER JOIN inserted AS i
                            ON ' + 
            STUFF((SELECT ' AND t.' + QUOTENAME(c.Name) + ' = i.' + QUOTENAME(c.Name)
                    FROM    sys.index_columns AS ic
                            INNER JOIN sys.columns AS c
                                ON c.object_id = ic.object_id
                                AND c.column_id = ic.column_id
                    WHERE   ic.object_id = t.object_id
                    AND     ic.index_id = ix.index_id
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 4, '') + ';
            END;
            GO'
FROM    sys.tables AS t
        INNER JOIN sys.indexes AS ix
            ON ix.object_id = t.object_id
            AND ix.is_primary_key = 1
        CROSS APPLY (SELECT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(t.name)) o (ObjectName)
WHERE   EXISTS 
        (   SELECT  1 
            FROM    sys.columns AS c 
            WHERE   c.Name = 'LastUpdate' 
            AND     c.object_id = t.object_id
        );

使用 LastUpdate 为每个表生成SQL列沿行：

Generates SQL for each table with a LastUpdate column along the lines of:

CREATE TRIGGER trg_TableName_Update ON [dbo].[TableName]
AFTER UPDATE 
AS 
BEGIN
    UPDATE  t
    SET     LastUpdate = GETDATE()
    FROM    [dbo].[TableName] AS t
            INNER JOIN inserted AS i
                ON  t.[PrimaryKey] = i.[PrimaryKey];
END;
GO

每个表都具有主键才能从插入表回到要更新的表。

The relies on each table having a primary key to get the join from the inserted table back to the table being updated.

您可以复制并粘贴结果并执行（我会推荐这种方式，这样您至少可以检查生成的SQL，或将其构建到游标中并使用 sp_executesql 执行它。我建议使用前者，即，使用它来保存

You can either copy and paste the results and execute them (I would recommend this way so you can at least check the SQL Generated, or build it into a cursor and execute it using sp_executesql. I would recommend the former, i.e. use this to save a bit of time, but still check each trigger before actually creating it.

我个人认为最后修改的列是一个有缺陷的概念，在我看来，它总是喜欢存储烦人的少量信息，如果您确实关心数据更改，则可以使用审计表对其进行正确跟踪：首先，知道什么时候更改了什么，但是从何而来，或者是谁更改了，比根本不知道更令人讨厌，其次，它覆盖了所有内容。以前的更改，是什么使最新的更改比所有更改都重要以前已经过去了。

I personally think last modified columns are a flawed concept, it always feels to me like storing annoyingly little information, if you really care about data changes then track them properly with an audit table. Firstly, knowing when something was changed, but what it was changed from, or who changed it is probably more annoying than not knowing at all, secondly it overwrites all previous changes, what makes the latest change more important than all those that have gone before.

这篇关于如何为SQL Server数据库中的所有表创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！