CTE sur plusieurs tables et insertion

**Kropernic** · 28/01/2014, 13h12

Bonjour,

Je sais qu'il est possible de faire des insert/update/delete sur une CTE "simple" (ne portant que sur une seule table).

Qu'en est-il des CTE "complexes" (portant sur plusieurs table à l'aide de clause(s) JOIN) ?

De base, je me dis que non. Que c'est un peu comme pour les vues pour lesquelles il faut mettre en place le ~~trigger~~déclencheur (parlons français^^) adéquat.

Maintenant, vu qu'il y a encore tout un tas de commandes/ordres que je ne connais pas, je me dis qu'il y a peut-être un truc.

**Sergejack** · 28/01/2014, 13h50

Je n'en suis pas sûr qu'on puisse utiliser une CTE "simple" pour l'insert.
Mais, vous pouvez le faire avec un DELETE ... FROM ..., un UPDATE ... FROM et le MERGE (dans lequel vous pouvez faire des insertions).

Concernant les CTE complexes, je suis quasi sûr que ça ne passe pas.

**mikedavem** · 28/01/2014, 13h52

Les vues peuvent être mise à jour directement sans forcément passer par un trigger à condition bien sûr de respecter les prérequis nécessaires

C'est la même chose pour les CTE comportant une ou plusieurs jointures.

++

**mikedavem** · 28/01/2014, 14h02

Par exemple pour un update :

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
40
41
42
create table #t1
(
 id int primary key,
 col1 char(2)
)
 
create table #t2
(
 id int primary key,
 col2 char(2)
)
 
create table #t3
(
 id int primary key,
 col3 char(2)
)
 
 
 
insert #t1 values (1, 't1')
insert #t1 values (2, 't2')
insert #t2 values (1, 't2')
insert #t3 values (1, 't3')
 
with cte
as
(
	select 
		t1.id,
		t1.col1,
		t2.col2
	from #t1 as t1
		left join #t2 as t2
			on t1.id = t2.id
		left join #t3 as t3
			on t2.id = t3.id
 
)
update cte
set col1 = 2
where id = 1;

Pour les insertions effectivement les cte il faudra que les valeurs insérées concernent uniquement les colonnes que d'une seule table.

++

**Kropernic** · 28/01/2014, 16h39

C'est bien ce que je pensais. Sniff...

J'avais espéré pouvoir faire "d'un coup" une insertion dans une table mère et fille histoire de pas devoir m'emmerder à récupérer les id's nouvellement insérés dans la table mère...

Pour info, voici la structure en question :

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
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
USE INNO_ADMIN;
GO
 
CREATE TABLE S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(
    PEM_ID            INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    PEM_CREATED_ON    DATETIME2 NOT NULL DEFAULT GETDATE(),
    PEM_CREATED_BY    VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO
 
CREATE TABLE S_OPERATIONAL.T_REAL_EMPLOYEE_REM(
    PEM_ID            INT NOT NULL PRIMARY KEY REFERENCES S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(PEM_ID),
    EMP_ID            INT NOT NULL UNIQUE REFERENCES S_HR.T_EMPLOYEE_EMP(EMP_ID),
    REM_CREATED_ON    DATETIME2 NOT NULL DEFAULT GETDATE(),
    REM_CREATED_BY    VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO
 
CREATE TABLE S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM(
    PEM_ID            INT NOT NULL PRIMARY KEY REFERENCES S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(PEM_ID),
    STR_ID            TINYINT NOT NULL REFERENCES dbo.T_STORE_STR(STR_ID),
    FEM_CREATED_ON    DATETIME2 NOT NULL DEFAULT GETDATE(),
    FEM_CREATED_BY    VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO
 
CREATE TRIGGER TRG_INS_T_REAL_EMPLOYEE_REM
    ON    S_OPERATIONAL.T_REAL_EMPLOYEE_REM
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE
        @ERRNO    INT,
        @ERRMSG    VARCHAR(255)
 
    /* LA CLEF DE T_PLANIFIED_EMPLOYEE_PEM DOIT EXISTER POUR L'INSERTION DANS T_REAL_EMPLOYEE_REM */
    IF NOT EXISTS(    SELECT    *
                    FROM    S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM PEM
                                INNER JOIN INSERTED INS
                                    ON    PEM.PEM_ID = INS.PEM_ID)
    BEGIN
        SET @ERRNO = 30002
        SET @ERRMSG = 'Clef de T_PLANIFIED_EMPLOYEE_PEM inconnue.  Insertion dans T_REAL_EMPLOYEE_REM impossible.'
        GOTO LBL_ERROR
    END
 
    /*GESTION D'ERREURS*/
    LBL_ERROR:
    RAISERROR @ERRNO @ERRMSG
    ROLLBACK TRANSACTION
END
GO
 
CREATE TRIGGER TRG_INS_T_FICTITIOUS_EMPLOYEE_FEM
    ON    S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE
        @ERRNO    INT,
        @ERRMSG    VARCHAR(255)
 
    /* LA CLEF DE T_PLANIFIED_EMPLOYEE_PEM DOIT EXISTER POUR L'INSERTION DANS T_FICTITIOUS_EMPLOYEE_FEM */
    IF NOT EXISTS(    SELECT    *
                    FROM    S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM PEM
                                INNER JOIN INSERTED INS
                                    ON    PEM.PEM_ID = INS.PEM_ID)
    BEGIN
        SET @ERRNO = 30002
        SET @ERRMSG = 'Clef de T_PLANIFIED_EMPLOYEE_PEM inconnue.  Insertion dans T_FICTITIOUS_EMPLOYEE_FEM impossible.'
        GOTO LBL_ERROR
    END
 
    /*GESTION D'ERREURS*/
    LBL_ERROR:
    RAISERROR @ERRNO @ERRMSG
    ROLLBACK TRANSACTION
END

(pas encore testé, ni exécuter, suis encore en train de réfléchir dessus pour voir si je n'ai rien oublié).

Mon plus gros souci étant que je ne sais pas comment faire des insertions ensembliste sur la table mère car elle ne contient qu'une colonne significative et elle est auto-incrémentée...

Ce qui donne une requête d'insertion comme suit :

Code :

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

INSERT INTO S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM DEFAULT VALUES;

Mais cela n'insère qu'une seule ligne... Comment faire un "insert into matable(col1,col2,etc)select...from..." où rien n'a besoin d'être réellement sélectionné. La seule chose importante, est le nombre de ligne à créer.

Je ne sais pas si je suis clair

**aieeeuuuuu** · 29/01/2014, 13h07

Bonjour

Envoyé par Kropernic

(pas encore testé, ni exécuter, suis encore en train de réfléchir dessus pour voir si je n'ai rien oublié).

Je dirai même qu'il y en a trop

- Soit j'ai loupé quelque chose, soit vos triggers sont inutiles, puisqu'ils ne font que vérifier l’intégrité référentielle... la déclaration de la clef étrangère entre vos tables filles et la table mère me parait donc suffisante.
- avec les infos que l'on a, les colonnes REM_CREATED_ON et REM_CREATED_BY me semblent de trop dans les tables filles.

Envoyé par Kropernic

Mon plus gros souci étant que je ne sais pas comment faire des insertions ensembliste sur la table mère car elle ne contient qu'une colonne significative et elle est auto-incrémentée...

Vous pourriez utilisez une des deux autres colonnes, par exemple la date (même si elle a une valeur par defaut

Code :

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

1
2
3
4
 
INSERT INTO Mere
    SELECT GETDATE()
    FROM ...

Une alternative intéressante, c'est d'utiliser un MERGE. elle présente l'avantage de pouvoir spécifier les valeur par défaut, mais également de pouvoir récupérer les valeurs de clef créée directement associées aux lignes à insérer dans la table fille.

Vou spourriez par exemple créer ce trigger dans les tables filles :

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
 
 
CREATE TRIGGER S_OPERATIONAL.TRG_EMP_CREA
ON S_OPERATIONAL.T_REAL_EMPLOYEE_REM
INSTEAD OF INSERT
AS
BEGIN
 
	DECLARE @tmp TABLE(PEM_ID INT NOT NULL, EMP_ID INT NOT NULL)
 
	--insertion des clefs dans a table mere
	MERGE INTO S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM
	USING INSERTED i
		ON 1 = 0
	WHEN NOT MATCHED BY TARGET THEN
		INSERT DEFAULT VALUES
	OUTPUT INSERTED.PEM_ID, i.EMP_ID INTO @tmp(PEM_ID, EMP_ID)
		;
 
	--insertion dans la table fille	
	INSERT INTO S_OPERATIONAL.T_REAL_EMPLOYEE_REM(PEM_ID, EMP_ID)
		SELECT PEM_ID, EMP_ID
		FROM @tmp
 
END

Vous pouvez alors directement insérer dans les tables filles :

Code :

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

1
2
 
INSERT INTO S_OPERATIONAL.T_REAL_EMPLOYEE_REM(emp_id) VALUES(20),(30)

Attention, le trigger ci-dessus n'est que pour l'exemple, et est à compléter. Il ne gère par exemple pas le cas où PEM_ID est spécifié lors de l'insertion...

**Kropernic** · 29/01/2014, 13h30

Envoyé par aieeeuuuuu

Bonjour

Je dirai même qu'il y en a trop

- Soit j'ai loupé quelque chose, soit vos triggers sont inutiles, puisqu'ils ne font que vérifier l’intégrité référentielle... la déclaration de la clef étrangère entre vos tables filles et la table mère me parait donc suffisante.
- avec les infos que l'on a, les colonnes REM_CREATED_ON et REM_CREATED_BY me semblent de trop dans les tables filles.

Je sors de réunion et je réponds en vitesse avant de partir manger.

Je me suis effectivement planté de bout de code lors de copier/coller depuis l'artice de sqlpro sur l'héritage exclusif

. Ces triggers sont donc sensés vérifier que la valeur de PEM_ID que j'insère dans une table fille n'est pas déjà utilisé dans une autre.

Pour les colonnes de tracking dans les tables filles, c'est car un employé fictif peut devenir un employé réel.

Je dois y aller (mon boss m'attend^^). Plus de détails après manger XD

**Kropernic** · 29/01/2014, 14h29

Donc comme je disais, je me suis planté de code lors de mon copier/coller (enfin je ne copie/colle pas mais vous avez compris).

Voici donc le code avec les triggers corrigés :

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
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
USE INNO_ADMIN;
GO
 
CREATE TABLE S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(
    PEM_ID            INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    PEM_CREATED_ON    DATETIME2 NOT NULL DEFAULT GETDATE(),
    PEM_CREATED_BY    VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO
 
CREATE TABLE S_OPERATIONAL.T_REAL_EMPLOYEE_REM(
    PEM_ID            INT NOT NULL PRIMARY KEY REFERENCES S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(PEM_ID),
    EMP_ID            INT NOT NULL UNIQUE REFERENCES S_HR.T_EMPLOYEE_EMP(EMP_ID),
    REM_CREATED_ON    DATETIME2 NOT NULL DEFAULT GETDATE(),
    REM_CREATED_BY    VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO
 
CREATE TABLE S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM(
    PEM_ID            INT NOT NULL PRIMARY KEY REFERENCES S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(PEM_ID),
    STR_ID            TINYINT NOT NULL REFERENCES dbo.T_STORE_STR(STR_ID),
    FEM_CREATED_ON    DATETIME2 NOT NULL DEFAULT GETDATE(),
    FEM_CREATED_BY    VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO
 
CREATE TRIGGER TRG_INS_T_REAL_EMPLOYEE_REM
    ON    S_OPERATIONAL.T_REAL_EMPLOYEE_REM
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE
        @ERRNO    INT,
        @ERRMSG    VARCHAR(255)
 
    /* LA VALEUR DE PEM_ID NE DOIT PAS ETRE UTILISEE DANS UNE AUTRE TABLE FILLE */
    IF EXISTS(    SELECT    
                        *
                FROM    
                        S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM
                WHERE    
                        PEM_ID IN (    SELECT 
                                            PEM_ID
                                    FROM   
                                            INSERTED))
    BEGIN
        SET @errno  = 30002
        SET @errmsg = 'Clef de T_PLANIFIED_EMPLOYEE_PEM déjà utilisée ailleurs. Insertion dans T_REAL_EMPLOYEE_REM impossible.'
        GOTO LBL_ERROR
    END
 
    /*GESTION D'ERREURS*/
    LBL_ERROR:
    RAISERROR @ERRNO @ERRMSG
    ROLLBACK TRANSACTION
END
GO
 
CREATE TRIGGER TRG_INS_T_FICTITIOUS_EMPLOYEE_FEM
    ON    S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE
        @ERRNO    INT,
        @ERRMSG    VARCHAR(255)
 
    /* LA VALEUR DE PEM_ID NE DOIT PAS ETRE UTILISEE DANS UNE AUTRE TABLE FILLE */
    IF EXISTS(    SELECT    
                        *
                FROM        
                        S_OPERATIONAL.T_REAL_EMPLOYEE_REM
                WHERE    
                        PEM_ID IN (    SELECT 
                                            PEM_ID
                                    FROM   
                                            INSERTED))
    BEGIN
        SET @errno  = 30002
        SET @errmsg = 'Clef de T_PLANIFIED_EMPLOYEE_PEM déjà utilisée ailleurs. Insertion dans T_FICTITIOUS_EMPLOYEE_FEM impossible.'
        GOTO LBL_ERROR
    END
 
    /*GESTION D'ERREURS*/
    LBL_ERROR:
    RAISERROR @ERRNO @ERRMSG
    ROLLBACK TRANSACTION
END

Je vais maintenant regarder le reste des remarques faites.

**Kropernic** · 29/01/2014, 15h00

Envoyé par aieeeuuuuu

- avec les infos que l'on a, les colonnes REM_CREATED_ON et REM_CREATED_BY me semblent de trop dans les tables filles.

J'y ai déjà brièvement répondu mais je vais compléter. Si les employées restaient toujours réels ou fictifs, les colonnes de tracking dans les tables filles ne seraient effectivement pas nécessaire. Mais ce n'est pas le cas. Un employé fictif est TOUJOURS (normalement) destiné à devenir réel. Cela s'intègre en fait dans une application de planification d'horaire pour le personnel d'un magasin. Hors, pour une période de forte affluence (comme les soldes actuellement par exemple), le magasin sait à l'avance qu'il va engager X personnes (étudiants, temporaires, etc.) supplémentaires mais il ne sait parfois qui se sera réellement que un ou deux jours avant que ces personnes arrive. Cad que le contrat ne sera réellement signé que quelques jours avant le début de la période de travail. Or, les horaires doivent être fait 4 semaines à l'avance. Vu que la liste des employés (réels) provient du service RH, il est logique que les personnes dont les contrats ne sont pas signés ne soient pas connus par l'application. D'où le fait qu'on essaie à présent de mettre en place un système d'employés fictifs afin de pouvoir les planifier pour ensuite, transformer un employé fictif en employé réel. Et vu que je veux pouvoir savoir qui a fait quoi, je mets donc les colonnes de tracking dans les tables filles (comme ça vous savez tout ^^).

Par contre, en écrivant ceci, que ce serait pas mal d'avoir une 3e table qui conserverait les employés fictifs qui sont devenus réels histoire d'avoir un historique. En gros, ce serait un clone de table actuel des fictifs mais qui contiendrait les lignes supprimées.
N.B.: j'ai oublié une colonne nom dans la table des employés fictifs

Envoyé par aieeeuuuuu

Vous pourriez utilisez une des deux autres colonnes, par exemple la date (même si elle a une valeur par defaut

Code :

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

1
2
3
4
 
INSERT INTO Mere
    SELECT GETDATE()
    FROM ...

J'y ai pensé mais j'aime pas

.

Envoyé par aieeeuuuuu

Une alternative intéressante, c'est d'utiliser un MERGE. elle présente l'avantage de pouvoir spécifier les valeur par défaut, mais également de pouvoir récupérer les valeurs de clef créée directement associées aux lignes à insérer dans la table fille.

Vou spourriez par exemple créer ce trigger dans les tables filles :

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
 
 
CREATE TRIGGER S_OPERATIONAL.TRG_EMP_CREA
ON S_OPERATIONAL.T_REAL_EMPLOYEE_REM
INSTEAD OF INSERT
AS
BEGIN
 
    DECLARE @tmp TABLE(PEM_ID INT NOT NULL, EMP_ID INT NOT NULL)
 
    --insertion des clefs dans a table mere
    MERGE INTO S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM
    USING INSERTED i
        ON 1 = 0
    WHEN NOT MATCHED BY TARGET THEN
        INSERT DEFAULT VALUES
    OUTPUT INSERTED.PEM_ID, i.EMP_ID INTO @tmp(PEM_ID, EMP_ID)
        ;
 
    --insertion dans la table fille    
    INSERT INTO S_OPERATIONAL.T_REAL_EMPLOYEE_REM(PEM_ID, EMP_ID)
        SELECT PEM_ID, EMP_ID
        FROM @tmp
 
END

Vous pouvez alors directement insérer dans les tables filles :

Code :

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

1
2
 
INSERT INTO S_OPERATIONAL.T_REAL_EMPLOYEE_REM(emp_id) VALUES(20),(30)

Attention, le trigger ci-dessus n'est que pour l'exemple, et est à compléter. Il ne gère par exemple pas le cas où PEM_ID est spécifié lors de l'insertion...

Je préfère nettement cette solution. Le souci est que je ne maitrise pas du tout l'instruction MERGE pour le moment. Je comprends à peine comment elle fonctionne

. Ce serait possible de détailler l'instruction MERGE que vous avez écrite ? Déjà, quand je vois "ON 1 = 0", je me dis WTF ?!

**aieeeuuuuu** · 29/01/2014, 15h45

Envoyé par Kropernic

Ce serait possible de détailler l'instruction MERGE que vous avez écrite ? Déjà, quand je vois "ON 1 = 0", je me dis WTF ?!

comme je disais, c'était pour l'exemple...

Comme son nom l'indique, l'instruction merge a pour but de fusionner deux tables, soit mettre à jour une table en fonction d'une autre. Le principal intérêt étant qu'elle permet de faire en une seule instruction les INSERT, UPDATE et DELETE.

Son principe est simple : on compare la table à mettre à jour avec une table source en fonction d'une condition de jointure. Ensuite, on spécifie les actions a effectuer selon les cas :
1 - La ligne existe dans les deux tables
2 - la ligne existe dans la source, mais pas dans la cible
3 - la ligne existe dans la cible, mais pas dans la source.

on peux en outre ajouter des conditions supplémentaires pour chacun de ces cas.

Ici, j'ai un peu dérivé du cas typique d'utilisation de cette instruction, car elle permet d'insérer les valeurs par défaut, et en plus, elle permet de récupérer avec la clause OUTPUT des données de la source qui ne sont en fait pas insérées dans la cible : ici, ça permet d'affecter directement les clef auto-incrémenté à une ligne de la table fille...

Pour ce qui est du ON 1 = 0, c'est simplement que dans le cas présent, on insère toutes les lignes de la source dans la cible. Mais il faudrait plutôt mettre ceci dans la condition de jointure : ON T_PLANIFIED_EMPLOYEE_PEM.PEM_ID = i.PEM_ID, ce qui vous permettra justement de gérer les cas où le PEM_ID est spécifié lors de l''insertion dans la table fille, et qu'il existe déjà dans la table mère. Vous pourrez alors gérer avec un WHEN MATCHED

**Kropernic** · 29/01/2014, 15h49

Va vraiment falloir que j'expérimente parce que c'est à peine plus clair XD.

Sur le principe, j'ai bien compris à quoi ça servait. C'est avec son fonctionnement que j'ai du mal. Je pense que ça viendra avec la pratique. D'où les expériences nécessaires ^^.

Quoi qu'il en soit, merci !

**Kropernic** · 30/01/2014, 16h26

Hello,

En utilisant le trigger instead of insert proposé par aieeuuuu, je commence tout doucement à comprendre MERGE.

J'ai commencé à le modifier comme ceci :

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
ALTER TRIGGER TRG_INS_DAUGHTER1 ON DAUGHTER1
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @TMP TABLE(MOTHER_ID INT NOT NULL, VALUE INT NOT NULL)
 
    --INSERTION DES CLEFS DANS LA TABLE MERE
    MERGE INTO MOTHER
    USING INSERTED I
        ON (MOTHER.MOTHER_ID = I.MOTHER_ID)    
    WHEN NOT MATCHED BY TARGET THEN
        INSERT DEFAULT VALUES
        OUTPUT INSERTED.MOTHER_ID, I.VALUE INTO @TMP(MOTHER_ID, VALUE);
 
    --INSERTION DANS LA TABLE FILLE
    INSERT INTO DAUGHTER1(MOTHER_ID, VALUE)
    SELECT    MOTHER_ID, VALUE
    FROM    @TMP;
END

Jusque là, il donne exactement (du moins jusqu'à présent) le même résultat que celui de aieeuuu (encore heureux, je n'ai modifié que la clause ON).

Par contre, si j'essaie d'ajouter une clause matched, c'est le bordel.
Voici donc mon trigger modifié :

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
ALTER TRIGGER TRG_INS_DAUGHTER1 ON DAUGHTER1
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @TMP TABLE(MOTHER_ID INT NOT NULL, VALUE INT NOT NULL)
 
    --INSERTION DES CLEFS DANS LA TABLE MERE
    MERGE INTO MOTHER
    USING INSERTED I
        ON (MOTHER.MOTHER_ID = I.MOTHER_ID)    
    WHEN MATCHED THEN
        INSERT INTO @TMP(MOTHER_ID, VALUE) VALUES(INSERTED.MOTHER_ID,INSERTED.VALUE)
    WHEN NOT MATCHED  THEN
        INSERT DEFAULT VALUES
        OUTPUT INSERTED.MOTHER_ID, I.VALUE INTO @TMP(MOTHER_ID, VALUE);
 
    --INSERTION DANS LA TABLE FILLE
    INSERT INTO DAUGHTER1(MOTHER_ID, VALUE)
    SELECT    MOTHER_ID, VALUE
    FROM    @TMP;
END

Et voici le code des tables utilisées :

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
USE [TEST_MERGE]
GO
 
/****** Object:  Table [dbo].[MOTHER]    Script Date: 30/01/2014 16:24:39 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[MOTHER](
    [MOTHER_ID] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [MOTHER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

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
USE [TEST_MERGE]
GO
 
/****** Object:  Table [dbo].[DAUGHTER1]    Script Date: 30/01/2014 16:25:14 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[DAUGHTER1](
    [MOTHER_ID] [int] NOT NULL,
    [VALUE] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [MOTHER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[DAUGHTER1]  WITH CHECK ADD FOREIGN KEY([MOTHER_ID])
REFERENCES [dbo].[MOTHER] ([MOTHER_ID])
GO

Avez-vous aussi des erreurs de syntaxe avec ce code de trigger ?????

**aieeeuuuuu** · 30/01/2014, 16h45

en effet, c'est votre INSERT INTO @TMP(MOTHER_ID, VALUE) ... qui ne va pas : vous ne pouvez insérer que dans la table visée par le MERGE...

(En plus, il me semble que vous ne pouvez pas inserer dans la clause WHEN MATCHED)

Cependant, si j'ai bien compris, c'est pour insérer quand même les lignes dont MOTHER_ID renseigné lors de l'insertion dans la table fille. Dans ce cas, lors de l'INSERT final dans la table fille, faites un union avec INSERTED (en filtrant MOTHER_ID à NULL).

**Kropernic** · 30/01/2014, 16h52

Envoyé par aieeeuuuuu

en effet, c'est votre INSERT INTO @TMP(MOTHER_ID, VALUE) ... qui ne va pas : vous ne pouvez insérer que dans la table visée par le MERGE...

(En plus, il me semble que vous ne pouvez pas inserer dans la clause WHEN MATCHED)

Cependant, si j'ai bien compris, c'est pour insérer quand même les lignes dont MOTHER_ID renseigné lors de l'insertion dans la table fille. Dans ce cas, lors de l'INSERT final dans la table fille, faites un union avec INSERTED (en filtrant MOTHER_ID à NULL).

C'était tellement évident !!!
Voici donc le trigger final (jusqu'à ce que je trouve une bourde ou que sqlpro vienne me dire que je fais de la merde

):

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
ALTER TRIGGER TRG_INS_DAUGHTER1 ON DAUGHTER1
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @TMP TABLE(MOTHER_ID INT NOT NULL, VALUE INT NOT NULL)
 
    --INSERTION DES CLEFS DANS LA TABLE MERE
    MERGE INTO MOTHER
    USING INSERTED I
        ON (MOTHER.MOTHER_ID = I.MOTHER_ID)    
    WHEN NOT MATCHED  THEN
        INSERT DEFAULT VALUES
        OUTPUT INSERTED.MOTHER_ID, I.VALUE INTO @TMP(MOTHER_ID, VALUE);
 
    --INSERTION DANS LA TABLE FILLE
    INSERT INTO DAUGHTER1(MOTHER_ID, VALUE)
    SELECT    MOTHER_ID, VALUE
    FROM    @TMP;
 
    INSERT INTO DAUGHTER1(MOTHER_ID, VALUE)
    SELECT    MOTHER_ID, VALUE
    FROM    INSERTED
    WHERE    MOTHER_ID IS NOT NULL;
END

**Kropernic** · 30/01/2014, 17h00

Il y a quand même encore un léger souci.

Pourquoi la clause ON du MERGE n'est plus valide quand j'ajoute une clause MATCHED ? Car je ne dois malgré faire quelques petites opérations si ça correspond.. Bon je pourrais le faire hors du MERDE comme je le fais pour l'insertion mais j'aimerais quand même bien comprendre.

Qu'est-ce qui fait que cette clause ON qui ne pause aucun souci sans clause MATCHED devient tout à coup invalide lors que je l'ajoute ???

**aieeeuuuuu** · 30/01/2014, 17h14

en fait je vois bien un cas particulier :

Si Mother_id est spécifié, mais n'existe pas dans la table mère : il sera inséré avec un nouvel ID généré lors du MERGE, puis inséré une deuxième fois lors du dernier INSERT, avec violation de contrainte d'intégrité à la clef lors de ce deuxième insert...

**aieeeuuuuu** · 30/01/2014, 17h17

Envoyé par Kropernic

Il y a quand même encore un léger souci.

Pourquoi la clause ON du MERGE n'est plus valide quand j'ajoute une clause MATCHED ?

Non, c'était votre clause WHEN MATCHED qui n'était pas correcte ! Dans cette clause, vous ne pouvez spécifier qu'un UPDATE ou un DELETE dans la table cible

Envoyé par Kropernic

Car je ne dois malgré faire quelques petites opérations si ça correspond..

Lesquelles ?

**Kropernic** · 31/01/2014, 09h32

Envoyé par aieeeuuuuu

Non, c'était votre clause WHEN MATCHED qui n'était pas correcte ! Dans cette clause, vous ne pouvez spécifier qu'un UPDATE ou un DELETE dans la table cible

Lesquelles ?

Votre remarque précédente me fait réaliser que la clause matched ne permet de travailler que sur la table cible. Donc effectivement, je n'ai rien à y faire.

Cependant, je vous assure que lorsque j'ajoute une clause matched, SSMS me souligne la clause le prédicat de la clause ON me disant qu'il s'agit d'une expression non-booléenne et que ce doit être une expression booléenne. Pourtant, cette expression ne change pas... D'où mon incompréhension.

Ok je n'en ai pas besoin pour le cas actuel mais j'aimerais autant comprendre pour plus tard.

**aieeeuuuuu** · 31/01/2014, 10h32

Envoyé par Kropernic

Cependant, je vous assure que lorsque j'ajoute une clause matched, SSMS me souligne la clause le prédicat de la clause ON me disant qu'il s'agit d'une expression non-booléenne et que ce doit être une expression booléenne. Pourtant, cette expression ne change pas... D'où mon incompréhension.

Comme votre requête MERGE est fausse du coup, je pense que le parseur de SSMS s'y perd. (à mon avis, il considère votre INSERT comme une deuxième requete...)
D'ailleurs, si vous tentez d’exécuter la requête, cette erreur ne ressort pas.

CTE sur plusieurs tables et insertion [2008R2]

Développement SQL Server

Discussions similaires

Partager

Partager