Copie de ligne, à l'exception de l'identifiant unique, sans précision des noms de champs

Version imprimable

05/08/2016, 12h13
calagan99

Copie de ligne, à l'exception de l'identifiant unique, sans précision des noms de champs

Bonjour à tous,

je dois mettre en place un trigger à l'ajout d'une ligne dans une table.
Cette table n'a pas une structure figée, les utilisateurs d'une appli peuvent y ajouter des champs en fonction de leur besoins via une IHM. Le nombre de champs augmente donc avec le temps (dans des proportions raisonnables).

Le trigger en question a pour objectif de copier les éléments d'un ancien enregistrement vers un nouvel enregistrement au moment de l'INSERT.

Or, dans la mesure où la structure de la table mise à jour peut changer, je voudrais si possible éviter de faire un update en déclarant la liste de tous les champs disponible dans la table. Cela m'obligerait à maintenir régulièrement le trigger en lui ajoutant les champs ajoutés par les utilisateurs.

Je cherche donc un moyen de copier un enregistrement (identifiant unique exclu) vers un autre.

Avez-vous des idées de la manière dont je peux procéder ?

Merci d'avance
05/08/2016, 12h33
François DORIN

Bonjour,

La première solution qui me vient à l'esprit, c'est de générer une requête dynamique en interrogeant la couche meta et en listant toutes les colonnes de la table et en excluant l'identifiant unique.
05/08/2016, 12h40
al1_24

Je complèterais la réponse de @dorinf en ne prenant que les colonnes communes aux tables source et cible si elles sont différentes, en excluant toujours l'identifiant unique.

J'ai pris 5min pour écrire une requête qui devrait répondre quasi à tes besoins :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @TableName VARCHAR(126);
DECLARE @ID_Column VARCHAR(128);
DECLARE @ID VARCHAR(50);
 
SET @TableName = 'sexe_enum';
SET @ID_Column = 'id';
SET @ID = '456';
SELECT 'INSERT INTO ' + @TableName +' ('
	+ SUBSTRING((SELECT ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME != @ID_Column AND TABLE_NAME = @TableName FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 2, 8000)
	+ ') SELECT ' 
	+ SUBSTRING((SELECT ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME != @ID_Column AND TABLE_NAME = @TableName FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 2, 8000)
	+ ' FROM ' + @TableName + ' WHERE ' + @ID_Column + ' = ''' + @ID + '''';

Le résultat du SELECT est une requête SQL générée dynamiquement. Il ne reste plus qu'à l'exécuter. Je suis parti du principe que la table source et la table cible étaient les mêmes. Il faudra l'adapter si cela n'est pas le cas, comme souligné par @al1_24

05/08/2016, 14h00
calagan99

Dorinf, Al1_24 : merci pour vos réponses.

Al1_24 : l'update se fait sur un enregistrement cible présent dans la même table que l'enregistrement source. La structure est donc identique.

Dorinf : j'avais en effet pensé à un SQL dynamique comme celui que tu me proposes. Le problème est que je dois faire un update et non un insert.
la structure de requête dynamique à créer est donc différente et je n'arrive pas à voir comment la créer.

Edit : une solution possible consisterait à supprimer l'enregistrement cible puis à le recréer après avec un insert comme proposé. Je n'ai pas de champ auto-incrémenté dans la table en question. Mais cette solution me paraît risquée.
05/08/2016, 14h04
François DORIN

La structure est différente mais le principe reste le même. Comment sais-tu quel enregistrement tu dois mettre à jour ? Tu as son id ?
05/08/2016, 14h15
calagan99

Citation:

Envoyé par dorinf

Tu as son id ?

Oui, je le récupères via

Code:

SELECT ID_EXT FROM inserted

dans mon trigger.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @TableName VARCHAR(126);
DECLARE @ID_Column VARCHAR(128);
DECLARE @SourceID VARCHAR(50);
DECLARE @TargetID VARCHAR(50);
 
SET @TableName = 'sexe_enum';
SET @ID_Column = 'id';
SET @SourceID = '456';
SELECT @TargetID = ID_EXT FROM inserted;
 
SELECT 'UPDATE T SET '
	+ SUBSTRING((SELECT ',T.' + COLUMN_NAME + ' = S.' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME != @ID_Column AND TABLE_NAME = @TableName FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 2, 8000)
	+ ' FROM ' + @TableName + ' AS T, ' + @TableName + ' AS S ' 
	+ ' WHERE T.id = ' + @TargetID + ' AND S.id = ' + @SourceID;

Requête écrite en live, donc pas testée. Mais le principe est là ;)

05/08/2016, 14h57
calagan99

Merci Dorinf, c'est exactement ce qu'il me fallait.

Je ne sais pas pourquoi je bloquais sur la gestion du FOR XML PATH. J'en étais à 10 tentatives infructueuses à base de curseur...
05/08/2016, 16h21
aieeeuuuuu

Bonjour,

Citation:

Envoyé par calagan99

Cette table n'a pas une structure figée, les utilisateurs d'une appli peuvent y ajouter des champs en fonction de leur besoins via une IHM

Fonctionnement étrange !

Une colonne de type XML (ou autre solution, je ne connais pas le contexte) aurais surement été plus appropriée pour stocker ces données variées plutôt que d'ajouter des colonnes sur des actions des utilisateurs...

et ça aurait bien simplifié l'écriture du trigger ;)
08/08/2016, 15h01
calagan99

aieeeuuuuu : malheureusement, ce n'est pas la seule "bizarrerie"... Pas de contraintes d'intégrité, pour chaque table une seconde suffixée "_ID" dans laquelle on insère une ligne pour sélectionner l'ID généré puis supprimer ladite ligne avant d'utiliser l'ID sélectionner dans la table principale, etc.
Le tout sur un progiciel "du commerce" de large diffusion...

Sinon, je ré-ouvre le sujet car le script fonctionne sur la forme mais plus à l'exécution de mon trigger.
Quand le trigger est en place est qu'il est déclenché par une insertion depuis l'application, une erreur est remontée : Une erreur de dépassement arithmétique s'est produite lors de la conversion de varchar en type de données numeric.

J'ai débuggué le script du trigger et c'est bien l'UPDATE qui pose problème.
Je ne vois pas comment c'est possible puisque les données sources sont mises à jour sur une ligne de la même table.
08/08/2016, 15h04
François DORIN

Pourrait-on avoir la structure de la table ainsi que le code du trigger ?

Dorinf :
le script de création de la table :

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
CREATE TABLE [dbo].[EXTEQU](
	[ID_EXT] [dbo].[T_NUMINT] NOT NULL,
	[PNU_DIAM] [numeric](4, 0) NULL,
	[PNU_PUISS] [numeric](4, 0) NULL,
	[PST_NUMSER] [varchar](50) NULL,
	[PNU_DEBIT] [varchar](4) NULL,
	[PDE_HMT] [numeric](5, 2) NULL,
	[PNU_DEBI] [numeric](4, 0) NULL,
	[PNU_VROTAT] [numeric](5, 0) NULL,
	[PNU_VOLUME] [numeric](5, 0) NULL,
	[PNU_ANMES] [numeric](4, 0) NULL,
	[PN_DVIEPRE] [numeric](2, 0) NULL,
	[PNU_DVIPRE] [numeric](2, 0) NULL,
	[PNU_ANRENO] [numeric](4, 0) NULL,
	[PMO_VALREM] [numeric](16, 5) NULL,
	[PST_RPRNP] [varchar](3) NULL,
	[PNU_NBRP] [numeric](3, 0) NULL,
	[PMO_MTRP] [numeric](16, 5) NULL,
	[PNU_RISQUE] [numeric](3, 0) NULL,
	[PMO_MTRNP] [numeric](16, 5) NULL,
	[PST_TYPE] [varchar](100) NULL,
	[PST_ADRESS] [varchar](250) NULL,
	[PST_TYPCON] [varchar](50) NULL,
	[PNU_ORDRE] [numeric](4, 0) NULL,
	[PST_NEWEQU] [varchar](100) NULL,
	[PST_EQUOLD] [varchar](100) NULL,
	[PST_TYPOEQ] [varchar](100) NULL,
	[PDT_DATMES] [datetime] NULL,
	[PDT_DATREN] [datetime] NULL,
	[PNU_DVIPMO] [numeric](3, 0) NULL,
	[PNU_DVITEC] [numeric](2, 0) NULL,
	[PDT_DTRETC] [datetime] NULL,
	[PNU_COEF15] [numeric](7, 4) NULL,
	[PDT_DEPOSE] [datetime] NULL,
	[PDT_POSE] [datetime] NULL,
	[PST_REFEXT] [varchar](500) NULL,
 CONSTRAINT [PK_EXTEQU] PRIMARY KEY CLUSTERED 
(
	[ID_EXT] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

et le trigger :

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
CREATE TRIGGER [dbo].[PER_TRIGGER_UPDATE_OLDEQU]
ON  [dbo].[EXTEQU]
AFTER INSERT
AS 
 
DECLARE @ID_NUMBT numeric(10,0),
@ID_EXTNEW numeric(10,0), -- ID EXT DU NOUVEL EQU
@ID_EXTOLD numeric(10,0), -- ID EXT DE L'EQU REMPLACE
@EQUOLD VARCHAR(100), -- CODE LONG DE L'EQU REMPLACE
@NEWEQU VARCHAR(100), -- CODE LONG DU NOUVEL EQU
@ID_CODPERS numeric(6,0), -- CODE PERSO AUTO GENERE
@ID_CODPERSANCIEN varchar(20), -- CODE PERSO DE L EQU REMPLACE. UTILISE S IL S AGIT AU MOINS DU 2E REMPLACEMENT DE L EQU
@NOW SMALLDATETIME, -- DATE REALISATION
@DATE_DEPOSE DATETIME, -- DATE DE DEPOSE DE L'EQU
@ID_NUMEQUNEW numeric(10,0),
@ID_NUMEQUOLD numeric(10,0),
@EXTEQUTable VARCHAR(126),
@ID_EXTColumn VARCHAR(128),
@SQL nVARCHAR(4000)
 
BEGIN
SET NOCOUNT ON;
 
SET @EXTEQUTable = 'EXTEQU'
SET @ID_EXTColumn = 'ID_EXT'
 
-- ON RECUPERE L'ID_EXT DE L EQU CREE
set @ID_EXTNEW = (SELECT ID_EXT FROM inserted)
-- ON RECUPERE LE CODE LONG DE L EQU CREE
set @NEWEQU = (SELECT ST_CODLON FROM [dbo].[EQU] WHERE ID_EXT = @ID_EXTNEW)
 
-- SI CES DEUX VARIABLES NE SONT PAS VIDES
IF (@NEWEQU IS NOT NULL AND @ID_EXTNEW IS NOT NULL)
	BEGIN
		-- ON RECUPERE LE CODE LONG DE L EQU REMPLACE DANS LA DONNEE ETENDUE PST_EQUOLD DU NOUVEL EQU
		set @EQUOLD = (SELECT PST_EQUOLD FROM [dbo].[EXTEQU] WHERE [ID_EXT] = @ID_EXTNEW)
		-- ON RECUPERE L'ID EXT DE L EQU REMPLACE A PARTIR DE SON CODE LONG
		set @ID_EXTOLD = (SELECT ID_EXT FROM EQU WHERE ST_CODLON = @EQUOLD)
 
			-- SI CES DEUX VARIABLES NE SONT PAS VIDES
			IF (@EQUOLD IS NOT NULL)
				BEGIN		
					-- ON VERIFIER SI L EQU A DEJA ETE REMPLACE. POUR CELA ON RECHERCHE SI EQUOLD A DEJA UN CODE PERSO
					SET @ID_CODPERSANCIEN = (SELECT ST_CODPERS FROM EQU WHERE ST_CODLON = @EQUOLD)
					--SI C EST LE CAS ON REUTILISE CE CODE PERSO POUR CONSERVER L HISTO COMPLET DES RENOU SUR CET EMP
					IF (@ID_CODPERSANCIEN IS NOT NULL)
						BEGIN
							SET @ID_CODPERS = @ID_CODPERSANCIEN
						END
					-- SINON ON GENERE UN CODE PERSO
					ELSE
						BEGIN
							SELECT @Now = GETDATE()
							INSERT INTO PERCODPER_ID (ID_CODUTI,DT_ID) VALUES ('SYS',@Now)
							SELECT @ID_CODPERS = ID FROM PERCODPER_ID WHERE ID_CODUTI='SYS' AND DT_ID=@Now  
							DELETE FROM PERCODPER_ID WHERE ID_CODUTI='SYS' AND DT_ID=@Now
						END
 
					-- ON RECUPERE LA DATE DE DEPOSE
					SET @DATE_DEPOSE = (SELECT PDT_DEPOSE FROM EXTEQU WHERE ID_EXT = @ID_EXTNEW)
					-- ON RECUPERE L'ID_NUMEQU DU NOUVEL EQUIPEMENT
					SET @ID_NUMEQUNEW = (SELECT ID_NUMEQU FROM EQU WHERE ST_CODLON = @NEWEQU)
					-- ON RECUPERE L'ID_NUMEQU DE L'ANCIEN EQUIPEMENT
					SET @ID_NUMEQUOLD = (SELECT ID_NUMEQU FROM EQU WHERE ST_CODLON = @EQUOLD)
					-- ON PASSE L EQU DEPOSE EN INACTIF ET ON LUI ATTRIBUE LE CODE PERSO GENERE CI DESSUS						
					UPDATE EQU SET ST_INA = 'O', ST_CODPERS = CONVERT(varchar(20),@ID_CODPERS) WHERE ST_CODLON = @EQUOLD
					-- ON MET A JOUR LA DONNEE ETENDUE DE L EQU REMPLACE POUR INDIQUER LE CODE LONG DE L EQU REMPLACANT
					UPDATE EXTEQU SET PST_NEWEQU = @NEWEQU, PDT_DEPOSE = @DATE_DEPOSE WHERE ID_EXT = @ID_EXTOLD					
					-- ON APPLIQUE LE MEME CODE PERSO AU NOUVEL EQU
					UPDATE EQU SET ST_CODPERS = CONVERT(varchar(20),@ID_CODPERS) WHERE ST_CODLON = @NEWEQU									
					-- ON VIDE LA DATE DE DEPOSE SAISIE SUR LE NOUVEL EQUIPEMENT
					UPDATE EXTEQU SET PDT_DEPOSE = NULL WHERE ID_EXT = @ID_EXTNEW					
					-- ON MET A JOUR LES ECHEANCES DE L'ANCIEN EQU AVEC L'ID DU NOUVEL EQUIPEMENT
					UPDATE ECH SET ID_NUMEQU = @ID_NUMEQUNEW WHERE ID_NUMEQU = @ID_NUMEQUOLD
 
					set ARITHABORT ON
					-- ON GENERE UN SCRIPT POUR DUPLIQUER LES VALEURS DES DONNEES ETENDUES
					SELECT @SQL = 'UPDATE T SET '
					+ SUBSTRING((SELECT ',T.' + COLUMN_NAME + ' = S.' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME NOT IN ('ID_EXT','PST_NEWEQU','PDT_POSE','PDT_DEPOSE') AND TABLE_NAME = @EXTEQUTable FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 2, 8000)
					+ ' FROM ' + @EXTEQUTable + ' AS T, ' + @EXTEQUTable + ' AS S ' 
					+ ' WHERE T.ID_EXT = ' + @ID_EXTNEW + ' AND S.ID_EXT = ' + @ID_EXTOLD 
					-- ON EXECUTE LA COPIE DES DONNEES ETENDUES
					EXEC(@SQL)
					set ARITHABORT OFF
 
					-- ON MET A JOUR LE NOUVEL EQU AVEC LES DONNEES DE BASE DE L'EQU REMPLACE
					UPDATE T SET 
						T.ST_CONTRABT = S.ST_CONTRABT, -- CONTRAT SUR BT
						T.ST_COM = S.ST_COM, -- PRESENCE COMPTEUR
						T.ID_CODCONTRA = S.ID_CODCONTRA, -- CONTRAT (AFFAIRE)
						T.ID_CODCOM = S.ID_CODCOM -- IDENTIFIANT DU COMPTEUR
					FROM EQU AS T, EQU AS S
					WHERE T.ID_NUMEQU = @ID_NUMEQUNEW AND S.ID_NUMEQU = @ID_NUMEQUOLD
 
				END		
	END
END

Le trigger fait appel à d'autres tables dont je n'ai pas mis ici les scripts de création.

08/08/2016, 15h56
François DORIN

A priori, le problème se situe lors de la construction même de la requête. @ID_EXTNEW et @ID_EXTOLD sont de type numeric dans ton script. Il faut donc les convertir en VARCHAR avant la concaténation.

bonjour,

quelle est la définition du type utilisateur [dbo].[T_NUMINT] ?

quelques remarques au passage :
1/ sur cette partie :
Code:

1 2 3 4 5 6 7 BEGIN SELECT @Now = GETDATE() INSERT INTO PERCODPER_ID (ID_CODUTI,DT_ID) VALUES ('SYS',@Now) SELECT @ID_CODPERS = ID FROM PERCODPER_ID WHERE ID_CODUTI='SYS' AND DT_ID=@Now DELETE FROM PERCODPER_ID WHERE ID_CODUTI='SYS' AND DT_ID=@Now END
Vous pourriez récupérer directement l'identifiant généré grâce par exemple à SCOPE_IDENTITY, plutôt que faire une requete SELECT sur la table après insertion

2/ vous effectuez plusieurs requêtes sur la même table pour récupérer des informations diverses, vous pourriez le faire en une seule requete
Code:

1 2 SELECT @var1 = col1, @var2,col2... FROM LaTable
Je pense par exemple à toutes les requetes qui se terminent par WHERE ID_EXT = @ID_EXTNEW : vous pourriez récupérer toutes vos info dès le début (et dans la pseudo table inserted directement)

3/ enfin, votre trigger n'est pas ensembliste, et ne fonctionnera pas si plusieurs lignes sont insérées en même temps. J'imagine que ce cas n'est pas censé arriver, mais il pourrait être utile d'ajouter un contrôle en début de trigger.

08/08/2016, 16h19
calagan99

Dorinf, aieeeuuuuu : merci pour votre aide.

@Dorinf : la création du SQL et son affectation à la variable @SQL ne pose pas de problème. L'erreur se produit bien sur "Exec @SQL". J'avais déjà essayé de modifier les variables citées de numeric vers varchar.

@aieeeuuuuu :
le type T_NUMINT est défini comme suit

Code:

CREATE TYPE [dbo].[T_NUMINT] FROM [numeric](10, 0) NULL

1) je ne connaissais pas. Merci.
2) en effet. L'écriture du trigger est en cours et pour le moment j'empile les règles de gestion et ajoute une requête à chaque fois. Je factoriserai ce qui pourra l'être une fois que j'aurais finalisé l'écriture.
3) une seule ligne peut être ajoutée via l'IHM.
08/08/2016, 16h43
François DORIN

D'accord. J'avais réussi à faire quelques tests et c'était justement le cast de ces variables qui me posaient problème. Donc là, je ne vois pas trop d'où peut venir l'erreur. Il faudrait un exemple complet pour pouvoir tester.

Il serait bon aussi que tu nous donnes un exemple de requête SQL générée qu'on puisse l'étudiée, à défaut de pouvoir la générer nous même.

lorsque vous construisez la requete, il faut transtyper explicitement vos variables numériques en VARCHAR
Code:

1 2 + ' WHERE T.ID_EXT = ' + @ID_EXTNEW + ' AND S.ID_EXT = ' + @ID_EXTOLD
-->
Code:

1 2 + ' WHERE T.ID_EXT = ' + CAST(@ID_EXTNEW AS VARCHAR(10))+ ' AND S.ID_EXT = ' + CAST(@ID_EXTOLD AS VARCHAR(10))

08/08/2016, 17h51
calagan99

@aieeeuuuuu : en effet, le cast explicite fonctionne.
Je ne comprends pas. J'avais typé mes variables en varchar dans leur déclaration, comme Dorinf l'indiquais, et ça n'avait pas fonctionné.

Désormais, ça fonctionne correctement.

Merci pour votre aide.