Create triggers Dynamically on Database

Create triggers Dynamically on Database

create Proc CreateTriggers
as
BEGIN
SET nocount ON

DECLARE @t TABLE
  (
     NAME VARCHAR(50)
  )

EXEC DropTrigger
/*

CREATE PROC DropTrigger
AS
  BEGIN
      SET nocount ON

      DECLARE @t TABLE
        (
           NAME VARCHAR(50)
        )

      INSERT INTO @t
      SELECT DISTINCT table_name
      FROM   information_schema.COLUMNS

      DECLARE @TableName VARCHAR(50)=''
      DECLARE cur CURSOR FOR
        SELECT NAME
        FROM   @t

      OPEN cur

      FETCH next FROM cur INTO @TableName

      WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @DROPTrigger VARCHAR(max)=''
            DECLARE @DROPTABLE VARCHAR(max)=''
            DECLARE @CREATELOGTBL VARCHAR(max)=''
            DECLARE @TRIGGERCREATE VARCHAR(max)=''
            DECLARE @STR VARCHAR(max)=''

            SELECT @STR = @STR + ', ' ++ '[' + column_name + ']'
            FROM   information_schema.COLUMNS
            WHERE  table_name = @TableName

            DECLARE @DropTRG VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
              + ''') is not null' + Char(10) + 'begin' + Char(10)
              + 'drop trigger trg' + @tableName + ';' + Char(10)
              + 'end'

            SET @DROPTrigger= @DropTRG

            --EXEC(@DROPTrigger)
            DECLARE @CREATE VARCHAR(100)='CREATE TABLE trg' + @tableName +
                                         '_History('
            DECLARE @Drop VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
              + '_History'') is not null' + Char(10) + 'begin'
              + Char(10) + 'drop table trg' + @tableName
              + '_History' + Char(10) + 'end'

            SET @DROPTABLE= @drop

            EXEC(@DROPTrigger)

            EXEC(@DROPTABLE)

            FETCH next FROM cur INTO @TableName
        END

      CLOSE cur

      DEALLOCATE cur

      SET nocount OFF
  END

*/
INSERT INTO @t
SELECT DISTINCT table_name
FROM   information_schema.COLUMNS

DECLARE @TableName VARCHAR(50)=''
DECLARE cur CURSOR FOR
  SELECT NAME
  FROM   @t

OPEN cur

FETCH next FROM cur INTO @TableName

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @DROPTrigger VARCHAR(max)=''
      DECLARE @DROPTABLE VARCHAR(max)=''
      DECLARE @CREATELOGTBL VARCHAR(max)=''
      DECLARE @TRIGGERCREATE VARCHAR(max)=''
      DECLARE @STR VARCHAR(max)=''

      SELECT @STR = @STR + ', ' ++ '[' + column_name + ']'
      FROM   information_schema.COLUMNS
      WHERE  table_name = @TableName

      DECLARE @DropTRG VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
        + ''') is not null' + Char(10) + 'begin' + Char(10)
        + 'drop trigger trg' + @tableName + ';' + Char(10)
        + 'end'

      SET @DROPTrigger= @DropTRG

      --EXEC(@DROPTrigger)
      DECLARE @CREATE VARCHAR(100)='CREATE TABLE trg' + @tableName + '_History('
      DECLARE @Drop VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
        + '_History'') is not null' + Char(10) + 'begin'
        + Char(10) + 'drop table trg' + @tableName
        + '_History' + Char(10) + 'end'

      SET @DROPTABLE= @drop

      --EXEC(@DROPTABLE)
      DECLARE @STRCRE VARCHAR(max)=''

      SELECT @STRCRE = @STRCRE + ', ' + CASE WHEN character_maximum_length IS
                       NULL
                       THEN '['+
                              column_name
                              +']'+' '+data_type ELSE '['+column_name+']'+' '+
                       data_type+'('+
                              CONVERT(VARCHAR(10), Replace(
                       character_maximum_length,
                       '-1',
                              'MAX'))+')' END
      FROM   information_schema.COLUMNS
      WHERE  table_name = @TableName

      SET @CREATELOGTBL= @CREATE + ' '
                         + RIGHT(@STRCRE, Len(@STRCRE)-1)
                         + ',Actions varchar(20),CreatedDate1 dateTime)'
                         + Char(10)
      --EXEC (@CREATELOGTBL)
      SET @TRIGGERCREATE= 'create trigger trg' + @tableName + ' on ' + '['
                          + @tableName + ']' + Char(10)
                          + ' for insert,update , delete ' + Char(10)
                          + 'AS ' + Char(10) + 'BEGIN' + Char(10)
                          + 'IF EXISTS(SELECT * FROM inserted)'
                          + Char(10) + 'BEGIN' + Char(10) + 'INSERT INTO trg'
                          + @tableName + '_History' + '('
                          + RIGHT(@STR, Len(@str)-1)
                          + ',Actions, CreatedDate1' + ')'
                          + ' SELECT *,''INSERTED'',getdate() FROM inserted'
                          + Char(10) + 'END' + Char(10)
                          + 'IF EXISTS(SELECT * FROM deleted)'
                          + Char(10) + 'BEGIN' + Char(10) + 'INSERT INTO trg'
                          + @tableName + '_History' + '('
                          + RIGHT(@STR, Len(@str)-1)
                          + ',Actions, CreatedDate1' + ')'
                          + ' SELECT *,''DELETED'',getdate() FROM deleted'
                          + Char(10) + 'END' + Char(10) + 'END'

      EXEC(@DROPTrigger)

      EXEC(@DROPTABLE)

      EXEC (@CREATELOGTBL)

      EXEC (@TRIGGERCREATE)

      FETCH next FROM cur INTO @TableName
  END

CLOSE cur

DEALLOCATE cur

SET nocount OFF

end