Récupérer les identity dans une clause output après avoir inseré dans une vue.

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour,

j'ai une vue avec un déclencheur pour insérer dans des tables, dont une table qui a une colonne identity. J'insère des données dans cette table, et je souhaite récupérer la valeur de ces identity dans une clause output. Or, comme vous pouvez le voir dans l'exemple de code complet fourni, je ne récupère pas cet id...:pleure: Avez-vous une idée, s'il-vous-plait ?

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
 
CREATE TABLE My
(
	Id INT IDENTITY(1,1) CONSTRAINT PKMy PRIMARY KEY
);
 
CREATE TABLE MyLabel
(
	Id INT NOT NULL CONSTRAINT FKMyLabel FOREIGN KEY REFERENCES My(Id),
	Label VARCHAR(50) NOT NULL
);
GO
 
CREATE VIEW VMy
AS
	SELECT m.Id, ml.Label
	FROM My m
		INNER JOIN MyLabel ml
			ON ml.Id = m.Id;
GO
 
CREATE TRIGGER iVMY ON VMy
INSTEAD OF INSERT
AS
BEGIN    
	DECLARE @are TABLE (
        Row bigint IDENTITY(1,1) not null primary key,
        Id INT not null
    );
	MERGE INTO My
			USING inserted T
			ON 1 = 0
			WHEN NOT MATCHED THEN
			  INSERT
			  DEFAULT VALUES
			OUTPUT inserted.Id INTO @are;
 
	DECLARE @inserted TABLE (
        Id INT not null,
        Label VARCHAR(55) null
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Id, a.Id),
        i.Label
    FROM (
        SELECT
            Id,
            Label,
            ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS Row
        FROM
            inserted
    ) i
    LEFT JOIN
        @are a
    ON a.Row = i.Row;
END;
GO
 
CREATE TABLE #TEST
(
	Label VARCHAR(50)
)
 
INSERT INTO #TEST
VALUES ('HOP'), ('POUET'), ('TEST')
 
CREATE TABLE #INSERTED
(
	Id INT,
	Label VARCHAR(50)
)
 
INSERT INTO VMy(Label)
OUTPUT inserted.Id, inserted.Label INTO "#INSERTED"
SELECT	Label
FROM #TEST
 
SELECT *
FROM #INSERTED

Résultat :

Id	Label
NULL	HOP
NULL	POUET
NULL	TEST

25/02/2020, 13h42
SQLpro

Il ne faut pas utiliser un trigger INSTEAD OF mais AFTER INSERT ou bien récupérer les ID après l'insertion dans le déclencheur INSTEAD OF.

A +
25/02/2020, 14h41
Monstros Velu

Bonjour, et merci pour l'aide !

Malheureusement, on ne peut pas utiliser de trigger "AFTER INSERT" sur une vue :(
Et je ne vois pas comment passer les ID du trigger à l'appelant...
25/02/2020, 16h07
SQLpro

Citation:

Envoyé par Monstros Velu

Bonjour, et merci pour l'aide !

Malheureusement, on ne peut pas utiliser de trigger "AFTER INSERT" sur une vue :(
Et je ne vois pas comment passer les ID du trigger à l'appelant...

Si, à condition de faire l'INSERT dans une seule des tables de la vue….

Au lieu de nous communiquer des pseudo tables, donnez nous de vrais élément afin qu'on vous aide…. parce que une table d'une seule colonne de type ID autoincrémenté…. Je voit pas bien l'usage !

A +
25/02/2020, 17h29
Monstros Velu

Et pourtant, c'est ainsi qu'est modelée la base : Les entités (anchor) sont juste des tables avec un Id, et les attributs sont dans des tables séparées. Je peux avoir 10 attributs sur une entité, j'aurai 11 tables (1 pour l'entité et 10, une pour chaque attributs). La seule différence avec le pseudo modèle de l'exemple, c'est que j'ai des temporal tables.
26/02/2020, 13h30
escartefigue

Bonjour,

Quel intérêt de créer une table par colonne :weird: ?
Dans votre exemple, avec 10 attributs, il faut faire 11 jointures si l'on veut récupérer les valeurs de toutes les colonnes :aie: et si je veux joindre le résultat avec une autre table de 20 colonnes ... :aie::aie::aie:
26/02/2020, 14h50
SQLpro

Citation:

Envoyé par escartefigue

Bonjour,

Quel intérêt de créer une table par colonne :weird: ?
Dans votre exemple, avec 10 attributs, il faut faire 11 jointures si l'on veut récupérer les valeurs de toutes les colonnes :aie: et si je veux joindre le résultat avec une autre table de 20 colonnes ... :aie::aie::aie:

Non, c'est pas débile…. Au contraire ! Mais j'aurais mis la dedans au moins la date de création métier…. et non la table d'INSERT de la table temporelle.

A +
26/02/2020, 16h18
escartefigue

Peut être mais des explications m'intéressent en ce cas :weird:
Même avec un méta-modèle, on n'arrive pas à une situation aussi extrême

Exemple (que je donne dans tous mes cours) :

table mal modélisée :

Code:

1
2
3
4
5
CREATE TABLE T_EMP (EMP_ID            INT IDENTITY PRIMARY KEY,
                    EMP_NOM           CHAR(32),
                    EMP_PRENOM        VARCHAR(25),
                    EMP_D_NAISSANCE   CHAR(32));
GO

Si je veut éviter TOUS null, cette modélisation ne convient pas ! En effet, je ne sais pas si je vais n'insérer que 1 nom où bien que un prénom où bien que une date de naissance !

=> bonne modélisation :

Code:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE T_EMP     (EMP_ID            INT IDENTITY PRIMARY KEY);
GO
CREATE TABLE T_EMP_NOM (EMP_ID            INT PRIMARY KEY REFERENCES T_EMP (EMP_ID),
                        EMP_NOM           CHAR(32) NOT NULL);
GO
CREATE TABLE T_EMP_PRE (EMP_ID            INT PRIMARY KEY REFERENCES T_EMP (EMP_ID),
                        EMP_PRENOM        VARCHAR(25) NOT NULL);
GO
CREATE TABLE T_EMP_DN  (EMP_ID            INT PRIMARY KEY REFERENCES T_EMP (EMP_ID),
                        EMP_D_NAISSANCE   CHAR(32) NOT NULL);
GO

+ la vue misajourable ! Et dans ce dernier cas je rajoute la date de création "métier", ce peut d'ailleurs être la date de reprise d'un logiciel à l'autre...

A +

26/02/2020, 16h29
Monstros Velu

Citation:

Envoyé par SQLpro

Mais j'aurais mis la dedans au moins la date de création métier….

Il y a un statut qui contient les libellés "Valide", "Archivé", etc. : C'est le passage à "Valide" qui détermine la date de création métier.
26/02/2020, 16h32
Monstros Velu

Citation:

Envoyé par SQLpro

+ la vue misajourable !

Et c'est bien là que j'ai un problème pour récupérer les id créés pour être utilisé ailleurs ;)

Du coup, je suis en train de tout modifier pour passer par des procédures stockées au lieu de faire la mise à jour par des vues... Mais est-ce la bonne solution ? En tout cas, c'est un peu décevant...

Avec la base suivante :

Code:

1
2
3
4
5
CREATE DATABASE DB_TEST;
GO
 
USE DB_TEST;
GO

et le modèle de table ci-après :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE T_EMP     (EMP_ID            INT IDENTITY PRIMARY KEY,
                        EMP_VALIDE        BIT DEFAULT 0,
                        EMP_ARCHIVE       BIT DEFAULT 0);
GO
CREATE TABLE T_EMP_NOM (EMP_ID            INT PRIMARY KEY REFERENCES T_EMP (EMP_ID),
                        EMP_NOM           CHAR(32) NOT NULL);
GO
CREATE TABLE T_EMP_PRE (EMP_ID            INT PRIMARY KEY REFERENCES T_EMP (EMP_ID),
                        EMP_PRENOM        VARCHAR(25) NOT NULL);
GO
CREATE TABLE T_EMP_DN  (EMP_ID            INT PRIMARY KEY REFERENCES T_EMP (EMP_ID),
                        EMP_D_NAISSANCE   DATE NOT NULL);
GO

la vue qui va bien :

Code:

1
2
3
4
5
6
7
8
CREATE VIEW V_EMP
AS
SELECT E.*, EMP_NOM, EMP_PRENOM, EMP_D_NAISSANCE
FROM   T_EMP AS E
       LEFT OUTER JOIN T_EMP_NOM AS N ON E.EMP_ID = N.EMP_ID
       LEFT OUTER JOIN T_EMP_PRE AS P ON E.EMP_ID = P.EMP_ID
       LEFT OUTER JOIN T_EMP_DN  AS D ON E.EMP_ID = D.EMP_ID
GO

Le trigger est le 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
25
26
27
28
29
30
31
32
CREATE TRIGGER E_IOI_EMP
ON V_EMP
INSTEAD OF INSERT
AS
 
SET NOCOUNT ON;
 
SELECT *
INTO   #INSERTED
FROM   inserted;
 
INSERT INTO T_EMP 
SELECT EMP_VALIDE, EMP_ARCHIVE
FROM   #INSERTED;
 
DECLARE @EMP_ID INT = SCOPE_IDENTITY();
 
INSERT INTO T_EMP_NOM
SELECT @EMP_ID, EMP_NOM
FROM   #INSERTED
WHERE  EMP_NOM IS NOT NULL;
 
INSERT INTO T_EMP_PRE
SELECT @EMP_ID, EMP_PRENOM
FROM   #INSERTED
WHERE  EMP_PRENOM IS NOT NULL;
 
INSERT INTO T_EMP_DN
SELECT @EMP_ID, EMP_D_NAISSANCE
FROM   #INSERTED
WHERE  EMP_D_NAISSANCE IS NOT NULL;
GO

test :

Code:

1
2
INSERT INTO V_EMP (EMP_VALIDE, EMP_ARCHIVE, EMP_NOM, EMP_PRENOM, EMP_D_NAISSANCE)
VALUES (0, 0, 'DUPONT', 'Marc', NULL);

Si tu souhaite INSERER de multiple lignes simultanément, il y a une petite subtilité… Il faut rajouter une colonne discriminante à ta colonne de tête, qui se servira qu'a l'insertion. Ce peut être un nombre de type ROW_NUMBER ou bien un GUID par exemple… Cette colonne publiée dans la vue sera toujours nulle au final

A +

26/02/2020, 17h29
Monstros Velu

J'ai testé le script, et quand je fais

Code:

SELECT SCOPE_IDENTITY()

Je reçois la valeur NULL, alors que j'espère recevoir la valeur insérée par l'identity.

Or j'ai besoin de récupérer cet Id pour la suite du traitement (le référencer dans une autre table)

Et je dois effectivement insérer plusieurs lignes à la fois, et récupérer leurs Id aussi...
26/02/2020, 17h55
SQLpro
ha ben non…. Scope IDENTITY a été absorbé dans la proc…. puisque c'est la dernière table insérée qui a la valeur or les 3 tables filles n'ont pas d'ID !

Il faut que tu résolve ceci par :
- soit en codant TOUT dans le déclencheur
- soit une procédure appelée par le déclencheur, que tu peut utiliser en toute autonomie (moi c'est ce que je fais en général)
A +
26/02/2020, 18h12
Monstros Velu

Je vais donc utiliser des procédures stockées, c'est aussi ce que j'avais trouvé de plus pertinent dans ce cas.

Merci :)
26/02/2020, 18h24
SQLpro
Et dans tes triggers INSTEAD OFF tu reroute les INSERT dans la proc stock….

2 façon :
- soit ta proc est monoligne et donc tu utilises un curseur sur inserted dans ton déclencheur pour insérer ligne par ligne au cas ou
- soit ta proc permet d'absorber un lot de ligne (via un doc XML) et tu construit un XML à partir de inserted via la clause FOR XML AUTO…
A +
27/02/2020, 08h59
escartefigue
Citation:
Envoyé par SQLpro

Exemple (que je donne dans tous mes cours) :
=> bonne modélisation :

Code:

1 2 3 CREATE TABLE T_EMP_DN (EMP_ID INT PRIMARY KEY REFERENCES T_EMP (EMP_ID), EMP_D_NAISSANCE CHAR(32) NOT NULL);
Heu, parler de bonne modélisation avec une date de naissance de type char :aie: j'espère que c'est une erreur de copier/coller ;)

Sinon, je trouve que c'est pousser le bouchon très très loin que de modéliser ainsi et l'intérêt fonctionnel est extrêmement limité voire aberrant : on autorise par exemple avec un tel modèle, d'enregistrer une date de naissance sans nom ni prénom...

Donc je repose ma question : quel est l'intérêt, non pas technique, mais fonctionnel ?
27/02/2020, 14h07
aieeeuuuuu
bonjour,

Il y a une astuce pour éviter tout ce bazar.

Il suffit de s'appuyer sur le fait que la clause OUTPUT de la commande MERGE accepte de spécifier les données de la source de fusion.

Il suffit donc d'utiliser un MERGE (même pour un simple INSERT ..; vous étiez bien parti !), et dans la clause OUTPUT, quelque chose comme :
Code:

1 2 OUTPUT INSERTED.id, SOURCE.* INTO UneTableTemporaire
ça à l'avantage de mettre les bon id autogénérés sur les bonnes lignes, sans avoir à passer par une colonne tierce pour l'identification

il ne reste ensuite plus qu'à insérer le contenu de la table temporaire dans la table fille

Pas trop le temps pour un exemple complet, mais j'essaierai de poster ça un peu plus tard.
27/02/2020, 15h44
SQLpro

Citation:

Envoyé par escartefigue

Heu, parler de bonne modélisation avec une date de naissance de type char :aie: j'espère que c'est une erreur de copier/coller ;)

OUI !!!!!

Je corrige….
27/02/2020, 15h46
SQLpro
Citation:
Envoyé par aieeeuuuuu

bonjour,

Il y a une astuce pour éviter tout ce bazar.

Il suffit de s'appuyer sur le fait que la clause OUTPUT de la commande MERGE accepte de spécifier les données de la source de fusion.

Il suffit donc d'utiliser un MERGE (même pour un simple INSERT ..; vous étiez bien parti !), et dans la clause OUTPUT, quelque chose comme :

Code:

1 2 OUTPUT INSERTED.id, SOURCE.* INTO UneTableTemporaire

ça à l'avantage de mettre les bon id autogénérés sur les bonnes lignes, sans avoir à passer par une colonne tierce pour l'identification

il ne reste ensuite plus qu'à insérer le contenu de la table temporaire dans la table fille

Pas trop le temps pour un exemple complet, mais j'essaierai de poster ça un peu plus tard.
Non, ça marche pas, car comme tu n'as pas d'identifiant tu n'a aucune certitude de l'ordre des lignes… Imagine que tu insère deux Dupont…

A +

Voir 40 message(s) de cette discussion en une page