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

MS SQL Server Discussion :

Problème de triggers et dépendence fonctionnelle


Sujet :

MS SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2010
    Messages
    196
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2010
    Messages : 196
    Par défaut Problème de triggers et dépendence fonctionnelle
    Bonjour alors voici mon trigger

    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
    USE [geststockmazars]
    GO
    /****** Object:  Trigger [dbo].[suppr_four]    Script Date: 05/04/2011 16:56:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[suppr_four]
    ON [dbo].[fournisseur] FOR delete
    as
    declare
    @id_four numeric(2,0)
    begin
    select @id_four = nofour from deleted;
    delete from consommable
    where reffour = @id_four
    end
    Je souhaitais qu'il supprime automatiquement dans ma table consommable qui contient tout mes consommables, tout les consommables qui ont pour reffour (reference forunnisuser) le fournisseur qu'on supprime de la table fournisseur.

    Voilà mes tables :

    consommable (noprod, proddesig, prodprix, qtestock, datemodif, codetype#, reffour#)
    fournisseur (nofour, libfour, raisonsoc)
    typeconso (notype, libtype)
    users(id, login , pass, statut)

    La base de données est consituée de 4 tables : consommable, fournisseur, typeconso et users :

    La table consommable contient les différents consommables stockés dans la base de données
    La table fournisseur répertorie les différents fournisseurs des consommables
    La table typeconso recense elle les différents types de consommables
    Enfin, la table users stocke les différents utilisateurs et leurs droits.

    Le problème ne semble pas venir de mon trigger mais du fait que j'ai mis une clé étrangère entre ces 2 tables.
    Quand je lenleve la suppression se fait et le trigger sexecute.
    Comment faire pour que cela fonctionne sans que je n'ai à enlever cette clé étrangère ?Faire une suppression en cascade, chose que je ne sais pas faire avec sqlserver...



    Merci d'avance !

  2. #2
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Par défaut
    Bonjour,


    En effet, le suppression en cascade peut etre une bonne solution dans votre cas. Quelle est votre version de SQL Server ? (il me semble que cette fonctionnalité date de 2005 ?!?)

    Vous pouvez alors ajouter "ON DELETE CASCADE" à la fin de votre définition de clef étrangère.


    Cependant, il est normal également que votre trigger ne fonctionne pas :
    Vous spécifiez un trigger "FOR DELETE", qui s’exécute donc après que la suppression a eu lieu, et donc après que les contraintes d’intégrité ont été vérifiées !
    Dans votre cas, la suppression du fournisseur viole une contrainte d'integrité, votre ordre est donc annulé, et votre trigger n'est jamais executé.
    Pour qu'il fonctionne tel que vous l'avez écrit, vous devriez faire un trigger INSTEAD OF, qui supprime d'abord les consommables, puis supprime les fournisseurs.

    Pensez également qu'un ordre DELETE peut affecter plusieurs lignes, dans votre trigger, vous cherchez l'ID du fournisseur. Vous devriez donc plutôt faire une suppression directement à partir de la pseudo Table deleted :

    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
     
     
    ALTER TRIGGER [dbo].[suppr_four]
    ON [dbo].[fournisseur] 
    INSTEAD OF DELETE
    AS
    BEGIN
     
        --supression des consommables
        DELETE FROM Consommable 
        FROM DELETED 
        WHERE Consommable.reffour = DELETED.nofour
     
        --suppression des fournisseurs
        DELETE FROM Fourniseeur
        FROM DELETED
        WHERE Fornisseur.nofour= DELETED.nofour
     
    END

  3. #3
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2010
    Messages
    196
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2010
    Messages : 196
    Par défaut
    Tout d'abord merci pour votre réponse très complète.

    Vous me proposez deux solutions.
    Dois-je les appliquer toutes les deux ou une seule des deux suffit ?
    J'entends par là que si je rajouter le on delete cascad cela suffira ou alors il faut que jemodifie aussi mon trigger ?

    Merci d'avance !

  4. #4
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Bonjour,

    Citation Envoyé par aieeeuuuuu
    En effet, le suppression en cascade peut etre une bonne solution dans votre cas. Quelle est votre version de SQL Server ? (il me semble que cette fonctionnalité date de 2005 ?!?)
    Cela existe aussi dans la version 2000 ... et probablement dans les versions antérieures

    Citation Envoyé par polux31
    Vous me proposez deux solutions.
    Dois-je les appliquer toutes les deux ou une seule des deux suffit ?
    J'entends par là que si je rajouter le on delete cascad cela suffira ou alors il faut que jemodifie aussi mon trigger ?
    C'est l'un des deux. Je pencherai vers le CASCADE néanmoins.
    Aieeeuuuuu a voulu vous expliquer pourquoi votre trigger ne fait pas ce que vous voulez qu'il fasse.

    Le trigger tel que vous l'avez écrit n'est pas ensembliste : il est donc faux.

    @++

  5. #5
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2010
    Messages
    196
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2010
    Messages : 196
    Par défaut
    Ok.
    Si j'ajoute la fonction en cascade.
    Un trigger comme cela irait ?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    ALTER TRIGGER [dbo].[suppr_four]
    ON [dbo].[fournisseur] 
    INSTEAD OF DELETE
    AS
    BEGIN
     
        --supression des consommables
        DELETE FROM Consommable 
        FROM DELETED 
        WHERE Consommable.reffour = DELETED.nofour
     
     
    END

    Comme ça lorsqu'on delete sur ma table fournisseur automatiquement le trigger se lance et on delete les consommables.

    C'est bien ça ?

  6. #6
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    C'est l'une des deux solutions, mais pas les deux en même temps :
    Si vous mettez CASCADE pour vos DELETE dans la contrainte, vous n'avez pas besoin du trigger, et inversement

    Pour le trigger, on je préfère cette écriture :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    ALTER TRIGGER [dbo].[suppr_four]
    	ON [dbo].[fournisseur] 
    INSTEAD OF DELETE
    AS
    BEGIN
    	SET NOCOUNT ON
     
    	-- Supression des consommables
    	DELETE		FROM dbo.Consommable 
    	FROM		DELETED AS D
    	INNER JOIN	dbo.Consommable AS C
    				ON C.reffour = D.nofour 
    END
    Cependant attention, parce que ce trigger est un INSTEAD OF
    Cela signifie que tel quel, les lignes de la table Consommable seront supprimées, mais pas les lignes de la table fournisseur.

    En ce sens le code complet du trigger devrait être :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    ALTER TRIGGER [dbo].[suppr_four]
    	ON [dbo].[fournisseur] 
    INSTEAD OF DELETE
    AS
    BEGIN
    	SET NOCOUNT ON
     
    	-- Supression des consommables
    	DELETE		FROM dbo.Consommable 
    	FROM		DELETED AS D
    	INNER JOIN	dbo.Consommable AS C
    				ON C.reffour = D.nofour 
     
    	-- Suppression des fournisseurs
    	DELETE		FROM dbo.Fourniseeur
    	FROM		DELETED AS D
    	INNER JOIN	dbo.Fournisseur AS F
    				ON F.nofour = D.nofour
    END
    Dans tous les cas le trigger que vous a donné aieeeuuuuu est correct.
    Il s'agit juste des jointures que j'ai horreur (mais c'est mon goût) de voir dans une clause WHERE.

    Enfin le CASCADE sera moins consommateur de ressources que le trigger.
    Mais lorsque vous supprimerez des lignes de la table Fournisseur, vous supprimerez également toutes les lignes de toutes les tables référençant la table Fournisseur qui ont pour valeur celle de clé primaire des lignes supprimées de la table Fournisseur.
    Si ceci n'est pas le comportement que vous souhaitez avoir, alors créez le trigger.


    @++

Discussions similaires

  1. PL/SQL problème sur Trigger
    Par kitsune dans le forum PL/SQL
    Réponses: 4
    Dernier message: 06/12/2005, 20h35
  2. [9i] problème avec trigger after logon
    Par Michael# dans le forum Oracle
    Réponses: 2
    Dernier message: 17/03/2005, 12h14
  3. [Interbase6] Problème de triggers
    Par emeraudes dans le forum Bases de données
    Réponses: 4
    Dernier message: 08/03/2005, 09h52
  4. [SQLPLUS] - Problème de Triggers Java
    Par farcis dans le forum Oracle
    Réponses: 7
    Dernier message: 23/12/2004, 09h21
  5. [PL/SQL] problème de trigger
    Par Chuck67 dans le forum Oracle
    Réponses: 14
    Dernier message: 09/12/2004, 23h17

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