How to create trigger for all tables in a database, automatically?

Question

How to create trigger for all tables in a database, automatically?

Navigation

#1 by (5 votes)

5

Example scenario

I have a database with 1000 tables .

A table named log .

Objective

I would like to create a default trigger on each of these tables, automatically .

This trigger , whenever there is any change (insert, update, delete) it executes a insert in the log table, with a timestamp and nome of the table in (extra would be the name of the column (s)).

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

sql sql-server

asked by anonymous 05.09.2018 / 12:36

1 answer

What is the padding for the Bootstrap container? Extract dataframes from lists with dataframes under certain criteria in R

score 5 · Accepted Answer

I think that way you can get 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 trigger and then just run.
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 query all triggers of a database, you can do the following:

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