IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Schéma Discussion :

Respect au niveau SQL des cardinalités 1,n du MCD merisien


Sujet :

Schéma

  1. #1
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut Respect au niveau SQL des cardinalités 1,n du MCD merisien
    Bonsoir,

    Je traite ici des cardinalités 1,n de Merise, lesquelles ne sont pas souvent respectées quand on déboule au niveau logique, c’est-à-dire dans l’univers des tables SQL.

    Prenons l’exemple suivant dans lequel des professeurs enseignent des matières, avec les contraintes suivantes :
     
    — un professeur enseigne au moins une matière ;
     
    — une matière est enseignée par au moins un professeur.
     
    MCD correspondant, créé avec Looping, gracieusement proposé par le professeur Patrick Bergougnoux (encore une fois, merci Paprick !) :
     
     
    Code SQL des tables correspondantes :
     
    Code SQL : 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
    CREATE TABLE Prof
    (
       profId SMALLINT NOT NULL
     , profMatricule CHAR(5) NOT NULL
     , profNom VARCHAR(24) NOT NULL
     , CONSTRAINT Prof_PK PRIMARY KEY(profId)
     , CONSTRAINT Prof_AK UNIQUE(profMatricule)
    );
     
    CREATE TABLE Matiere
    (
       matiereId SMALLINT NOT NULL
     , matiereCode VARCHAR(5) NOT NULL
     , matiereNom VARCHAR(24) NOT NULL
     , CONSTRAINT Matiere_PK PRIMARY KEY(matiereId)
     , CONSTRAINT Matiere_AK1 UNIQUE(matiereCode)
     , CONSTRAINT Matiere_AK2 UNIQUE(matiereNom)
    );
     
    CREATE TABLE Enseigner
    (
       profId SMALLINT NOT NULL
     , matiereId SMALLINT NOT NULL
     , CONSTRAINT Enseigner_PK PRIMARY KEY(profId, matiereId)
     , CONSTRAINT Enseigner_Prof_FK FOREIGN KEY(profId) REFERENCES Prof(profId)
     , CONSTRAINT Enseigner_Matiere_FK FOREIGN KEY(matiereId) REFERENCES Matiere(matiereId)
    );
     
    Pour que les contraintes soient systématiquement respectées, on ne peut pas se contenter de la séquence suivante :
     
    — (a) Créer des profs (table Prof) ;
     
    — (b) Créer des matières (table Matiere) ;
     
    — (c) Créer les liens profs/matières (table Enseigner).
     
    Pour ma part, je préfère en passer par des triggers.

    Par exemple, un 1er trigger aura pour objet de ventiler dans les tables les données présentées à une vue créée à cet effet.

    Soit ProfMatiere_V le nom de cette vue. Sa structure est définie selon le code suivant :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    create view ProfMatiere_V
    (profId, profMatricule, profNom, matiereId, matiereCode, matiereNom)
    as 
    select e.profId, profMatricule, profNom, e.matiereId, matiereCode, matiereNom 
    from Prof as p 
         join Enseigner as e on p.profId = e.profId
         join Matiere as m on e.matiereId = m.matiereId ;

    Quelques insertions dans la vue :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    insert into ProfMatiere_V
    values
       (1, 'fenau', 'Fernand', 1, 'phy', 'physique')
     , (1, 'fenau', 'Fernand', 2, 'chm', 'chimie')
     , (2, 'ravol', 'Raoul', 2, 'chm', 'chimie')
     , (2, 'ravol', 'Raoul', 3, 'maths', 'mathématiques')
     , (3, 'pat', 'Patricia', 4, 'mus', 'musique')
    ;
     
    Ainsi, Fernand enseigne la physique et la chimie, Raoul enseigne pour sa part les maths et la chimie, tandis que Patricia enseigne la musique. On constate à cette occasion certaines redondances, mais ceci serait l’objet d’un autre débat.

    Comme je n’ai plus accès à DB2 depuis au moins 20 ans, j’utilise ici SQL Server. Le trigger qui suit a pour mission de ventiler dans les tables concernées les lignes proposées à la vue.
     
    Code SQL : 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
    CREATE TRIGGER ProfMatiere_instead_Tr ON ProfMatiere_V INSTEAD OF INSERT AS
     
    begin
      declare @nProf as smallint ;
      declare @nMatiere as smallint ;
     
      declare @tableProf table 
              ( 
                  profId smallint
                , profMatricule varchar(24)
                , profNom varchar(24)
              ) ; 
      declare @tableMatiere table 
              ( 
                  matiereId smallint not null
                , matiereCode varchar(5) not null
                , matiereNom varchar(24) not null
              ) ; 
      declare @tableEnseigner table 
              ( 
                  profId smallint not null
                , matiereId smallint not null
              ) ; 
     
      insert into @tableProf
        select distinct i.profId, i.profMatricule, i.profNom 
        from inserted as i join Prof as p on i.profId = p.profId
     
      set @nProf = (select count(a.profId) 
                    from Prof as a join @tableProf as b on a.profId = b.profId
                   ) ;
     
    -- pour debug :
      print '@nProf = ' + cast(@nProf as varchar) ;
     
      if @nProf = 0
        begin ;
          disable trigger Prof_after_insert on Prof ;
          -- print 'insert into Prof' ;     
          insert into Prof 
            select distinct profId, profMatricule, profNom 
            from inserted ;
          enable trigger Prof_after_insert on Prof ;
        end
     
      insert into @tableMatiere
        select distinct i.matiereId, i.matiereCode, i.matiereNom 
        from inserted as i join Matiere as m on i.matiereId = m.matiereId
     
      set @nMatiere = (select count(a.matiereId) 
                       from Matiere as a 
                       join @tableMatiere as b on a.matiereId = b.matiereId
                      ) ;
     
    -- pour debug :
      print '@nMatiere = ' + cast(@nMatiere as varchar) ;
     
      if @nMatiere = 0
        begin ;
          disable trigger Matiere_after_insert on Matiere ;
          -- print 'insert into Matiere' ;     
          insert into Matiere 
            select distinct matiereId, matiereCode, matiereNom 
            from inserted ;
          enable trigger Matiere_after_insert on Matiere ;
        end
     
      insert into Enseigner
        select profId, matiereId
        from inserted ;
     
    print 'the end!' ;
     
    end

    Quelques commentaires :

    Le code est très vraisemblablement simplifiable, mais peu importe ici. L’idée est la suivante :

    Fernand fait l’objet de deux lignes et l’on ne peut pas le créer deux fois dans la table Prof, on se ferait jeter par le SGBD au motif de viol de la contrainte de clé primaire. On n’insère donc que la 1re ligne si Fernand n’existe pas encore dans la table, sinon on s’abstient.

    Concernant les matières (table Matiere), le principe est le même.

    Quant à la table Enseigner, on ne contrôle rien, le SGBD se chargera de faire respecter la contrainte de clé primaire (voire les contraintes d’intégrité référentielle).

    Le trigger qu’on vient de définir n’est pas le seul à mettre en oeuvre. En effet, Hilarion Lefuneste pourrait violer les règles, d’où la nécessité de définir un trigger de contrôle des inserts dans la table Prof et un 2e trigger pour contrôle les inserts dans la table Matiere.

    Exemple de trigger pour contrôler les inserts dans la table Prof :

    Code SQL : 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
    CREATE TRIGGER Prof_after_insert on Prof after insert as
    begin
      declare @n varchar(24) -- int ;
      declare @tableProfInserted table (profNom varchar(24)) ; 
      declare @errId as int = 314115  -- numéro de l'erreur à afficher
      declare @errTexte as varchar (255) -- message d'erreur
      declare @profNom as varchar(24)
     
      insert into @tableProfInserted (profNom)
        select distinct profNom  
        from inserted
        where profId not in (select profId from Enseigner) ;
     
      set @n = (select count(profNom) from @tableProfInserted) ;
      if @n > 0
        begin
          set @profNom = (select top (1) profNom from @tableProfInserted)
          set @errTexte = 
              char(13)
            + 'Un prof doit enseigner au moins une matière.' 
            + char(13)
            + 'Le prof '''
            + @profNom
            + ''' ne répond pas à cette obligation.' 
            + char(13)
          ; throw @errId, @errTexte, 16
        end ;
    end

    Exemple de trigger pour contrôler les inserts dans la table Matiere :

    Code SQL : 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
    CREATE TRIGGER Matiere_after_insert on Matiere after insert as
    begin
      declare @n varchar(24) -- int ;
      declare @tableMatiereInserted table (matiereNom varchar(24)) ; 
      declare @errId as int = 314116  -- numéro de l'erreur à afficher
      declare @errTexte as varchar (255) -- message d'erreur
      declare @matiereNom as varchar(24)
     
      insert into @tableMatiereInserted (matiereNom)
        select distinct matiereNom  
        from inserted
        where matiereId not in (select matiereId from Enseigner) ;
     
      set @n = (select count(matiereNom) from @tableMatiereInserted) ;
      if @n > 0
        begin
          set @matiereNom = (select top(1) matiereNom from @tableMatiereInserted)
          set @errTexte = 
              char(13)
            + 'Une matière doit être enseignée par au moins un prof.' 
            + char(13)
            + 'La matière '''
            + @matiereNom
            + ''' ne répond pas à cette obligation.' 
            + char(13)
          ; throw @errId, @errTexte, 16
        end ;
    end
     
    Mais ces deux triggers prennent le contrôle dès qu’on effectue un insert, aussi faut-il les désactiver (le plus brièvement possible, à cause d’Hilarion toujours à l’affût !) quand cet insert est effectué dans le trigger ProfMatiere_instead_Tr. A cet effet, on utilise l’instruction disable trigger juste avant insert et l’instruction enable trigger juste après l’insert. Ainsi, dans le cas de la table Prof :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    disable trigger Prof_after_insert on Prof ;
    insert into Prof 
      select distinct profId, profMatricule, profNom 
      from inserted ;
    enable trigger Prof_after_insert on Prof ;
     
    Après exécution de l’insert dans la vue ProfMatiere_V, au résultat :
     
    Table Prof :
     
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    Prof	profId	profMatricule	profNom
    	1	fenau		Fernand
    	2	ravol		Raoul
     
    Table Matiere :
     
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    Matiere	matiereId	matiereCode	matiereNom
    	1		phy		physique
    	2		chm		chimie
    	3		maths		mathématiques
     
    Table Enseigner :
     
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    Enseigner	profId	matiereId
    		1	1
    		1	2
    		2	2
    		2	3
     
     
    Hilarion tentera de violer les règles, avec des instructions du genre :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    insert into Prof values
      (3, 'paulo', 'Paul') ;
     
    Réaction du système :
     
    Msg 314115, Niveau 16, État 16, Procédure Prof_after_insert, Ligne 30 [Ligne de départ du lot 237]
     
    Un prof doit enseigner au moins une matière.
    Le prof 'Paul' ne répond pas à cette obligation.
     
    Dans le même genre :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    insert into Matiere values
      (99, 'info', 'informatique') ;
     
    =>
     
    Msg 314116, Niveau 16, État 16, Procédure Matiere_after_insert, Ligne 27 [Ligne de départ du lot 237]
     
    Une matière doit être enseignée par au moins un prof.
    La matière 'informatique' ne répond pas à cette obligation.
     
    Je n’ai fait ici qu’une 1re tentative à propos du respect des cardinalités 1,n/1,n, nul doute qu’Hilarion reviendra à la charge.

    J’ai essayé de traiter des insert, mais concernant les update et delete, le chantier reste bien entendu à ouvrir.
    Je n’ai pas traité du cas plus simple des cardinalités 1,n/1,1, mais ça viendra.

    Par ailleurs les triggers Prof_after_insert et Matiere_after_insert sont du type AFTER INSERT, l’alternative INSTEAD OF est à étudier.

    En tout cas, merci de votre indulgence...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  2. #2
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 799
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 799
    Points : 34 031
    Points
    34 031
    Billets dans le blog
    14
    Par défaut
    Bonsoir François,

    Je n'ai pas analysé ton code en détail.
    Il me semble que ce que tu proposes pour SQL Server n'est pas possible encore pour MySQL et MariaDb. Sauf méconnaissance des dernières évolutions de ma part, il est impossible sur ces SGBD d'écrire un insert sur une vue. Et sur PostgreSQL ? Je ne sais pas.

    La méthode que j'ai appliquée pour une application utilisant MariaDB était celle de la procédure d'insertion ou d'update, selon le même principe : avec les données passées à la procédure d'insertion, on commence par contrôler si le prof et la matière existent déjà dans les tables idoines en récupérant, le cas échéant, leur identifiant. Ensuite, on démarre une transaction. Si les deux sont déjà là, on se contente d'insérer dans la table associative, sinon on insère ce qui manque (le prof, la matière ou les deux) en récupérant leur identifiant généré par auto-incrément (LAST_INSERT_ID) puis on insère ces derniers dans la table associative. Enfin on commit si tout s'est bien passé, on rollback s'il y a un souci avec un beau message d'erreur en clair à l'attention du programme applicatif puis on ferme la transaction.

    Selon les données objets de l'insertion (parce que, bien, sûr le cas prof / matière est un exemple mais ça peut être plus complexe que ça) on en profite pour vérifier dans la procédure que les données ne soient pas déconnantes... une date de naissance du prof, par exemple ou, dans ton exemple, un matricule qui soit cohérent avec le format de celui-ci, voire en le générant automatiquement.

    On ne dit jamais assez combien ces procédures d'insertion, mise à jour, suppression de données peuvent être puissantes. Au lieu que ce soit le programme applicatif qui interroge la BDD pour savoir si le prof et la matière sont déjà là et si, par exemple, le matricule n'est pas déjà attribué à un autre prof, ce qui fait autant d'allers-retours entre le programme et la BDD, avec les risques que la vérification fait à l'instant N ne soit plus valable à l'instant N+1 parce qu'une autre donnée à été insérée, il suffit que le programme applicatif envoie les données à la procédure et c'est cette dernière qui fait le boulot. L'intégrité des données est bien mieux assurée et il n'y a qu'un aller-retour entre application et SGBD.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  3. #3
    Membre actif
    Profil pro
    Inscrit en
    Mars 2006
    Messages
    34
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mars 2006
    Messages : 34
    Points : 229
    Points
    229
    Par défaut
    Bonsoir,
    Le souci des cardinalités (1,n) est que, la plupart du temps, quand on creuse elles vont plutôt être d'ordre (0,n). Soit pour des raisons pratiques (par exemple, on peut se retrouver avec une vacance d'enseignant et donc avoir une matière enseignée par personne ; au contraire, on peut avoir un enseignant surnuméraire à qui il va falloir trouver un cours par un jeu de réaffectations), soit pour des raisons techniques (dans mon logiciel de planning, il faut bien que je crée d'abord l'enseignant, puis le cours, puis que je rattache l'un à l'autre).
    L'utilisation de triggers est très bien au niveau purement technique mais, dans la confrontation à la réalité, on peut se retrouver à avoir d'autres contraintes (par exemple, une fiche enseignant va nécessiter des informations qui vont au-delà de ce qui va être saisissable depuis l'enregistrement d'un cours).
    Après, j'admets qu'il y a des situations où on peut effectivement se retrouver avec des cardinalités (1,n), mais moins dans des modélisations du réel que dans des modélisations de mécanismes purement techniques (où on n'est pas liés à des considérations organisationnelles humaines ^^).
    En tout cas, merci pour le tuto et les modalités de mise en œuvre !

  4. #4
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 936
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 936
    Points : 4 356
    Points
    4 356
    Par défaut
    Une solution qui nécessite une étape DISABLE TRIGGER n'est pas une bonne solution.
    Pour info, avec ORACLE on peut résoudre ce genre de problème assez simplement un COMPOUND TRIGGER sur la table d'intersection, 2 tables qui contiendront les compteurs et initialisées par des triggers AFTER INSERT et les contraintes sur les cardinalités respectives qui seront définies DEFERRABLE INITIALLY DEFERRED, et c'est ainsi trivial d'implémenter une cardinalité différente pour chaque matière et/ou chaque professeur.
    Notez quand même que dans le monde réel, la relation d'intersection contiendrait plus que probablement une notion d'intervalle de temps qui rendrait l'exercice encore plus amusant...

  5. #5
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonsoir,

    Citation Envoyé par Karadoc
    Le souci des cardinalités (1,n) est que, la plupart du temps, quand on creuse elles vont plutôt être d'ordre (0,n).
    On est bien d’accord ! Après 50 ans de modélisation, mise en oeuvre des base de données, audits et barouds en tous genres dans tous les secteurs d’activité, j’ai essentiellement eu à me frotter aux cardinalités (0,n). Mais il arrive que les malheureuses cardinalités (1,n/1,n) se manifestent timidement, donc j’ai voulu voir ce qu’il en était dans leur mise en oeuvre à l’étage SQL. Les cardinalités 1,n dans le contexte (1,n/1,1) pour leur part ne sont pas rares, en effet elles s’imposent par exemple dans le cas des factures et de leurs lignes, car en l’occurrence les cardinalités (0,n/1,1) n’ont pas de sens.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  6. #6
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonsoir,

    Citation Envoyé par JeitEmgie
    Une solution qui nécessite une étape DISABLE TRIGGER n'est pas une bonne solution.
    Etape brève s'il en est, mais je souhaiterais qu’un spécialiste comme SQLpro fournisse une solution alternative.
     
    Citation Envoyé par JeitEmgie
    Notez quand même que dans le monde réel, la relation d'intersection contiendrait plus que probablement une notion d'intervalle de temps qui rendrait l'exercice encore plus amusant...
    Je suis désolé, mais la dernière fois que j’ai utilisé Oracle, c’était à la fin des années quatre-vingt...
    A part çà, qu’est-ce pour Oracle une relation d’intersection ? Dans le cadre du Modèle Relationnel de Données, je sais ce qu’est une relation, ce qu’est l’intersection de deux relations (r1 INTERSECT r2), mais sorti de là je donne ma langue au chat...
    Par ailleurs, quel rôle jouerait l’intervalle de temps dans cette affaire ? Ce rôle est-il comparable avec celui de l’intervalle de temps qui est étudié (paragraphes 6.3.2,6.3.3) dans le cadre du Modèle Relationnel de Données ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  7. #7
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonsoir Philippe,


    Citation Envoyé par CinePhil
    On ne dit jamais assez combien ces procédures d'insertion, mise à jour, suppression de données peuvent être puissantes.
    Hilarion ne pourra pas violer les règles ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  8. #8
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 936
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 936
    Points : 4 356
    Points
    4 356
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    Bonsoir,


    Etape brève s'il en est, mais je souhaiterais qu’un spécialiste comme SQLpro fournisse une solution alternative.
     


    Je suis désolé, mais la dernière fois que j’ai utilisé Oracle, c’était à la fin des années quatre-vingt...
    A part çà, qu’est-ce pour Oracle une relation d’intersection ? Dans le cadre du Modèle Relationnel de Données, je sais ce qu’est une relation, ce qu’est l’intersection de deux relations (r1 INTERSECT r2), mais sorti de là je donne ma langue au chat...
    Par ailleurs, quel rôle jouerait l’intervalle de temps dans cette affaire ? Ce rôle est-il comparable avec celui de l’intervalle de temps qui est étudié (paragraphes 6.3.2,6.3.3) dans le cadre du Modèle Relationnel de Données ?

  9. #9
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 936
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 936
    Points : 4 356
    Points
    4 356
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    A part çà, qu’est-ce pour Oracle une relation d’intersection ?
    çà n'a rien à vois avec ORACLE : votre table "enseigner"... pourrait contenir des dates debut-fin pour exprimer d'un prof a enseigné une matière dans cet interval de temps,
    cela pimenterait un peu le problème, s'il faut que les cardinalités soient respectées pour chaque moment dans le temps.

    Ce qui est spécifique à ORACLE c'est la possibilité d'avoid des contraintes "DEFERRABLE INITIALLY DEFERRED" == qui ne sont validées qu'au COMMIT, je suis pas certain que ce soit dispo dans SQLServer, et cette fonctionnalité facilite grandement l'implémentation.

  10. #10
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 799
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 799
    Points : 34 031
    Points
    34 031
    Billets dans le blog
    14
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    Bonsoir Philippe,
    Hilarion ne pourra pas violer les règles ?
    Réponse vite fait car je n'ai pas le temps maintenant... J'ai oublié de le préciser mais je maintiens des triggers (ou des contraintes CHECK ? Ce développement remonte à plusieurs années et je n'en ai pas refait depuis) empêchant l'insertion de l'un sans l'autre.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  11. #11
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 761
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 761
    Points : 52 547
    Points
    52 547
    Billets dans le blog
    5
    Par défaut
    Je rappelle que normalement, le développement des applications ne devrait faire qu'accéder à des vues et des procédures et que les vues peuvent êtres mises à jour directement ou indirectement (déclencheurs INSTEAD OF au pire).
    Ainsi la liste des factures devrait être une vue qui présente uniquement les factures ayant au moins une ligne de facturation...
    Du genre :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE VIEW V_FACTURE 
    AS
    SELECT *
    FROM   T_FACTURE AS F
    WHERE  EXISTS(SELECT 1/0 
                  FROM   T_LIGNE_FACTURE AS LF
                  WHERE  F.FAC_ID = LF.FAC_ID);
    Point n'est donc besoin des usines à gaz d'Oracle pour ou des contraintes deferred que Chris Date qualifie d'abomination à cause dans ce cas de l'impossibilité pour l'optimiseur de faire de l'optimisation sémantique notamment... (SQL and the Relational Theory - Third Ed; O'Reilly 2015 pages 296 à 299)...

    Cette vue optimale des factures correctes, doit évidemment être complétée par celle des factures annulées comme suit :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE VIEW V_FACTURE_ANNULEE
    AS
    SELECT *
    FROM   T_FACTURE AS F
    WHERE  NOT EXISTS(SELECT 1/0 
                      FROM   T_LIGNE_FACTURE AS LF
                      WHERE  F.FAC_ID = LF.FAC_ID);
    Surtout que l'id de facture doit être continu et sans trou... D’où l'interdiction du DELETE qui lui doit être contrôlé par déclencheur !

    En effet, dans la vraie vie (celle du papier et du crayon d’antan que beaucoup ont oublié), un facturier est un carnet de feuille de papier duplicata ou triplicata, dans lequel les factures sont pré numérotées. EN cas d'erreur dans l'élaboration d'une facture avec son crayon, on ne supprime pas les feuilles. On les laisse et on met en travers des pages, la mention "annulée"...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  12. #12
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonjour,

    Citation Envoyé par JeitEmgie
    votre table "enseigner"... pourrait contenir des dates debut-fin pour exprimer d'un prof a enseigné une matière dans cet interval de temps,
    cela pimenterait un peu le problème, s'il faut que les cardinalités soient respectées pour chaque moment dans le temps.
    Dès que le temps est partie prenante (cf. le paragraphe 6.4 ici, on doit ici modéliser les règles suivantes :
     
    — Un professeur enseigne telle matière depuis telle date ;
     
    — Un professeur a enseigné telle matière entre telle date et telle date.
     
    D’où l’enrichissement du MCD en conséquence :
     
     
     
    Je ne vois donc pas en quoi le respect des cardinalités (1,n/1,n) est impliqué. La prise en compte des historiques est vieille comme l’informatique de gestion et j’y fus confronté dès 1965 quand j’avais programmé la mise à jour des fichiers du personnel de l’Assistance Publique à Paris.
    Pour la petite histoire, mes programmes ont été opérationnels pendant 30 ans. C’était le bon temps...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  13. #13
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par SQLpro
    Je rappelle que normalement, le développement des applications ne devrait faire qu'accéder à des vues et des procédures et que les vues peuvent êtres mises à jour directement ou indirectement (déclencheurs INSTEAD OF au pire).
    Vu l’approche que j’ai utilisée, on est donc en phase.

    Dans la séquence suivante :
     
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    begin ;
      disable trigger Prof_after_insert on Prof ;
      insert into Prof 
        select distinct profId, profMatricule, profNom 
        from inserted ;
      enable trigger Prof_after_insert on Prof ;
    end ;
     
    j’ai désactivé le trigger en cause juste le temps de l’insert.
     
    Ceci m’a valu cette observation:
     
    Citation Envoyé par JeitEmgie
    Une solution qui nécessite une étape DISABLE TRIGGER n'est pas une bonne solution.
     
    Ce à quoi j’ai répondu :
     
    Citation Envoyé par fsmrel
    je souhaiterais qu’un spécialiste comme SQLpro fournisse une solution alternative.
    JeitEmgie n’explique pas pourquoi ma solution est mauvaise, mais s’il avait raison, Fred, aurais-tu une alternative en magasin ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  14. #14
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 936
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 936
    Points : 4 356
    Points
    4 356
    Par défaut
    Citation Envoyé par fsmrel Voir le message

    JeitEmgie n’explique pas pourquoi ma solution est mauvaise, mais s’il avait raison, Fred, aurais-tu une alternative en magasin ?
    Au minimum parce que
    "Pour désactiver un déclencheur DML, un utilisateur doit avoir au minimum l'autorisation ALTER pour la table ou la vue sur laquelle le déclencheur a été créé."

  15. #15
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par JeitEmgie
    "Pour désactiver un déclencheur DML, un utilisateur doit avoir au minimum l'autorisation ALTER pour la table ou la vue sur laquelle le déclencheur a été créé."
     
    Je veux bien, mais ceci est orthogonal au sujet traité.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  16. #16
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    A propos des suppressions (delete).

    Prudemment, on s’assure que les tentatives de suppression des profs et des matières échouent. Pour cela, on s’abstient de coder CASCADE dans les contraintes référentielles affectées à la table Enseigner.

    Supposons maintenant que l’on veuille supprimer le professeur Raoul. Je pense que SQLpro sera d’accord avec moi avec le scénario suivant :

    — Affecter à la table Enseigner un trigger de type AFTER DELETE ;
     
    — Supprimer dans la table Enseigner les lignes référençant Raoul, et quand il n’y en a plus, supprimer Raoul et les matières qu’il enseigne.

    Il s’agit là d’un CASCADE à rebours...

    Se pose maintenant la question suivante : effet Cascade ou Restrict (No action) ? Autrement dit, dans ce dernier cas, hors de question de supprimer Raoul et ses matières... On peut supposer qu’au milieu des années soixante-dix, l’inventeur merisien des cardinalités (1,n) ne se doutait pas des conséquences de la chose, d’autant que SQL n’existait pas à l’époque (tandis que Chamberlin et Boyce créaient le prototype Sequel, Sequel 2)

    En attendant, une ébauche de trigger pourrait être celle-ci (optique Restrict) :

    Code SQL : 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
    CREATE TRIGGER Enseigner_after_delete on Enseigner after delete as
    begin
      declare @n varchar(24) -- int ;
      declare @tableEnseignerDeleted table (profId smallint, matiereId smallint) ; 
      declare @errId as int = 314118  -- numéro de l'erreur à afficher
      declare @errTexte as varchar (255) -- message d'erreur
      declare @profId as smallint --varchar(24)
      declare @profNom as varchar(24)
     
      insert into @tableEnseignerDeleted (profId, matiereId)
        select distinct profId, matiereId  
        from deleted
        where profId in (select profId from Prof) ;
     
      set @n = (select count(*) 
                from @tableEnseignerDeleted as a
                join Enseigner as b on a.profId = b.profId) ;
     
      if @n = 0 
        begin
          set @profId = (select top (1) profId 
                         from @tableEnseignerDeleted)
          set @profNom = (select profnom from prof where profid = @profId)
          set @errTexte = 
              char(13)
            + 'Un prof doit enseigner au moins une matière.' 
            + char(13)
            + 'Du fait de la tentative de suppression, le prof '''
            + @profNom
          --  + cast(@profId as varchar)
            + ''' ne répondrait pas à cette obligation.' 
            + char(13)
          ; throw @errId, @errTexte, 16
        end ;
    end
     
    Se pose un problème dans ce contexte qui relève du métabolisme des données : comment signifier de façon simple et élégante au trigger que l’on se place dans l’optique Cascade ? Quant à moi, I give my tongue to the cat ...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  17. #17
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonjour,

    Citation Envoyé par fsmrel
    Se pose un problème dans ce contexte qui relève du métabolisme des données : comment signifier de façon simple et élégante au trigger que l’on se place dans l’optique Cascade ?
    Je sens que je vais en passer par une procédure stockée, c’est un peu bourrin, je vous tiens au courant.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  18. #18
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonjour,

    Je fignole, et dans l'après-midi ça devrait être bon...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  19. #19
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 761
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 761
    Points : 52 547
    Points
    52 547
    Billets dans le blog
    5
    Par défaut
    En fait dans le cas de SQL Server les déclencheurs peuvent être récursif et réentrant.

    Un trigger est dit récursif si le code du déclencheur lance la même action sur la même table ou vue.

    Par exemple un déclencheur UPDATE sur une table des clients qui ferait un UPDATE sur la table des clients...

    Mais par défaut les bases de données créées par SQL Server ont un paramètre "recurive triggers" défini à OFF. Il n'y a donc pas récursivité, sauf modification dudit paramètre et dans ce cas la limityation est par construction de 32 niveau et peut être suvie par @@NESTLEVEL.

    La réentrance est pilotée par un autre paramètre de niveau instance "nested triggers" (modifiable via la procédure sp_configure) qui lui est à 1 (pour "ON"). Ceci concerne des triggers que je qualifie de ping-pong (la mise à jour du client déclenche un trigger qui met à jour une facture qui déclenche un trigger qui met à jour le client...).

    On peut donc désactiver cette fonctionnalité au niveau de l'instance....
    Pourquoi au niveau de l'instance, tout simplement pour permettre à des déclencheurs d'atteindre les données d'une autre base (par exemple pour gérer une intégrité référentielle procédurale et non plus déclarative entre une base de données centrale et différentes bases locales - une base constiuant un référentiel pour les différentes autres bases...).

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  20. #20
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 001
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 001
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonjour,

    Dans ce message, j’ai fourni un trigger ayant pour objet le contrôle des suppressions dans la table Enseigner.
     
    Par la suite, j’ai évoqué la mise en oeuvre d’une procédure stockée. En fait, en attendant une solution optimale, élégante et tout ça, je propose de procéder ainsi :
     
    Avant d’effectuer des delete dans la table Enseigner, l’utilisateur précise son intention. Si le prof et la matière sont à supprimer au cas où ils n’existent plus dans la table Enseigner, alors le trigger devra supprimer le prof dans la table Prof, ainsi que la matière dans la table Matiere : à cet effet, l’utilisateur précise son intention de la façon suivante, option 'cascade' dans une table de paramétrage (nommée Parametre ci-dessous).
    Si le prof et la matière ne doivent pas être supprimés, alors le trigger devra annuler la suppression dans la table Enseigner (option 'no action' dans la table Parametre).
     
    Exemple :
     
    Table Parametre :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    CREATE TABLE Parametre
    (
       action varchar(9) NOT NULL
     , constraint Parametre_VA check (action in ('cascade', 'no action')
    ) ;
     
    Cette table ne contient pas de clé primaire, mais faudra trouver une astuce pour interdire qu’elle contienne plus d’une ligne (Hilarion traîne ses guêtres...) : à vot’ bon coeur, m’sieurs dames...
    (A noter que le trigger supprimera systématiquement son contenu, effet Hilarion oblige).
     
    Contenu de la table Prof :
     
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    profId	profMatricule	profNom
    1	fenau		Fernand
    2	ravol		Raoul
    3	pat  		Patricia
     
    Table Enseigner :
     
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    profMatricule	profNom		matiereCode	matiereNom
    fenau		Fernand		phy		physique
    fenau		Fernand		chm		chimie
    ravol		Raoul		chm		chimie
    ravol		Raoul		maths		mathématiques
    pat  		Patricia	mus		musique
     
    Raoul ne doit plus enseigner la chimie :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    insert into Parametre values ('cascade') ;
      
    delete from Enseigner 
    where profId = (select profId 
                    from Prof 
                    where profNom = 'Raoul')
    and matiereId = (select matiereId
                     from Matiere
                     where matiereNom = 'chimie') ;
     
    Le trigger n’aura pas d’objection quant à la suppression effectuée par le système, car Raoul enseigne plus d’une matière.
     
    Au résultat :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select profNom, matiereNom 
    from Enseigner as e
    join Prof as p on p.profId = e.profId
    join Matiere as m on m.matiereId = e.matiereId
    order by profNom, matiereNom
     
    =>
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    profNom		matiereNom
    Fernand		chimie
    Fernand		physique
    Patricia	musique
    Raoul		mathématiques
     
    Faisons encore le ménage :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    insert into Parametre values ('cascade') ;
      
    delete from Enseigner 
    where profId = (select profId 
                    from Prof 
                    where profNom = 'Raoul')
     
    Le trigger validera la suppression effectuée par le système, car l’utilisateur a retenu l’option 'cascade'.
    Au résultat :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select profNom, matiereNom 
    from Enseigner as e
    join Prof as p on p.profId = e.profId
    join Matiere as m on m.matiereId = e.matiereId
    order by profNom, matiereNom
     
    =>
     
    Raoul a été supprimé dans la table Enseigner :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    profNom		matiereNom
    Fernand		chimie
    Patricia	musique
     
    Raoul a aussi été supprimé dans la table Prof :
     
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    profId	profMatricule	profNom
    1	fenau	Fernand
    3	pat  	Patricia
     
    Revenons à la case départ de delete, et remplaçons maintenant 'cascade' par 'no action'.
     
    Raoul ne doit plus enseigner la chimie :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    insert into Parametre values ('no action') ;
      
    delete from Enseigner 
    where profId = (select profId 
                    from Prof 
                    where profNom = 'Raoul')
    and matiereId = (select matiereId
                     from Matiere
                     where matiereNom = 'chimie') ;
     
    A nouveau, le trigger n’aura d’objection quant à la suppression effectuée par le système, puisque Raoul enseigne plus d’une matière.
     
    Au  résultat :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select profNom, matiereNom 
    from Enseigner as e
    join Prof as p on p.profId = e.profId
    join Matiere as m on m.matiereId = e.matiereId
    order by profNom, matiereNom
     
    =>
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    profNom		matiereNom
    Fernand		chimie
    Fernand		physique
    Patricia	musique
    Raoul		mathématiques
     
    Continuons le ménage :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    insert into Parametre values ('no action') ;
     
    delete from Enseigner 
    where profId = (select profId 
                    from Prof 
                    where profNom = 'Raoul')
     
    Cette fois-ci le trigger ne validera pas la suppression effectuée par le système, car l’utilisateur a retenu l’option 'no action'.
     
    Au résultat :
     
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    Msg 314118, Niveau 16, État 16, Procédure Enseigner_after_delete, Ligne 77 [Ligne de départ du lot 354]
     
    Un prof doit enseigner au moins une matière.
    Du fait de la tentative de suppression, le prof 'Raoul' ne répondrait pas à cette obligation.
    (matière impliquée : mathématiques).

     
    Le trigger de contrôle des suppressions :
     
    Code SQL : 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
    CREATE TRIGGER Enseigner_after_delete on Enseigner after delete as
    begin
      declare @n varchar(24) -- int ;
      declare @tableEnseignerDeleted table (profId smallint, matiereId smallint) ; 
      declare @errId as int = 314118  -- numéro de l'erreur à afficher
      declare @errTexte as varchar (255) -- message d'erreur
      declare @profId as smallint --varchar(24)
      declare @profNom as varchar(24)
      declare @matiereId as smallint --varchar(24)
      declare @matiereNom as varchar(24)
     
      insert into @tableEnseignerDeleted (profId, matiereId)
        select distinct profId, matiereId  
        from deleted
        where profId in (select profId from Prof) 
      ;
      set @profNom = (select profNom from Prof where profId in  (select profId from deleted))
      set @matiereNom = (select matiereNom from Matiere where matiereId in  (select matiereId from deleted))
     
      declare @action varchar(9) ;
     
      set @action = (select action from Parametre) ;
      -- on n'a plus besoin du contenu de la table Parametre, on fait le ménage 
      delete from Parametre ;
     
      set @n = (select count(*) 
                from @tableEnseignerDeleted as a
                join Enseigner as b on a.profId = b.profId) ;
     
      if @n = 0 and @action = 'cascade' 
        begin
          set @profId = (select top (1) profId 
                         from @tableEnseignerDeleted)
     
          declare @countProf as smallint
          set @countProf = (select count(*) from Prof where profId = @profId)
           set @matiereId = (select top (1) matiereId 
                             from @tableEnseignerDeleted);
          declare @countMatiere as smallint
          set @countMatiere = (select count(*) from Matiere where matiereId = @matiereId)
          if @countProf = 1 and @countMatiere = 1
            begin
              delete from Prof where profId =  @profId ;
              delete from Matiere where matiereId =  @matiereId ;
            end
        end
     
      if @n = 0 and @action <> 'cascade' 
        begin
          set @profId = (select top (1) profId 
                         from @tableEnseignerDeleted)
          set @profNom = (select profNom from Prof where profId = @profId)
          set @errTexte = 
              char(13)
            + 'Un prof doit enseigner au moins une matière.' 
            + char(13)
            + 'Du fait de la tentative de suppression, le prof '''
            + @profNom
          --  + cast(@profId as varchar)
            + ''' ne répondrait pas à cette obligation.' 
            + char(13)
            + '(matière impliquée : ' + @matiereNom + ').'
          ; throw @errId, @errTexte, 16
        end ;
    end
    go
     
    J’ai sûrement oublié des choses, merci de m’en informer...

    Si vous avez une meilleure solution, merci de la présenter, je suis toute ouïe !
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

+ Répondre à la discussion
Cette discussion est résolue.
Signaler un problème
Page 1 sur 4 1234 DernièreDernière

Discussions similaires

  1. Pb au niveau des cardinalités
    Par sebac dans le forum Access
    Réponses: 1
    Dernier message: 26/04/2014, 10h08
  2. Niveau isolement des transactions
    Par lio33 dans le forum Débuter
    Réponses: 4
    Dernier message: 23/11/2005, 15h00
  3. Logs SQL des actions réalisées dans Enterprise Manager ?
    Par [DreaMs] dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 11/08/2005, 12h14
  4. [Together] Gestion des cardinalités
    Par cladsam dans le forum Autres
    Réponses: 3
    Dernier message: 03/08/2005, 21h33
  5. [SQL Server 2000] Générer le script SQL des données
    Par Giovanny Temgoua dans le forum MS SQL Server
    Réponses: 4
    Dernier message: 31/03/2005, 18h35

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo