Trigger Ensembliste de Alter table pour historisation. [Débutant(e)] [Résolu]

[obarator]

Bonjour,
J'ai a pour ma culture perso un peu réfléchis à l’excellent article de SQLpro sur l'historisation de données au passage merci pour le partage .
Mon problème réside dans l'implémentation de la partie du trigger qui permettre de modifier des colonnes d'une table d'historisations pour suivre les alter(nom, type...) de la table de production d'un point de vue ensembliste. Parce comme fourni on a les colonnes impacter par la modification leur nature ainsi que les différentes manipulations qu'elle on subit (ouf !). Je vois comment exploiter les informations par curseur ou avec une table temporaire (comme le présente encore SQLpro ici point 3). Mais avec la procédure sp_rename ou par colonne temporaire (ici et oui encore merci) sa ressemble plus à du fonctionnel (boucle sur les colonnes...).
Mais auriez-vous des pistes de réflexions pour respecter le point de vu ensembliste dans ce cas ? Des exemples de mécanismes similaires ?

[SQLpro]

Il suffit de capturer les commandes SQL ALTER, changer le nom de la table et les relancer pour la nouvelle table.

A +

[obarator]

Hum ok merci.
Mais en terme de "capturer les commandes SQL ALTER" je dois chercher du coter du contenu du EVENTDATA() (je connais pas trop cette bête là!)
Ou plutôt par comparaison de tables je recrée les modifications ?

[elsuket]

Bonjour,

Je ne suis pas sûr d'avoir complètement compris ce que vous cherchez à faire :
- simplement suivre les instructions ALTER TABLE
- capturer l'instruction ALTER TABLE pour l'exécuter sur la table d'audit des modifications
- les deux ?

Quoi qu'il en soit, voici un exemple d'utilisation de EVENTDATA().
Voici une table de test :

Code :

1
2
3
4
5
CREATE TABLE test
(
	i int
)
GO

Et le trigger d'audit au niveau base de données

Code :

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER DBTR_ALTER_TABLE
	ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @event_data xml = EVENTDATA()
		, @tsql_statement nvarchar(max)
 
	SELECT	@tsql_statement = @event_data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
END

Tout ALTER TABLE est alors capturé par le trigger de base de données, et exposé par la fonction EVENTDATA() qui retourne le document suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2011-10-25T22:51:48.990</PostTime>
  <SPID>52</SPID>
  <ServerName>ELSUKET-PC</ServerName>
  <LoginName>ElSuket-PC\ElSuket</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>ELSUKET</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>test</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>j</Name>
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE test
ADD j int</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

Vous pouvez vous servir des divers éléments de ce document pour répondre à votre besoin.
Vous aurez éventuellement besoin de la méthode XQuery nodes()

Notez que si vous êtes sous SQL Server 2008 Enterprise ou Developer, vous pouez utiliser la fonctionnalité Change Data Capture, plus simple à mettre en place et plus légère

@++; )

[obarator]

merci pour les infos.

Je m'explique en fait j'essaie de faire ce qui présenter dans cet article.
A savoir:

1 A l'ajout d'une table de prod je crée une table d'historisation dans une deuxième base par trigger:

Concrètement:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
USE [DB_PRO]
GO
 
/****** Object:  DdlTrigger [E_DB_CRETAB]    Script Date: 10/26/2011 10:08:12 ******/
 
/********************************************************************************************  
* Trigger de creation de table historisation                                                 * 
**********************************************************************************************  
* CF :Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com -                       * 
*http://www.sqlspot.com/sites/sqlspot.com/IMG/pdf/Historisation_des_donnees_en_mode_ligne.pdf*
**********************************************************************************************  
* Ce trigger crée un double de la table crée dans la base historisation                      *  
* avec les champs de suivi des modifs                                                        *
*********************************************************************************************/
 
CREATE TRIGGER [E_DB_CRETAB] 
ON DATABASE  
FOR CREATE_TABLE 
AS 
BEGIN 
SET NOCOUNT ON;
-- récupération des informations du "paquet" d'événement du tgrigger DDL 
DECLARE @XML XML, @SCH sysname, @TAB sysname; 
SET @XML = EVENTDATA(); 
-- extraction à l'aide d'XQuery/XPath du nom du schema et du nom de table 
SELECT @SCH = @XML.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'), 
       @TAB = @XML.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'); 
-- génération d'une requête de création de la table d'historisation 
DECLARE @SQL VARCHAR(max); 
-- un schéma existe-il avec ce nom là ? 
IF NOT EXISTS (SELECT * 
               FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.SCHEMATA 
               WHERE  SCHEMA_NAME = @SCH) 
BEGIN 
-- non : on le créé 
   SET @SQL = 'CREATE SCHEMA ' + @SCH; 
   EXEC (@SQL); 
END;  
 
-- on regarde si c'est une table temporaire "Tmp_"+lenom de la table init
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = SUBSTRING(@TAB,5,LEN(@TAB)-4)
 
IF @@ROWCOUNT = 0
begin
-- création de la table 
SET @SQL = 'CREATE TABLE DB_HST_LIGNE.' + @SCH+'.'+@TAB + ' (' 
    + '_ID BIGINT NOT NULL IDENTITY PRIMARY KEY, _MD CHAR(1), ' 
    + '_DH DATETIME DEFAULT CURRENT_TIMESTAMP, ' 
    + '_SU NVARCHAR(128) DEFAULT USER, _MA NCHAR(40), '; 
SELECT @SQL = @SQL + COLUMN_NAME + ' ' + DATA_TYPE + 
       CASE  
          WHEN DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')  
             THEN ' (' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(16))  
                  + ') COLLATE ' + COLLATION_NAME 
          WHEN DATA_TYPE IN ('decimal', 'numeric')  
             THEN ' (' + CAST(NUMERIC_PRECISION AS VARCHAR(16)) +', '  
                  + CAST(NUMERIC_SCALE AS VARCHAR(16)) + ')' 
          ELSE '' 
       END + ', ' 
FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @SCH 
  AND  TABLE_NAME = @TAB; 
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ')'; 
EXEC (@SQL); 
end;
 
END;

2 A la modification d'une table de prod je modifie la table d'historisation dans une deuxième base par trigger:

Concrètement: (attention il est très sale mais encoure de modification toute subjection est la bien venu)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
 
USE [DB_PRO]
GO
 
/****** Object:  DdlTrigger [E_DB_ALTTAB]    Script Date: 10/26/2011 10:05:08 ******/
 
/********************************************************************************************  
* Trigger de alter de table historisation                                                 * 
**********************************************************************************************  
* CF :Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com -                       * 
*http://www.sqlspot.com/sites/sqlspot.com/IMG/pdf/Historisation_des_donnees_en_mode_ligne.pdf*
**********************************************************************************************  
* Ce trigger reporte les modifications de la table de prod vers la table historique          *  
*                                                                                            *
*********************************************************************************************/
 
 
CREATE TRIGGER [E_DB_ALTTAB] 
ON DATABASE  
FOR ALTER_TABLE 
AS
BEGIN 
SET NOCOUNT ON; 
-- récupération des informations du "paquet" d'événement du tgrigger DDL 
DECLARE @XML XML, @SCH sysname, @TAB sysname; 
declare @test integer;
declare @Col VARCHAR(max);
SET @XML = EVENTDATA(); 
-- extraction à l'aide d'XQuery/XPath du nom du schema et du nom de table 
SELECT @SCH = @XML.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'), 
       @TAB = @XML.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'); 
-- génération d'une requête de création de la table d'historisation 
DECLARE @SQL VARCHAR(max); 
/* 
-- cette modification a t-elle ajoutée des colonnes de la table ? 
-- supression d'une colonne => la colonne est renommée en #001, #002, etc... 
-- changement de type d'une colonne => l'ancienne colonne est renomée _#001 et la 
nouvelle ajoutée 
-- ajout d'une colonne,  
*/
-- c'est une modification de type, voici comment on la détecte : 
SELECT TE.COLUMN_NAME, TE.DATA_TYPE, TE.CHARACTER_MAXIMUM_LENGTH, TE.COLLATION_NAME,
TE.NUMERIC_PRECISION, TE.NUMERIC_SCALE  
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME 
WHERE  NOT EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                  WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB 
                  EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB 
                  UNION 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                  EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                  WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB) 
 AND  T.COLUMN_NAME  <> '_DH' 
 AND  T.COLUMN_NAME  <> '_MA' 
 AND  T.COLUMN_NAME  <> '_MD' 
 AND  T.COLUMN_NAME  <> '_SU' 
 AND  T.COLUMN_NAME  <> '_ID' 
   AND  NOT T.COLUMN_NAME  LIKE '%#%' ;
 IF @@ROWCOUNT > 0 
 begin 
BEGIN TRANSACTION
--copie dans les structures des colonnes à modifier dans des colones annex nomcole+ # + num de modif
SET @SQL = 'ALTER TABLE DB_HST_LIGNE.' + @SCH+'.'+@TAB + ' ADD '; 
SELECT @SQL = @SQL + TE.COLUMN_NAME +'#' + cast((( SELECT COUNT(COLUMN_NAME) FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = @SCH   AND TABLE_NAME = @TAB AND COLUMN_NAME LIKE T.COLUMN_NAME+'#%' )+1) AS VARCHAR(max))+' ' + TE.DATA_TYPE + 
       CASE  
          WHEN TE.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')  
             THEN ' (' + CAST(TE.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(16))  
                  + ') COLLATE ' + TE.COLLATION_NAME 
          WHEN TE.DATA_TYPE IN ('decimal', 'numeric')  
             THEN ' (' + CAST(Te.NUMERIC_PRECISION AS VARCHAR(16)) +', '  
                  + CAST(TE.NUMERIC_SCALE AS VARCHAR(16)) + ')' 
          ELSE ' ' 
       END + ', ' 
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
		CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
			SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
			CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
			WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME 
WHERE  NOT EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                  WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB 
                  EXCEPT 
					SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   INFORMATION_SCHEMA.COLUMNS 
					WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB 
                  UNION 
				SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   INFORMATION_SCHEMA.COLUMNS 
				WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                  EXCEPT 
				SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                  WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB) 
 AND  T.COLUMN_NAME  <> '_DH' 
 AND  T.COLUMN_NAME  <> '_MA' 
 AND  T.COLUMN_NAME  <> '_MD' 
 AND  T.COLUMN_NAME  <> '_SU' 
 AND  T.COLUMN_NAME  <> '_ID'
  AND  NOT T.COLUMN_NAME  LIKE '%#%'  
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ''; 
EXEC (@SQL);
--2 je transfert les data des colonnes a modifier dans les nouvellements crée
SET @SQL = 'UPDATE DB_HST_LIGNE.' + @SCH+'.'+@TAB + ' SET '; 
SELECT @SQL = @SQL + TE.COLUMN_NAME +'#' + cast((( SELECT COUNT(COLUMN_NAME) FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = @SCH   AND TABLE_NAME = @TAB AND COLUMN_NAME LIKE T.COLUMN_NAME+'#%' )) AS VARCHAR(max))+'=' + TE.COLUMN_NAME  + ', ' 
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
		CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
			SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
			CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
			WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME 
WHERE  NOT EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                  WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB 
                  EXCEPT 
					SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   INFORMATION_SCHEMA.COLUMNS 
					WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB 
                  UNION 
				SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   INFORMATION_SCHEMA.COLUMNS 
				WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                  EXCEPT 
				SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                  WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB) 
 AND  T.COLUMN_NAME  <> '_DH' 
 AND  T.COLUMN_NAME  <> '_MA' 
 AND  T.COLUMN_NAME  <> '_MD' 
 AND  T.COLUMN_NAME  <> '_SU' 
 AND  T.COLUMN_NAME  <> '_ID' 
 AND  NOT T.COLUMN_NAME  LIKE '%#%' 
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + '';
 
--3 je sup les colone nommé
SET @SQL = 'ALTER TABLE DB_HST_LIGNE.' + @SCH+'.'+@TAB + ' DROP COLUMN '; 
SELECT @SQL = @SQL + TE.COLUMN_NAME+ ', ' 
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
		CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
			SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
			CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
			WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME 
WHERE  NOT EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                  WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB 
                  EXCEPT 
					SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   INFORMATION_SCHEMA.COLUMNS 
					WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB 
                  UNION 
				SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   INFORMATION_SCHEMA.COLUMNS 
				WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                  EXCEPT 
				SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                  FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                  WHERE  TABLE_SCHEMA = @SCH 
                    AND  TABLE_NAME = @TAB) 
 AND  T.COLUMN_NAME  <> '_DH' 
 AND  T.COLUMN_NAME  <> '_MA' 
 AND  T.COLUMN_NAME  <> '_MD' 
 AND  T.COLUMN_NAME  <> '_SU' 
 AND  T.COLUMN_NAME  <> '_ID' 
 AND  NOT T.COLUMN_NAME  LIKE '%#%' 
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + '';
 
EXEC (@SQL);
 
Commit
end
 
-- c'est une suppression, voici comment on la détecte :  
SELECT T.COLUMN_NAME, T.DATA_TYPE, T.CHARACTER_MAXIMUM_LENGTH, T.COLLATION_NAME,T.NUMERIC_PRECISION, T.NUMERIC_SCALE  
FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T 
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME 
   AND  T.COLUMN_NAME  <> '_DH' 
 AND  T.COLUMN_NAME  <> '_MA' 
 AND  T.COLUMN_NAME  <> '_MD' 
 AND  T.COLUMN_NAME  <> '_SU' 
 AND  T.COLUMN_NAME  <> '_ID' 
   AND  NOT T.COLUMN_NAME  LIKE '%#%' ;              
IF @@ROWCOUNT > 0 
BEGIN 
BEGIN TRANSACTION
--copie dans les structures des colonnes à modifier dans des colones annex nomcole+ # + num de modif
SET @SQL = 'ALTER TABLE DB_HST_LIGNE.' + @SCH+'.'+@TAB + ' ADD '; 
SELECT @SQL = @SQL + TE.COLUMN_NAME +'#' + cast((( SELECT COUNT(COLUMN_NAME) FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = @SCH   AND TABLE_NAME = @TAB AND COLUMN_NAME LIKE T.COLUMN_NAME+'#%' )+1) AS VARCHAR(max))+' ' + T.DATA_TYPE + 
       CASE  
          WHEN T.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')  
             THEN ' (' + CAST(T.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(16))  
                  + ') COLLATE ' + T.COLLATION_NAME 
          WHEN T.DATA_TYPE IN ('decimal', 'numeric')  
             THEN ' (' + CAST(T.NUMERIC_PRECISION AS VARCHAR(16)) +', '  
                  + CAST(T.NUMERIC_SCALE AS VARCHAR(16)) + ')' 
          ELSE ' ' 
       END + ', ' 
FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T 
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME 
   AND  T.COLUMN_NAME  <> '_DH' 
 AND  T.COLUMN_NAME  <> '_MA' 
 AND  T.COLUMN_NAME  <> '_MD' 
 AND  T.COLUMN_NAME  <> '_SU' 
 AND  T.COLUMN_NAME  <> '_ID' 
   AND  NOT T.COLUMN_NAME  LIKE '%#%' ;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ''; 
EXEC (@SQL);
--2 je transfert les data des colonnes a modifier dans cell nouvellement crée
SET @SQL = 'UPDATE DB_HST_LIGNE.' + @SCH+'.'+@TAB + ' SET '; 
SELECT @SQL = @SQL + TE.COLUMN_NAME +'#' + cast((( SELECT COUNT(COLUMN_NAME) FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = @SCH   AND TABLE_NAME = @TAB AND COLUMN_NAME LIKE T.COLUMN_NAME+'#%' )) AS VARCHAR(max))+'=' + TE.COLUMN_NAME  + ', ' 
FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T 
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME 
   AND  T.COLUMN_NAME  <> '_DH' 
 AND  T.COLUMN_NAME  <> '_MA' 
 AND  T.COLUMN_NAME  <> '_MD' 
 AND  T.COLUMN_NAME  <> '_SU' 
 AND  T.COLUMN_NAME  <> '_ID' 
   AND  NOT T.COLUMN_NAME  LIKE '%#%' ;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + '';
EXEC (@SQL);
--3 je sup les colone nommé
SET @SQL = 'ALTER TABLE DB_HST_LIGNE.' + @SCH+'.'+@TAB + ' DROP COLUMN '; 
SELECT @SQL = @SQL + TE.COLUMN_NAME+ ', ' 
FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T 
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME 
   AND  T.COLUMN_NAME  <> '_DH' 
 AND  T.COLUMN_NAME  <> '_MA' 
 AND  T.COLUMN_NAME  <> '_MD' 
 AND  T.COLUMN_NAME  <> '_SU' 
 AND  T.COLUMN_NAME  <> '_ID' 
   AND  NOT T.COLUMN_NAME  LIKE '%#%' ;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + '';
 
EXEC (@SQL);
 
Commit
END 
 
 
--- c'est un ajout, voici comment on le détecte :  
SELECT T.COLUMN_NAME, T.DATA_TYPE, T.CHARACTER_MAXIMUM_LENGTH, T.COLLATION_NAME,
T.NUMERIC_PRECISION, T.NUMERIC_SCALE  
FROM   INFORMATION_SCHEMA.COLUMNS AS T 
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME  
IF @@ROWCOUNT > 0 
BEGIN 
 
SET @SQL = 'ALTER TABLE DB_HST_LIGNE.' + @SCH+'.'+@TAB + ' ADD '; 
SELECT @SQL = @SQL + T.COLUMN_NAME + ' ' + T.DATA_TYPE + 
       CASE  
          WHEN DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')  
             THEN ' (' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(16))  
                  + ') COLLATE ' + COLLATION_NAME 
          WHEN DATA_TYPE IN ('decimal', 'numeric')  
             THEN ' (' + CAST(NUMERIC_PRECISION AS VARCHAR(16)) +', '  
                  + CAST(NUMERIC_SCALE AS VARCHAR(16)) + ')' 
          ELSE '' 
       END + ', ' 
FROM   INFORMATION_SCHEMA.COLUMNS AS T 
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB 
                   EXCEPT 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME --DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE  
                   FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS 
                   WHERE  TABLE_SCHEMA = @SCH 
                     AND  TABLE_NAME = @TAB) AS TE 
             ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA 
                AND T.TABLE_NAME = TE.TABLE_NAME 
                AND T.COLUMN_NAME = TE.COLUMN_NAME  
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ' '; 
EXEC (@SQL); 
END  ;
 end;

Tout semble fonctionnel "en manuel" (j'entends par la le même code que j’exécute hors trigger) avec les un peu gore pour le deuxième trigger mais fonctionnel.
Mon problème c'est que quand tout est en automatique si dans mes modification de table je passe par l'interface graphique et que je modifie un type de colonne il me généré une erreur assez embêtante parce qu'elle me supprime tout simplement ma table de production
L’erreur dit:
"table "test"
-Impossible de modifier la table.
Soit le paramètre de @objname est ambigu, soit le @objtype déclaré(OBJECT) est incorrect"

Je me demande si cela ne viens pas du fait que lors d'une modification de type par l'interface graphique il est réalité crée une table temporaire (comme ici ou quelque chose s'en approchant parce mon trigger de création de table la capter avant que je ne le modifie) et cela je pense en même temps que mon trigger d'alter table du coup ma table de prod n'existe plus entre le début et la fin de mon trigger et du coup mon trigger n'arrive pas aller au bout de son traitement et stop tout les traitements. Ce qui ne ce produit pas si je fais un changement de type par code.
Votre avis ?

[elsuket]

Je me demande si cela ne viens pas du fait que lors d'une modification de type par l'interface graphique il est réalité crée une table temporaire (comme ici ou quelque chose s'en approchant parce mon trigger de création de table la capter avant que je ne le modifie) et cela je pense en même temps que mon trigger d'alter table du coup ma table de prod n'existe plus entre le début et la fin de mon trigger et du coup mon trigger n'arrive pas aller au bout de son traitement et stop tout les traitements. Ce qui ne ce produit pas si je fais un changement de type par code.
Votre avis ?

C'est effectivement le cas si vous :

- changez le type d'une colonne
- vous la rendez (non)NULL-able
- ajoutez / supprimez une colonne

Dès lors effectivement votre transaction plante, et la table source n'existe plus.

Si vous êtes certain que vous n'allez jamais qu'ajouter des colonnes aux tables, vous pouvez gérer cela en capturant à l'aide du trigger que je vous ai donné la colonne ajoutée, et en l'ajoutant à la table d'audit.
Il faut en plus modifier le(s) trigger(s) correspondants à la volée, et le seul moyen de le faire c'est avec du code T-SQL dynamique : vous créez la chaîne de requête au fil de l'eau, et vous exécutez le code avec sp_executesql ou EXEC(@sql).
Dans votre cas EXEC(@sql) est acceptable.

Vous pouvez faire pareil lorsqu'une colonne est supprimée, mais dans ce cas vous perdrez les valeurs des colonnes pour les mises à jour antérieures au moment de la suppression de la colonne.

Enfin, je suppose donc que vous êtes sous SQL Server 2005 ou 2000 ...

@++

[mikedavem]

Quelle version et quelle édition de SQL Server ? Sachez que vous pouvez utiliser les audits à partir de la version 2008 pour trapper ce genre d'événement.

++

[obarator]

Merci pour vos réponse.

Je suis en 2008 (ou 2008r2) express. Donc pas de Change Data Capture. Maintenant je comprend pas trop ce dont vous me parler.

Je ne pas tellement envie d'auditer les modifications de mes tables je veu juste que le modification suivent (avec des régles spécifique) ma table d'historisation ce qui fonctionne avec mon trigger.

Maintenant je vois pas trop comment faire en sorte que dans le cas ou il y a utilisation d'une table temporaire mon trigger soit tjs fonctionnel.

Ni a-t-il pas moyen "d'attendre" que le serveur et finis d'utiliser un table temporaire pour qu'il ré-existe bien un table tel que nommer au lancer du trigger d'alter ?

[obarator]

Bonjour,
En fait le problème venais bien du fait qu'il a création d'une table temporaire quand on modifie par l'interface graphique.
Mais pas du fait que mon trigger voyer sa table de référence disparaître en cour de traitement pour la bonne et simple raison que le trigger ce fait "AFTER" il ne peu pas en tout logique perdre sa table.

Le serveur fais plutot sa quand on modifie le type d'une colonne.
Creation table Tmp -> Alter table Tmp -> transfert table origine
Creation new table -> Alter new table -> transfert table alter

Mon problème venais du "alter table Tmp" enfin je pense puisque j'ai virer le traitement sur les tables Tmp et maintenant tout semble fonctionner.

je reviendrai donc vous proposer ma solutions complète si sa peus aider