JPybpH5U

1USE MYAWESOMEDATABASE
2GO
3
4IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
5CREATE TABLE Audit
6(
7AuditID [int]IDENTITY(1,1) NOT NULL,
8Type char(1), 
9TableName varchar(128), 
10PrimaryKeyField varchar(1000), 
11PrimaryKeyValue varchar(1000), 
12FieldName varchar(128), 
13OldValue varchar(1000), 
14NewValue varchar(1000), 
15UpdateDate datetime DEFAULT (GetDate()), 
16UserNamevarchar(128)
17)
18GO
19
20DECLARE @sql varchar(8000), @TABLE_NAMEsysname
21SET NOCOUNT ON
22
23SELECT @TABLE_NAME= MIN(TABLE_NAME) 
24FROM INFORMATION_SCHEMA.Tables 
25WHERE 
26TABLE_TYPE= 'BASE TABLE' 
27AND TABLE_NAME!= 'sysdiagrams'
28AND TABLE_NAME!= 'Audit'
29
30WHILE @TABLE_NAMEIS NOT NULL
31 BEGIN
32EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
33SELECT @sql = 
34'
35create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
36as
37
38declare @bit int ,
39@field int ,
40@maxfield int ,
41@char int ,
42@fieldname varchar(128) ,
43@TableName varchar(128) ,
44@PKCols varchar(1000) ,
45@sql varchar(2000), 
46@UpdateDate varchar(21) ,
47@UserName varchar(128) ,
48@Type char(1) ,
49@PKFieldSelect varchar(1000),
50@PKValueSelect varchar(1000)
51
52select @TableName = ''' + @TABLE_NAME+ '''
53
54-- date and user
55select @UserName = system_user ,
56@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
57
58-- Action
59if exists (select * from inserted)
60if exists (select * from deleted)
61select @Type = ''U''
62else
63select @Type = ''I''
64else
65select @Type = ''D''
66
67-- get list of columns
68select * into #ins from inserted
69select * into #del from deleted
70
71-- Get primary key columns for full outer join
72select@PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
73fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
74INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
75where pk.TABLE_NAME = @TableName
76andCONSTRAINT_TYPE = ''PRIMARY KEY''
77andc.TABLE_NAME = pk.TABLE_NAME
78andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
79
80-- Get primary key fields select for insert
81select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '''''''' 
82fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
83INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
84where pk.TABLE_NAME = @TableName
85andCONSTRAINT_TYPE = ''PRIMARY KEY''
86andc.TABLE_NAME = pk.TABLE_NAME
87andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
88
89select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
90from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    
91INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
92where  pk.TABLE_NAME = @TableName   
93and CONSTRAINT_TYPE = ''PRIMARY KEY''   
94and c.TABLE_NAME = pk.TABLE_NAME   
95and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
96
97if @PKCols is null
98begin
99raiserror(''no PK on table %s'', 16, -1, @TableName)
100return
101end
102
103select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
104while @field < @maxfield
105begin
106select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
107select @bit = (@field - 1 )% 8 + 1
108select @bit = power(2,@bit - 1)
109select @char = ((@field - 1) / 8) + 1
110if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
111begin
112select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
113select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
114select @sql = @sql + '' select '''''' + @Type + ''''''''
115select @sql = @sql + '','''''' + @TableName + ''''''''
116select @sql = @sql + '','' + @PKFieldSelect
117select @sql = @sql + '','' + @PKValueSelect
118select @sql = @sql + '','''''' + @fieldname + ''''''''
119select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
120select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
121select @sql = @sql + '','''''' + @UpdateDate + ''''''''
122select @sql = @sql + '','''''' + @UserName + ''''''''
123select @sql = @sql + '' from #ins i full outer join #del d''
124select @sql = @sql + @PKCols
125select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname 
126select @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' + @fieldname + '' is not null)'' 
127select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' + @fieldname + '' is null)'' 
128exec (@sql)
129end
130end
131'
132SELECT @sql
133EXEC(@sql)
134SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables 
135WHERE TABLE_NAME> @TABLE_NAME
136AND TABLE_TYPE= 'BASE TABLE' 
137AND TABLE_NAME!= 'sysdiagrams'
138AND TABLE_NAME!= 'Audit'
139END