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

CTE sur plusieurs tables et insertion [2008R2]

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager