Thursday, March 31, 2011

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.

No comments:

Post a Comment