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
