· 6 years ago · Jun 29, 2019, 04:26 PM
1DECLARE @insertTriggers VARCHAR(MAX) = '';
2SELECT @insertTriggers = @insertTriggers1 + *Some Trigger creation code ending with 'GO'"
3FROM INFORMATION_SCHEMA.TABLES
4EXEC(@insertTriggers);
5
6if exists(select 1 from sysobjects where name = 'kv_sp_dbAudits_tr_AW')
7drop procedure kv_sp_dbAudits_tr_AW
8go
9
10set ansi_nulls on
11go
12
13set quoted_identifier on
14go
15
16create procedure kv_sp_dbAudits_tr_AW(@tablename varchar(150),@switch bit)
17as
18
19BEGIN
20
21if exists(select name from sysobjects where name = 'TempTableTriggers')
22drop table TempTableTriggers;
23
24create table TempTableTriggers (tablename varchar(100),columns varchar(max),columnvars varchar(max),columnvarst varchar(max),columnalloc varchar(max))
25
26declare @query varchar(max)
27, @loop int
28, @dbname varchar(50) = (select db_name())+'_Audit'
29, @table varchar(100)
30, @tableau varchar(100)
31, @trigger varchar(100)
32, @column varchar(max)
33, @columnvars varchar(max)
34, @columnvarst varchar(max)
35, @columnalloc varchar(max)
36, @crlf char(2) = char(13)+char(10)
37
38 declare @Tables table
39 ( id int identity primary key
40 , TableName varchar(100)
41 , TriggerName varchar(100)
42 , Columns varchar(max))
43
44 if @tablename like ''+'%'+''
45 set @tablename = @tablename
46 else
47 set @tablename = ''+@tablename+''
48
49 set @trigger = @tablename+'_Audit_kvtr_AW'
50
51 begin
52 if @switch = 1
53 begin
54 if exists(select name from sysobjects where name = @trigger)
55 begin
56 set @tablename = (select name from sysobjects where name = @tablename)
57 set @query = @crlf
58 set @query = @query + @crlf
59 set @query = @query + @crlf
60 set @query = @query + @crlf
61 set @query = @query + replicate('-',80)+@crlf
62 set @query = @query + @crlf
63 set @query = @query + 'The audit trigger "'+@tablename+'_Audit_kvtr_AW'+'" already exists!'
64 print (@query)
65 end
66 else
67 begin
68 if @tablename = 'all'
69
70 insert into @Tables
71 ( TableName )
72 select
73 name
74 from sysobjects
75 where xtype = 'u'
76 and name not like '%audit%'
77 and name <> 'TempTableTriggers'
78 order by name
79
80 else
81
82 insert into @Tables
83 ( TableName )
84 select
85 name
86 from sysobjects
87 where xtype = 'u'
88 and name not like '%audit%'
89 and name <> 'TempTableTriggers'
90 and name = @tablename
91 order by name
92
93 select @loop = min(id) from @Tables
94 while @loop is not null
95
96 begin
97 begin
98 set @query = ' declare @columns varchar(max)
99
100 select @columns = stuff((select '','' + char(10)+quotename(Column_Name)
101 from information_schema.columns
102 where table_name = '''+(select TableName from @Tables where id = @loop)+'''
103 and Column_Name <> ''cAllocs''
104 and data_type not in (''text'',''image'',''ntext'')
105 group by column_name, data_type, character_maximum_length, ordinal_position
106 order by ordinal_position
107 for xml path(''''), type).value(''.'', ''nvarchar(max)''),1,1,'''')
108
109 insert into TempTableTriggers (tablename,columns)
110 select distinct
111 table_name
112 , @columns
113 from information_schema.columns
114 where table_name = '''+(select TableName from @Tables where id = @loop)+''''
115 exec (@query)
116 end
117 select @loop = min(id) from @Tables where id>@loop
118 end
119
120 insert into @Tables
121 (TriggerName,TableName,Columns)
122 select
123 TableName,TableName,columns
124 from TempTableTriggers
125
126 select @loop = min(id) from @Tables
127 while @loop is not null
128
129 begin
130 begin
131 select @trigger = TriggerName+'_Audit_kvtr_AW'
132 , @tableau = TableName+'_Audit'
133 , @table = TableName
134 , @column = Columns
135 from @Tables
136 where id = @loop
137
138 set @query = 'create trigger '+@trigger+' on '+@table+@crlf
139 set @query = @query+ 'with encryption'+@crlf
140 set @query = @query+ 'after insert, update, delete'+@crlf
141 set @query = @query+ 'as'+@crlf
142 set @query = @query+ '/***********************************************************************************************************************************'+@crlf
143 set @query = @query+ 'Description : To insert any change made in '+@table+' into '+@dbname+'.dbo.'+@table+'_Audit'+@crlf
144 set @query = @query+ 'Author : Attie Wagner'+@crlf
145 set @query = @query+ 'Creation Date : 30 October 2018'+@crlf
146 set @query = @query+ 'Modified By : Attie Wagner'+@crlf
147 set @query = @query+ 'Modified Date : 28 January 2019'+@crlf
148 set @query = @query+ '************************************************************************************************************************************/'+@crlf
149 set @query = @query+ @crlf
150 set @query = @query+ 'begin'+@crlf
151 set @query = @query+ @crlf
152 set @query = @query+ 'set nocount on'+@crlf
153 set @query = @query+ @crlf
154
155 set @query = @query+ 'if (select trigger_nestlevel(object_id('''+@trigger+'''))) > 1'+@crlf
156 set @query = @query+ 'return'+@crlf
157 set @query = @query+ @crlf
158
159 set @query = @query+ 'declare @inserted varchar(15) = '''''+@crlf
160 set @query = @query+ 'declare @deleted varchar(15) = '''''+@crlf
161 set @query = @query+ 'declare @updated varchar(15) = '''''+@crlf
162 set @query = @query+ 'declare @action varchar(15) = '''''+@crlf
163 set @query = @query+ 'if((exists(select * from inserted)) and (exists(select * from deleted)))'+@crlf
164 set @query = @query+ ' set @updated = ''updated'''+@crlf
165 set @query = @query+ 'else'+@crlf
166 set @query = @query+ 'if(exists(select * from inserted))'+@crlf
167 set @query = @query+ ' set @inserted = ''new'''+@crlf
168 set @query = @query+ 'else'+@crlf
169 set @query = @query+ 'if(exists(select * from deleted))'+@crlf
170 set @query = @query+ ' set @deleted = ''deleted'''+@crlf
171 set @query = @query+ 'set @action = (select case when @inserted = ''new'' then ''new'' when @updated = ''updated'' then ''updated'' when @deleted = ''deleted'' then ''deleted'' else '''' end)'+@crlf
172 set @query = @query+ @crlf
173
174 set @query = @query+ 'declare @kvAgent varchar(100) = (select cAgentName from _rtblAgents where idAgents = [dbo]._efnAgentIDFromConnection(''dbo''))'+@crlf
175 set @query = @query+ @crlf
176
177 set @query = @query+ 'if @action in (''new'',''updated'')'+@crlf
178 set @query = @query+ @crlf
179
180 set @query = @query+ 'begin'+@crlf
181 set @query = @query+ 'insert into ['+@dbname+'].dbo.'+@tableau+' ('+@column+','+@crlf+'kvUsername,'+@crlf+'kvDTStamp,'+@crlf+'kvAction)'+@crlf
182 set @query = @query+ @crlf
183 set @query = @query+ 'select '+@column+','+@crlf+'@kvAgent,'+@crlf+'getdate(),'+@crlf+'@action'+@crlf
184 set @query = @query+ 'from inserted'+@crlf
185 set @query = @query+ 'end;'+@crlf
186 set @query = @query+ @crlf
187
188 set @query = @query+ 'if @action = ''deleted'''+@crlf
189 set @query = @query+ @crlf
190
191 set @query = @query+ 'begin'+@crlf
192 set @query = @query+ 'insert into ['+@dbname+'].dbo.'+@tableau+' ('+@column+','+@crlf+'kvUsername,'+@crlf+'kvDTStamp,'+@crlf+'kvAction)'+@crlf
193 set @query = @query+ @crlf
194 set @query = @query+ 'select '+@column+','+@crlf+'@kvAgent,'+@crlf+'getdate(),'+@crlf+'@action'+@crlf
195 set @query = @query+ 'from deleted'+@crlf
196 set @query = @query+ 'end;'+@crlf
197 set @query = @query+ @crlf
198
199 set @query = @query+ 'begin'+@crlf
200 set @query = @query+ 'delete '+quotename(@dbname)+'.dbo.'+@table+'_Audit'+@crlf
201 set @query = @query+ 'where datediff(month,kvDTStamp,getdate()) > 12'+@crlf
202 set @query = @query+ 'end;'+@crlf
203 set @query = @query+ @crlf
204 set @query = @query+ 'end;'+@crlf
205
206 exec (@query)
207 end
208 select @loop = min(id) from @Tables where id>@loop
209 end
210 end
211
212 drop table TempTableTriggers;
213 end
214 else
215 begin
216 if @tablename = 'all'
217
218 insert into @Tables
219 ( TriggerName )
220 select name from sysobjects
221 where xtype = 'tr'
222 and name like '%_Audit_kvtr_AW'
223 order by name
224
225 else
226
227 insert into @Tables
228 ( TriggerName )
229 select name from sysobjects
230 where xtype = 'tr'
231 and name like '%_Audit_kvtr_AW'
232 and name = @tablename+'_Audit_kvtr_AW'
233 order by name
234
235 select @loop = min(id) from @Tables
236 while @loop is not null
237
238 begin
239 begin
240 set @trigger = (select TriggerName from @Tables where id = @loop)
241 set @query = 'if exists(select name from sysobjects where name = '''+@trigger+''')'+@crlf
242 set @query = @query+'drop trigger '+@trigger
243 exec (@query)
244 end
245 select @loop = min(id) from @Tables where id>@loop
246 end
247 end
248 end
249END;