How to create Trigger for all tables in a bank automatically?

Question

Example scenario

I have a database with 1,000 tables.

One table call log.

---

Goal

I would like to create a Trigger "standard" in each of these tables, automatically.

That one Trigger, whenever there is any change (Insert, update, delete) it runs a insert on the table log, with a timestamp and the nome of the table in question (extra would be the name of the column(s))).

In the same context, I would like to know if there is a way view all existing triggers in a database.

Answer 1

I believe that in this way you will be able to achieve what you want:

CREATE TABLE [log]
(
        Id          UNIQUEIDENTIFIER    CONSTRAINT log_Id_DF        DEFAULT(NEWID())    NOT NULL
    ,   [Timestamp] DATETIME            CONSTRAINT log_Timestamp_DF DEFAULT(GETDATE())  NOT NULL
    ,   Tabela      NVARCHAR(255)       NOT NULL
    ,   Colunas     NVARCHAR(MAX)       NOT NULL
    ,   PRIMARY KEY (Id)
)

DECLARE @SQL AS NVARCHAR(MAX) = ''

DECLARE cur_trigger CURSOR FOR
    SELECT      '
                    CREATE TRIGGER [' + TABLE_SCHEMA + '].[tr' + TABLE_NAME + '_auto]
                    ON [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] FOR INSERT, UPDATE, DELETE AS
                    BEGIN
                        DECLARE @IdTabela   INT
                        DECLARE @Tabela     NVARCHAR(255)
                        DECLARE @Colunas    NVARCHAR(MAX)

                        SELECT  @Tabela = object_name(parent_id) 
                        FROM    sys.triggers 
                        WHERE   object_id = @@PROCID

                        SELECT  @IdTabela   = T.id
                        FROM    sysobjects P JOIN sysobjects T ON P.parent_obj = T.id 
                        WHERE   P.id = @@PROCID

                        SELECT  @Colunas = ISNULL(@Colunas + '', '', '''') + name 
                        FROM    syscolumns 
                        WHERE   id = @IdTabela   
                            AND CONVERT(VARBINARY, REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1) > 0

                        INSERT INTO [log](Tabela, Colunas) VALUES(@Tabela, @Colunas)
                    END
                '
    FROM        INFORMATION_SCHEMA.TABLES
    WHERE       TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME <> 'log'
    ORDER BY    TABLE_NAME

OPEN cur_trigger

FETCH NEXT FROM cur_trigger INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@SQL)
    FETCH NEXT FROM cur_trigger INTO @SQL   
END

CLOSE cur_trigger
DEALLOCATE cur_trigger

Basically uses dynamic SQL to build the trigger and then all you have to do is execute.
In terms of syntax is correct, but has not been tested in your scenario.

Some ideas taken from Soen: SQL Server Update Trigger, Get Only modified Fields

---

To consult all the triggers from a database, you can do the following:

SELECT      *
FROM        sys.triggers  
WHERE       [type] = 'TR'
ORDER BY    [name]