Adding Trigger to all database tables

The following T-SQL view and stored procedure along with the script add (or modifying existing) 'FOR INSERT' triggers to all database tables:

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

PRINT 'drop view dbo.udvTriggersDefinitions'
GO
if exists (select * from dbo.sysobjects
where id = object_id(N'dbo.udvTriggersDefinitions')
and OBJECTPROPERTY(id, N'IsView') = 1)

drop view dbo.udvTriggersDefinitions
GO

PRINT 'CREATE VIEW dbo.udvTriggersDefinitions'
GO

/******************************************************************************
NAME: dbo.udvTriggersDefinitions

PURPOSE:

REVISIONS:

$Log$
Ver Date Author Description
******************************************************************************/
create view dbo.udvTriggersDefinitions
as

 SELECT TOP 100 PERCENT T.name AS TableName
   ,COL_NAME(T.object_id,1) pkID
   ,0 fuCounter
   ,NULL definition
   ,NULL bgnText
   ,NULL endText
 FROM (SELECT *
   FROM sys.objects SOTR
   WHERE type_desc LIKE 'SQL_TRIGGER') AS TR
   RIGHT OUTER JOIN
    (SELECT *
    FROM sys.objects AS SOT
    WHERE (type_desc LIKE 'USER_TABLE')) AS T
    ON TR.parent_object_id = T.object_id
   LEFT OUTER JOIN
   (SELECT object_id, definition
    FROM sys.sql_modules
   ) AS TS
   ON TR.object_id = TS.object_id
   WHERE TR.name IS NULL
   AND T.name NOT IN('dtproperties','sysdiagrams')
   UNION
   SELECT TOP 100 PERCENT TableName
    ,COL_NAME(object_id,1) pkID
    ,SUM(Flag) fuCounter
    ,NULL definition
    ,NULL bgnText
    ,NULL endText
   FROM(SELECT T.name AS TableName
    , TS.definition
    ,(CASE WHEN TS.definition Like '%FOR UPDATE%' THEN 1
   ELSE 0 END) Flag
    ,T.object_id
   FROM (SELECT *
     FROM sys.objects SOTR
     WHERE type_desc LIKE 'SQL_TRIGGER') AS TR
   RIGHT OUTER JOIN (SELECT *
        FROM sys.objects AS SOT
        WHERE (type_desc LIKE 'USER_TABLE')) AS T
   ON TR.parent_object_id = T.object_id
   LEFT OUTER JOIN (SELECT object_id, definition
        FROM sys.sql_modules
       ) AS TS
   ON TR.object_id = TS.object_id
   WHERE TS.definition IS NOT NULL) T
   GROUP BY TableName
     ,object_id
   HAVING SUM(Flag) = 0
   UNION SELECT TOP 100 PERCENT T.name AS TableName
     ,COL_NAME(T.object_id,1) pkID
     ,1 fuCounter
     ,TS.definition
     ,REPLACE(SUBSTRING(TS.definition,1,LEN(TS.definition) - CHARINDEX('DNE', REVERSE(TS.definition)) + 1 - 3),'''','''''') bgnText
     ,SUBSTRING(TS.definition,LEN(TS.definition) - CHARINDEX('DNE', REVERSE(TS.definition)) + 1 + 3,LEN(TS.definition)) endText
     FROM (SELECT *
       FROM sys.objects SOTR
       WHERE type_desc LIKE 'SQL_TRIGGER') AS TR
     RIGHT OUTER JOIN (SELECT *
          FROM sys.objects AS SOT
     WHERE (type_desc LIKE 'USER_TABLE')) AS T
     ON TR.parent_object_id = T.object_id
   LEFT OUTER JOIN (SELECT object_id, definition
        FROM sys.sql_modules
       ) AS TS
     ON TR.object_id = TS.object_id
   WHERE TS.definition Like '%FOR UPDATE%'
   ORDER BY TableName
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

PRINT 'drop procedure dbo.udpUpdateAuditingFields'
GO

if exists (select * from dbo.sysobjects
where id = object_id(N'dbo.udpUpdateAuditingFields')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure dbo.udpUpdateAuditingFields
GO

PRINT 'CREATE PROC dbo.udpUpdateAuditingFields'

GO
/******************************************************************************
NAME: udpUpdateAuditingFields

PURPOSE:

REVISIONS:

$Log$

Ver Date Author Description
exec dbo.udpUpdateAuditingFields 'dbo.tblClients' --@TableName
 ,'test'  --@UserName
 ,'ClientID' --@ID_name
 ,5987   --@ID_value
*****************************************************************************/
CREATE PROC dbo.udpUpdateAuditingFields
 @TableName NVARCHAR(255)
 ,@UserName NVARCHAR(255)
 ,@ID_name NVARCHAR(255)
 ,@ID_value INT
AS
DECLARE  @Current_Date DATETIME
  ,@strSQL NVARCHAR(MAX)

BEGIN
 SET @Current_Date = getdate()
 SET @strSQL = 'UPDATE ' + @TableName
 SET @strSQL = @strSQL + ' SET ModifiedBy = ''' + @UserName
 SET @strSQL = @strSQL + ' '',ModifiedDate = ''' + CONVERT(NVARCHAR(30),@Current_Date)
 SET @strSQL = @strSQL + ''' WHERE '
 SET @strSQL = @strSQL + @ID_name + ' = ' + CONVERT(NVARCHAR(30),@ID_value)
 PRINT @strSQL

 EXEC(@strSQL)
 END
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

DECLARE @schema nvarchar(255)
  ,@pkID nvarchar(255)
  ,@table_name nvarchar(255)
  ,@command NVARCHAR(4000)
  ,@definition NVARCHAR(4000)
  ,@fuCounter INT
  ,@bgnText nvarchar(3000);

 DECLARE crs CURSOR FOR
  SELECT   TableName
    ,pkID
    ,fuCounter
    ,[definition]
    ,bgnText
  FROM dbo.udvTriggersDefinitions

  OPEN crs
  FETCH NEXT FROM crs
  INTO @table_name
   ,@pkID
   ,@fuCounter
   ,@definition
   ,@bgnText;

  SET @Command = N'SET QUOTED_IDENTIFIER ON;'
  SET @Command = @Command + N' SET ANSI_NULLS ON;'
  SET @Command = @Command + N' DECLARE @strSQL NVARCHAR(4000)'
  SET @Command = @Command + N' ,@trgName NVARCHAR(255);'
  SET @Command = @Command + N' SET @trgName = ''dbo.T_' + @table_name + '_UTrig'';'
  SET @Command = @Command + N' PRINT ''DROP TRIGGER '' + @trgName;'
  SET @Command = @Command + N' IF OBJECT_ID (@trgName,''TR'') IS NOT NULL'
  SET @Command = @Command + N' BEGIN'
  SET @Command = @Command + N' SET @strSQL = ''DROP TRIGGER '' + @trgName;'
  SET @Command = @Command + N' EXEC(@strSQL);'
  SET @Command = @Command + N' END;'
  SET @Command = @Command + N' PRINT ''CREATE TRIGGER '' + @trgName;'
  SET @Command = @Command + N' /******************************************************************************'
  SET @Command = @Command + N' NAME: @trgName'
  SET @Command = @Command + N' PURPOSE: '
  SET @Command = @Command + N''
  SET @Command = @Command + N' REVISIONS:'
  SET @Command = @Command + N''
  SET @Command = @Command + N' $Log$'
  SET @Command = @Command + N''
  SET @Command = @Command + N' Ver Date Author Description'
  SET @Command = @Command + N' --------- ---------- --------------- ------------------------------------'
  SET @Command = @Command + N' ******************************************************************************/'

 IF @fuCounter = 0 BEGIN
   SET @Command = @Command + N' SET @strSQL = ''CREATE TRIGGER '' + @trgName'
   SET @Command = @Command + N' + '' ON '' + ''' + @table_name + ''
   SET @Command = @Command + N' FOR UPDATE'
   SET @Command = @Command + N' AS'
   SET @Command = @Command + N' BEGIN'
   SET @Command = @Command + N' DECLARE @ID_value INT'
   SET @Command = @Command + N' ,@USR NVARCHAR(255);'
   SET @Command = @Command + N' SELECT @ID_value = ' + @pkID + ' FROM Inserted;'
   SET @Command = @Command + N' EXEC dbo.udpUpdateAuditingFields ''''' +@table_name + ''''''
   SET @Command = @Command + N', @USR'
   SET @Command = @Command + N', ''''' + @pkID + ''''''
   SET @Command = @Command + N', @ID_value;'
   SET @Command = @Command + N' END'''
   SET @Command = @Command + N' EXEC(@strSQL);'
   END ELSE BEGIN
   SET @Command = @Command + N' SET @strSQL = ''' + @bgnText;
   SET @Command = @Command + N' BEGIN';
   SET @Command = @Command + N' DECLARE @ID_value INT'
   SET @Command = @Command + N' ,@USR NVARCHAR(255);'
   SET @Command = @Command + N' SELECT @ID_value = ' + @pkID + ' FROM Inserted;'
   SET @Command = @Command + N' EXEC dbo.udpUpdateAuditingFields ''''' +@table_name + ''''''
   SET @Command = @Command + N', @USR'
   SET @Command = @Command + N', ''''' + @pkID + ''''''
   SET @Command = @Command + N', @ID_value;'
   SET @Command = @Command + N' END; END'''
   SET @Command = @Command + N' EXEC(@strSQL);'
  END;

  SET @Command = @Command + N' SET QUOTED_IDENTIFIER OFF;'
  SET @Command = @Command + N' SET ANSI_NULLS ON;'
  WHILE @@FETCH_STATUS = 0
  BEGIN
  PRINT @Command;
  PRINT ''

  EXEC sp_executesql @Command;

  PRINT ''
  FETCH NEXT FROM crs
  INTO @table_name
   ,@pkID
   ,@fuCounter
   ,@definition
   ,@bgnText;

  SET @Command = N'SET QUOTED_IDENTIFIER ON;'
  SET @Command = @Command + N' SET ANSI_NULLS ON;'
  SET @Command = @Command + N' DECLARE @strSQL NVARCHAR(4000)'
  SET @Command = @Command + N' ,@trgName NVARCHAR(255);'
  SET @Command = @Command + N' SET @trgName = ''dbo.T_' + @table_name + '_UTrig'';'
  SET @Command = @Command + N' PRINT ''DROP TRIGGER '' + @trgName;'
  SET @Command = @Command + N' IF OBJECT_ID (@trgName,''TR'') IS NOT NULL'
  SET @Command = @Command + N' BEGIN'
  SET @Command = @Command + N' SET @strSQL = ''DROP TRIGGER '' + @trgName;'
  SET @Command = @Command + N' EXEC(@strSQL);'
  SET @Command = @Command + N' END;'
  SET @Command = @Command + N' PRINT ''CREATE TRIGGER '' + @trgName;'
  SET @Command = @Command + N' /******************************************************************************'
  SET @Command = @Command + N' NAME: @trgName'
  SET @Command = @Command + N' PURPOSE: '
  SET @Command = @Command + N''
  SET @Command = @Command + N' REVISIONS:'
  SET @Command = @Command + N''
  SET @Command = @Command + N' $Log$'
  SET @Command = @Command + N''
  SET @Command = @Command + N' Ver Date Author Description'
  SET @Command = @Command + N' --------- ---------- --------------- ------------------------------------'
  SET @Command = @Command + N' ******************************************************************************/'

  IF @fuCounter = 0 BEGIN
   SET @Command = @Command + N' SET @strSQL = ''CREATE TRIGGER '' + @trgName'
   SET @Command = @Command + N' + '' ON '' + ''' + @table_name + ''
   SET @Command = @Command + N' FOR UPDATE'
   SET @Command = @Command + N' AS'
   SET @Command = @Command + N' BEGIN'
   SET @Command = @Command + N' DECLARE @ID_value INT'
   SET @Command = @Command + N' ,@USR NVARCHAR(255);'
   SET @Command = @Command + N' SELECT @ID_value = ' + @pkID + ' FROM Inserted;'
   SET @Command = @Command + N' EXEC dbo.udpUpdateAuditingFields ''''' +@table_name + ''''''
   SET @Command = @Command + N', @USR'
   SET @Command = @Command + N', ''''' + @pkID + ''''''
   SET @Command = @Command + N', @ID_value;'
   SET @Command = @Command + N' END'''
   SET @Command = @Command + N' EXEC(@strSQL);'
   END ELSE BEGIN
   SET @Command = @Command + N' SET @strSQL = ''' + @bgnText;
   SET @Command = @Command + N' BEGIN';
   SET @Command = @Command + N' DECLARE @ID_value INT'
   SET @Command = @Command + N' ,@USR NVARCHAR(255);'
   SET @Command = @Command + N' SELECT @ID_value = ' + @pkID + ' FROM Inserted;'
   SET @Command = @Command + N' EXEC dbo.udpUpdateAuditingFields ''''' +@table_name + ''''''
   SET @Command = @Command + N', @USR'
   SET @Command = @Command + N', ''''' + @pkID + ''''''
   SET @Command = @Command + N', @ID_value;'
   SET @Command = @Command + N' END; END'''
   SET @Command = @Command + N' EXEC(@strSQL);'
  END;

  SET @Command = @Command + N' SET QUOTED_IDENTIFIER OFF;'
  SET @Command = @Command + N' SET ANSI_NULLS ON;'
 END;

 CLOSE crs;
 DEALLOCATE crs;

If you find this script useful please donate generously.