Pb : requete avec nom de champ variable renvoie le nom du champ

**Illecebrum** · 04/12/2008, 12h37

Bonjour,

Je souhaite réaliser un trigger qui lors d'un modification d'une table archive les données des champs sous forme d'une chaine de caractères XML.
Pour ce faire j'utilise un curseur qui liste les champs de ma table, puis pour chaque champ je fait une requete me renvoyant la valeur du champ grace à l'instruction :
SELECT @value = @champ FROM DELETED;
Le problème est que la variable @value contient le nom du champ et non pas ça valeur.

Pourriez vous m'aider à résoudre ce problème?
Merci d'avance

Le trigger est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
ALTER TRIGGER [dbo].[tr_ArchiveDataOnUpdate]
ON [dbo].[Individual_Obj]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @id int, @xml nvarchar(MAX), @sqlval nvarchar(500), @Str_Para nvarchar(500);
    select @id = inserted.Individual_Obj_Pk from inserted
 
    DECLARE @champ nvarchar(250), @value nvarchar(250), @sql nvarchar(1000);
    DECLARE i CURSOR FOR SELECT syscolumns.name FROM sysobjects , syscolumns WHERE ( sysobjects.id = syscolumns.id) And (sysobjects.xtype = 'U') AND sysobjects.name = 'Individual_Obj';
 
    SET @xml = '<Object>'+ CHAR(13);
    SET @xml = @xml+ '<pk>'+CAST(@id AS nvarchar(250))+'</pk>'+ CHAR(13);
    SET @xml = @xml+ '<attibutes>'+ CHAR(13);
    OPEN i
    FETCH i INTO @champ
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @xml = @xml+ '<attibute>';
            SET @xml = @xml+ '<name>'+@champ+'</name>';
            SELECT @value = @champ FROM DELETED; 
            IF NOT @value IS NULL
            BEGIN
                SET @xml = @xml + '<value>'+@value+'</value>';
            END        
            SET @xml = @xml+ '</attibute>'+ CHAR(13) ;
        FETCH i INTO @champ
        END
    CLOSE i
    DEALLOCATE i     
    SET @xml = @xml+ '</attibutes>'+ CHAR(13);
SET @xml = @xml+ '</Object>'+ CHAR(13);
    print (@xml);
    SELECT * from DELETED;
    INSERT INTO dbo.LogObjects (object_type,fk_object, update_date, obj_archive ) VALUES ('Individual', @id , CURRENT_TIMESTAMP, @xml);
 
END

Lorsque je faire un update la variable @xml correspond à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
<Object>
<pk>90</pk>
<attibutes>
<attibute><name>Individual_Obj_Pk</name><value>Individual_Obj_Pk</value></attibute>
<attibute><name>creation_date</name><value>creation_date</value></attibute>
<attibute><name>end_date</name><value>end_date</value></attibute>
<attibute><name>available</name><value>available</value></attibute>
<attibute><name>TCaracThes_Sex</name><value>TCaracThes_Sex</value></attibute>
<attibute><name>birth_date</name><value>birth_date</value></attibute>
<attibute><name>death_date</name><value>death_date</value></attibute>
<attibute><name>comments</name><value>comments</value></attibute>
</attibutes>
</Object>

**elsuket** · 04/12/2008, 22h07

Bonjour,

Votre trigger n'est pas ensembliste.
Donc si vous mettez à jour par exemple 4000 lignes de votre table, que va-t-il se passer ?
Seule la première valeur rencontrée dans la colonne est affectée à vos variables, et les autres passeront à la trappe

Regardez les problèmes que cela pose dans le billet que j'ai écrit à ce sujet.

Bannissez aussi l'utilisation de curseur, surtout dans les triggers, qui doivent s'exécuter rapidement.
Vous connaissez déjà les colonnes de votre table, c'est dommage d'utiliser un curseur lent et moche.
Au pire si vous enlevez ou supprimez une colonne de votre table, il vous faudra maintenir un peu votre trigger

Voici donc le code que je vous propose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
ALTER TRIGGER dbo.tr_ArchiveDataOnUpdate
	ON dbo.Individual_Obj
AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @xml NVARCHAR(MAX);
 
	SELECT @xml = ISNULL(@xml, '') +
				'<Object>' + CHAR(13) + 
					'<pk>'+ CAST(id AS VARCHAR) + '</pk>' + CHAR(13) +
					'<attibutes>' + CHAR(13) +
						'<attibute><name>colonneDeTypeNumerique</name><value>' + CAST(colonneDeTypeNumerique AS VARCHAR) + '</value></attibute>' + CHAR(13) +
						'<attibute><name>colonneDeTypeChaîne</name><value>' + colonneDeTypeChaîne + '</value></attibute>' + CHAR(13) +
						'<attibute><name>colonneDeTypeDate</name><value>' + CONVERT(CHAR(10), colonneDeTypeDate, 103) + '</value></attibute>' + CHAR(13) +
					'</attibutes>' + CHAR(13) +
				'<Object>'
	FROM DELETED;
 
	INSERT INTO dbo.LogObjects
	(
		object_type,
		update_date,
		obj_archive
	)
	VALUES
	(
		'Individual',
		CURRENT_TIMESTAMP,
		@xml
	);
END;

Ici vous concaténez toutes les valeurs d'un trait, en ne vous servant que de la table virtuelle DELETED.

La colonne dbo.LogObjects est-elle de type XML ? Sinon, c'est dommage, d'autant que vous pouvez valider la chaîne construite avec un schéma XSD (CREATE XML SCHEMA COLLECTION monSchemaXML AS, et CREATE TABLE maTable (maColonne XML (monSchemaXML))

Vous pouvez utiliser la fonction CURRENT_TIMESTAMP, mais préférez lui GETDATE(), qui est ANSI.

@++

**Illecebrum** · 05/12/2008, 09h33

Merci pour la réponse. Je vais revoir mon trigger de façon à ne pas faire appel a un curseur. Pour ce qui est de faire un trigger ensembliste ce n'est théoriquement pas très important car les modifications (réalisées au travers d'une interface) n'implique qu'un seul enregistrement.

Par contre je ne comprend toujours pas le comportement de mon "ancien" trigger. Pourquoi me renvoie t-il la valeur de la variable @champ et non pas le résultat du SELECT?

a+