CODEWITHSUNDEEP
sqlt-sqltriggers
Bellash
Bellash

Reputation: 8184

How to create a trigger for all tables in a SQL Server database

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 ?

Upvotes: 6

Views: 16838

Answers (1)

GarethD
GarethD

Reputation: 69759

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

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

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.

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 (or temporal tables, or using Change Tracking). Firstly, knowing when something was changed, but not 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.

Upvotes: 14

Related Questions