· 10 years ago · Aug 31, 2016, 04:54 PM
1/*===================================================================
2
3 Crea triggers per a totes les taules d'una mateixa DB
4
5 Guarda qualsevol canvi (Insert, Update, Delete) a una única taula
6
7 S'ha de tornar a executar si s'han creat taules noves
8
9===================================================================*/
10
11use nom_basedades
12
13-- Creo taula per guardar l'històric de canvis (de totes les taules)
14if not exists (select * from information_schema.tables where table_name='Historial')
15create table Historial (
16 Historial_ID int IDENTITY(1,1) NOT NULL,
17 Taula varchar(128),
18 Tipus char(3),
19 Columna_PK varchar(1000),
20 Valor_PK varchar(1000),
21 Nom_columna varchar(128),
22 Valor_antic varchar(1000),
23 Valor_nou varchar(1000),
24 Data_canvi datetime default (getdate()),
25 Usuari varchar(128)
26)
27
28-- Variables per guardar el text del Trigger i el nom de la Taula
29declare @sql varchar(8000), @table_name sysname
30
31select @table_name = min(table_name) from information_schema.tables where table_type = 'BASE TABLE' and table_name!= 'sysdiagrams'
32and table_name!= 'Historial'
33
34-- Bucle per recorrer totes les taules
35while @table_name is not null
36 begin
37 -- Si existeix un trigger antic, l'esborro
38 EXEC('IF OBJECT_ID (''' + @table_name+ '_Historial'', ''TR'') IS NOT NULL DROP TRIGGER ' + @table_name+ '_Historial')
39
40 -- Preparo el contingut del trigger
41 SELECT @sql =
42 '
43 create trigger ' + @table_name+ '_Historial on ' + @table_name+ ' for insert, update, delete
44 as
45 declare @bit int ,
46 @field int ,
47 @maxfield int ,
48 @char int ,
49 @Nom_columna varchar(128) ,
50 @Taula varchar(128) ,
51 @PKCols varchar(1000) ,
52 @sql varchar(2000),
53 @Data_canvi varchar(21) ,
54 @Usuari varchar(128) ,
55 @Tipus char(3) ,
56 @PKFieldSelect varchar(1000),
57 @PKValueSelect varchar(1000)
58 select @Taula = ''' + @table_name+ '''
59 select @Usuari = system_user,
60 @Data_canvi = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
61 if exists (select * from inserted)
62 if exists (select * from deleted)
63 select @Tipus = ''Upd''
64 else
65 select @Tipus = ''Ins''
66 else
67 select @Tipus = ''Del''
68 select * into #ins from inserted
69 select * into #del from deleted
70 select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
71 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
72 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
73 where pk.table_name = @Taula
74 and CONSTRAINT_TYPE = ''PRIMARY KEY''
75 and c.table_name = pk.table_name
76 and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
77 select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
78 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
79 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
80 where pk.table_name = @Taula
81 and CONSTRAINT_TYPE = ''PRIMARY KEY''
82 and c.table_name = pk.table_name
83 and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
84 select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
85 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
86 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
87 where pk.table_name = @Taula
88 and CONSTRAINT_TYPE = ''PRIMARY KEY''
89 and c.table_name = pk.table_name
90 and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
91 if @PKCols is null
92 begin
93 raiserror(''No existeix PK a la taula %s'', 16, -1, @Taula)
94 return
95 end
96 select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where table_name = @Taula
97 while @field < @maxfield
98 begin
99 select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where table_name = @Taula and ORDINAL_POSITION > @field
100 select @bit = (@field - 1 )% 8 + 1
101 select @bit = power(2,@bit - 1)
102 select @char = ((@field - 1) / 8) + 1
103 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Tipus in (''Ins'',''Del'')
104 begin
105 select @Nom_columna = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table_name = @Taula and ORDINAL_POSITION = @field
106 select @sql = ''insert Historial (Tipus, Taula, Columna_PK, Valor_PK, Nom_columna, Valor_antic, Valor_nou, Data_canvi, Usuari)''
107 select @sql = @sql + '' select '''''' + @Tipus + ''''''''
108 select @sql = @sql + '','''''' + @Taula + ''''''''
109 select @sql = @sql + '','' + @PKFieldSelect
110 select @sql = @sql + '','' + @PKValueSelect
111 select @sql = @sql + '','''''' + @Nom_columna + ''''''''
112 select @sql = @sql + '',convert(varchar(1000),d.'' + @Nom_columna + '')''
113 select @sql = @sql + '',convert(varchar(1000),i.'' + @Nom_columna + '')''
114 select @sql = @sql + '','''''' + @Data_canvi + ''''''''
115 select @sql = @sql + '','''''' + @Usuari + ''''''''
116 select @sql = @sql + '' from #ins i full outer join #del d''
117 select @sql = @sql + @PKCols
118 select @sql = @sql + '' where i.'' + @Nom_columna + '' <> d.'' + @Nom_columna
119 select @sql = @sql + '' or (i.'' + @Nom_columna + '' is null and d.'' + @Nom_columna + '' is not null)''
120 select @sql = @sql + '' or (i.'' + @Nom_columna + '' is not null and d.'' + @Nom_columna + '' is null)''
121 exec (@sql)
122 end
123 end
124 '
125 select @sql
126
127 -- Creo el trigger
128 exec(@sql)
129 select @table_name = min(table_name) from information_schema.tables where table_name > @table_name and table_type= 'BASE TABLE' and table_name!= 'sysdiagrams' and table_name!= 'Historial'
130 end