Trigger Ensembliste de Alter table pour historisation.

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 :ccool:.
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 ?

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

A +

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 ?

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 ;)

@++; )

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 :aie:
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 ?

Citation:

---

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 ...

@++ ;)

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.

++

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 ?

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