1J41edyZ

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