CTE sur plusieurs tables et insertion

Version imprimable

28/01/2014, 13h12
Kropernic

CTE sur plusieurs tables et insertion

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.
28/01/2014, 13h50
Sergejack

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.
28/01/2014, 13h52
mikedavem

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.

++

Par exemple pour un update :

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

++

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

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:

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:

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

Bonjour

Citation:

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.

Citation:

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:

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:

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:

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

29/01/2014, 13h30
Kropernic

Citation:

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

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:

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.

Citation:

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

1 2 3 4 INSERT INTO Mere SELECT GETDATE() FROM ...
J'y ai pensé mais j'aime pas :oops:.
Citation:
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:

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:

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

29/01/2014, 15h45
aieeeuuuuu

Citation:

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 ?!

:lol:

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
29/01/2014, 15h49
Kropernic

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 !

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:

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:

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:

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:

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

30/01/2014, 16h45
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).

Citation:

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 :mouarf:):
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 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

30/01/2014, 17h00
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 ? 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 ???
30/01/2014, 17h14
aieeeuuuuu

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...
30/01/2014, 17h17
aieeeuuuuu

Citation:

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

Citation:

Envoyé par Kropernic

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

Lesquelles ?
31/01/2014, 09h32
Kropernic

Citation:

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.
31/01/2014, 10h32
aieeeuuuuu

Citation:

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.