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

PL/SQL Oracle Discussion :

Empêcher un insert dans une table par un Trigger


Sujet :

PL/SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre éclairé
    Homme Profil pro
    Inscrit en
    Mars 2007
    Messages
    616
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Luxembourg

    Informations forums :
    Inscription : Mars 2007
    Messages : 616
    Par défaut Empêcher un insert dans une table par un Trigger
    Bonjour,

    J'aimerai savoir comment je peux empêcher un insert par un Trigger.

    Le problème de Trigger BEFORE INSERT
    c'est qu'il ne peut pas bloquer l'insert (ou je me trompe). Les exemples que j'ai vu sur le net, consistent à lever une exception, ce qui va empêcher l'insert, mais ça va provoquer une exception qui va remonter jusqu'à l'application (et ça je ne veux pas).

    J'ai pensé à trigger INSTEAD OF, mais il ne fonctionne que sur de vues.

    Donc, ma question reste : y a-t-il moyen d'empêcher de faire l'insert par un trigger sans provoquer d'exception (de manière silencieuse)?

    Merci pour votre réponse.

  2. #2
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Non.

  3. #3
    Membre éclairé
    Homme Profil pro
    Inscrit en
    Mars 2007
    Messages
    616
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Luxembourg

    Informations forums :
    Inscription : Mars 2007
    Messages : 616
    Par défaut
    Donc, il faudra checker dans l'application, et voir si l’erreur retournée est "normale"

  4. #4
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 134
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 64
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 134
    Par défaut
    Idée peut-être farfelue
    Pourquoi pas un delete de la ligne qui vient d'être créée dans un trigger after insert ?
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  5. #5
    Membre Expert Avatar de Garuda
    Homme Profil pro
    Chef de projet / Urbaniste SI
    Inscrit en
    Juin 2007
    Messages
    1 285
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Vaucluse (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Chef de projet / Urbaniste SI
    Secteur : Bâtiment

    Informations forums :
    Inscription : Juin 2007
    Messages : 1 285
    Par défaut
    Citation Envoyé par al1_24 Voir le message
    Idée peut-être farfelue
    Pourquoi pas un DELETE de la ligne qui vient d'être créée dans un trigger after INSERT ?

    Cette idée est non seulement farfelue mais impossible a réaliser a cause de l'erreur "table is mutating" qui serait levée !
    http://sgbd.developpez.com/oracle/ora-04091/

  6. #6
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 134
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 64
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 134
    Par défaut
    En effet, j'avais oublié ça
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  7. #7
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Citation Envoyé par Garuda Voir le message

    Cette idée est non seulement farfelue mais impossible a réaliser a cause de l'erreur "table is mutating" qui serait levée !
    http://sgbd.developpez.com/oracle/ora-04091/
    Elle est farfelue et totalement erronée mais pas impossible!
    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
     
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
    Connected as mni
     
    SQL> 
    SQL> create table t_trg (a int)
      2  /
     
    Table created
    SQL> create or replace trigger ai_t_trg
      2  After insert on t_trg
      3  Begin
      4    Delete t_trg;
      5  end;
      6  /
     
    Trigger created
     
    SQL> insert into t_trg values (1);
     
    1 row inserted
     
    SQL> commit;
     
    Commit complete
     
    SQL> Select * 
      2  from t_trg
      3  /
     
                                          A
    ---------------------------------------
     
    SQL> insert into t_trg select level from dual connect by level <= 3
      2  /
     
    3 rows inserted
     
    SQL> Select *
      2    From t_trg
      3  /
     
                                          A
    ---------------------------------------
     
    SQL>
    Bref dans la dentelle, il y aura un trigger de ligne qui enregistre les rowids des lignes qui doivent être supprimées et le trigger d'instruction (statement level) qui supprimera que ces lignes. Un trigger compound à partir de la 11g permettras d'attendre le but plus facilement.

  8. #8
    Membre éclairé
    Homme Profil pro
    Inscrit en
    Mars 2007
    Messages
    616
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Luxembourg

    Informations forums :
    Inscription : Mars 2007
    Messages : 616
    Par défaut
    Citation Envoyé par Garuda Voir le message

    Cette idée est non seulement farfelue mais impossible a réaliser a cause de l'erreur "table is mutating" qui serait levée !
    http://sgbd.developpez.com/oracle/ora-04091/
    Je pensais aussi à MUTATING, mais après avoir créé le trigger, j'ai remarqué que ça ne plante pas.
    Voici le code:
    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
    CREATE OR REPLACE TRIGGER TRI_BI_DIM_POL
    BEFORE INSERT
    ON DIM_POL
    FOR EACH ROW
    DECLARE
    	one_row DIM_POL%ROWTYPE;
     
    BEGIN
    	-- search if DB does not have duplicate entry
    	SELECT * INTO one_row FROM DIM_POL WHERE POL_ID = :new.POL_ID AND START_D_VER = :new.START_D_VER;
    	IF one_row.pol_id IS NOT NULL THEN		
    		:new.POL_KEY := one_row.POL_KEY;
    		:new.START_D_VER := one_row.START_D_VER;
    		:new.BUILD_D_INS := one_row.BUILD_D_INS;
    		:new.BUILD_ID := one_row.BUILD_ID;
     
    		DELETE FROM DIM_POL WHERE POL_ID = :new.POL_ID AND START_D_VER = :new.START_D_VER;
    	END IF;
    EXCEPTION
    	WHEN NO_DATA_FOUND THEN
    	BEGIN
    		-- search for previous version and verify if there is no changes
    		SELECT * INTO one_row 
    		FROM DIM_POL 
    		WHERE POL_ID = :new.POL_ID 
    			AND :new.START_D_VER BETWEEN START_D_VER AND END_D_VER;
     
    		IF (	(one_row.pol_id = :new.pol_id OR one_row.pol_id IS NULL AND :new.pol_id IS NULL)
    			AND (one_row.pol_company = :new.pol_company OR one_row.pol_company IS NULL AND :new.pol_company IS NULL)
    			AND (one_row.pol_ver = :new.pol_ver OR one_row.pol_ver IS NULL AND :new.pol_ver IS NULL)
    			AND (one_row.pol_currency = :new.pol_currency OR one_row.pol_currency IS NULL AND :new.pol_currency IS NULL)
    			AND (one_row.pol_d_effective = :new.pol_d_effective OR one_row.pol_d_effective IS NULL AND :new.pol_d_effective IS NULL)
    			AND (one_row.pol_d_term = :new.pol_d_term OR one_row.pol_d_term IS NULL AND :new.pol_d_term IS NULL)
    			AND (one_row.pol_country_law = :new.pol_country_law OR one_row.pol_country_law IS NULL AND :new.pol_country_law IS NULL)
    			AND (one_row.pol_fiscal_country = :new.pol_fiscal_country OR one_row.pol_fiscal_country IS NULL AND :new.pol_fiscal_country IS NULL)
    			AND (one_row.pol_status = :new.pol_status OR one_row.pol_status IS NULL AND :new.pol_status IS NULL)
    			AND (one_row.pol_brk_key = :new.pol_brk_key OR one_row.pol_brk_key IS NULL AND :new.pol_brk_key IS NULL)
    			AND (one_row.pol_brk_id = :new.pol_brk_id OR one_row.pol_brk_id IS NULL AND :new.pol_brk_id IS NULL)
    		) THEN
    			-- no changes, delete the previous version and reinsert the the same value 
    			:new.POL_KEY := one_row.POL_KEY;
    			:new.START_D_VER := one_row.START_D_VER;
    			:new.BUILD_D_INS := one_row.BUILD_D_INS;
    			:new.BUILD_ID := one_row.BUILD_ID;
     
    			DELETE FROM DIM_POL WHERE POL_ID = :new.POL_ID AND START_D_VER = :new.START_D_VER;
    		ELSE
    			-- new version, update old version END_D_VER and CURR_VER_SW
    			UPDATE DIM_POL 
    			SET END_D_VER = :new.START_D_VER -1
    				,CURR_VER_SW = 'N'
    			WHERE POL_ID = :new.POL_ID 
    				AND :new.START_D_VER BETWEEN START_D_VER  AND END_D_VER;
     
    			:new.BUILD_D_INS := one_row.BUILD_D_INS;
    		END IF;
    	EXCEPTION
    		WHEN NO_DATA_FOUND THEN 
    			-- new entry, no historic
    			:new.BUILD_D_INS := sysdate;
    		NULL; 
    	END;
    END;
    /
    La table DIM_POL a une PK composée de colonnes POL_ID et START_D_VER

  9. #9
    Modérateur
    Avatar de Chtulus
    Homme Profil pro
    Ingénieur
    Inscrit en
    Avril 2008
    Messages
    3 094
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Santé

    Informations forums :
    Inscription : Avril 2008
    Messages : 3 094
    Par défaut
    Citation Envoyé par mnitu Voir le message
    Non.
    Bonsoir,

    @mnitu c'est pas vraiment une réponse !

    Nous te remercions de développer !
    ce n'est pas la seule réponse, développe stp !

    « Je ne cherche pas à connaître les réponses, je cherche à comprendre les questions. »
    - Confucius -

    Les meilleurs cours, tutoriels et Docs sur les SGBD et le SQL
    Tous les cours Office
    Solutions d'Entreprise



  10. #10
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Citation Envoyé par Chtulus Voir le message
    Bonsoir,

    @mnitu c'est pas vraiment une réponse !

    Nous te remercions de développer !
    ce n'est pas la seule réponse, développe stp !

    Bonjour,

    ...
    Donc, ma question reste : y a-t-il moyen d'empêcher de faire l'insert par un trigger sans provoquer d'exception (de manière silencieuse)?
    Non.
    Que voulez-vous que je développe ?

  11. #11
    Membre éclairé
    Homme Profil pro
    Inscrit en
    Mars 2007
    Messages
    616
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Luxembourg

    Informations forums :
    Inscription : Mars 2007
    Messages : 616
    Par défaut
    Merci Messieurs pour vos réponses.

    J'ai réussi à faire ce que je voulais avec le trigger en faisant un DELETE avant l'INSERT. J'aurais préféré, ne pas faire l'INSERT, mais bon ma solution me va aussi.


  12. #12
    Membre Expert
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    Février 2012
    Messages
    652
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : Distribution

    Informations forums :
    Inscription : Février 2012
    Messages : 652
    Par défaut
    Citation Envoyé par cmako Voir le message
    J'ai réussi à faire ce que je voulais avec le trigger en faisant un DELETE avant l'INSERT. J'aurais préféré, ne pas faire l'INSERT, mais bon ma solution me va aussi.






    Ca pique un peu les yeux tout ça !

  13. #13
    Membre expérimenté Avatar de dariyoosh
    Profil pro
    Inscrit en
    Avril 2009
    Messages
    236
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Avril 2009
    Messages : 236
    Par défaut
    Bonjour,

    Citation Envoyé par cmako Voir le message
    J'aimerai savoir comment je peux empêcher un insert par un Trigger.
    Merci de décrire exactement ce que vous souhaitez rejeter (avec la description de la table CREATE TABLE ainsi que le contrôle qu'il faut faire)

    Citation Envoyé par cmako Voir le message
    Donc, ma question reste : y a-t-il moyen d'empêcher de faire l'insert par un trigger sans provoquer d'exception (de manière silencieuse)?
    Et pourquoi de manière silencieuse qui pourrait devenir la source de nombreux problèmes que l'on aurait vraiment du mal à trouver?

  14. #14
    Membre éclairé
    Homme Profil pro
    Inscrit en
    Mars 2007
    Messages
    616
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Luxembourg

    Informations forums :
    Inscription : Mars 2007
    Messages : 616
    Par défaut
    Citation Envoyé par al1_24 Voir le message
    Pourquoi pas un delete de la ligne qui vient d'être créée dans un trigger after insert ?
    Par ce que sinon la clé primaire sera violée.


    Citation Envoyé par dariyoosh Voir le message
    Merci de décrire exactement ce que vous souhaitez rejeter (avec la description de la table CREATE TABLE ainsi que le contrôle qu'il faut faire)


    Et pourquoi de manière silencieuse qui pourrait devenir la source de nombreux problèmes que l'on aurait vraiment du mal à trouver?
    J'ai actuellement un système qui m'extrait des données et qui va m'alimenter une table.
    Le système peut générer des doublons, ce qui est parfaitement normal, dû aux différents critères de sélection.
    Donc si le système m'extrait l'information sur le contrat N°2 2 fois, je veux qu'il soit en un seul exemplaire, de plus il va violer la PK.

    De plus, toutes les données seront historisées, donc si demain j'insère le contrat N°2 et s'il n'a pas changé, je ne voudrais pas l'insérer.

    Donc, je ne voudrais pas remonter l'exception si ce fonctionnement est normal.


  15. #15
    Membre expérimenté
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Mai 2012
    Messages
    127
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Mai 2012
    Messages : 127
    Par défaut
    Bonjour,

    Citation Envoyé par cmako Voir le message
    J'ai actuellement un système qui m'extrait des données et qui va m'alimenter une table.
    Le système peut générer des doublons, ce qui est parfaitement normal, dû aux différents critères de sélection.
    Donc si le système m'extrait l'information sur le contrat N°2 2 fois, je veux qu'il soit en un seul exemplaire, de plus il va violer la PK.

    De plus, toutes les données seront historisées, donc si demain j'insère le contrat N°2 et s'il n'a pas changé, je ne voudrais pas l'insérer.

    Donc, je ne voudrais pas remonter l'exception si ce fonctionnement est normal.

    Vous n'avez pas besoin de trigger. Vous avez besoin de l'ordre MERGE, qui permet d'insérer ou de mettre à jour (ou de supprimer, ou de ne rien faire) en fonction d'une condition.

    Essayez quelque chose comme :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    MERGE 
    INTO table_destination dest
    USING  une table ou une requête qui fournit les données à insérer  source
    ON (dest.colonne_pk = source.colonne_pk) 
    
    -- la condition n'est pas remplie : il s'agit d'un nouveau contrat, à insérer
    WHEN NOT MATCHED THEN  INSERT (liste_de_colonnes) VALUES (source.colonne_1, ...)
    
    -- condition remplie : il s'agit d'un contrat pour lequel il y a déjà une ligne dans la table de destination
    -- Si on veut ne rien faire, il suffit de ne pas mettre la clause WHEN MATCHED THEN, autrement il faut inclure cette clause, avec par exemple un ordre UPDATE pour mettre à jour la ligne existante à partir des données de la source

  16. #16
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Cherchez dans la documentation Oracle "DML Error Logging" et "FORALL SAVE EXCEPTIONS" et prenez la solution qui vous semble la plus appropriée.

  17. #17
    Expert confirmé
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 822
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 822
    Billets dans le blog
    1
    Par défaut
    Bonjour,
    Citation Envoyé par cmako Voir le message
    Par ce que sinon la clé primaire sera violée.
    Il y a un hint magique pour faire ça: IGNORE_ROW_ON_DUPKEY_INDEX.
    Ce n'est probablement pas une bonne idée, mais pas pire qu'un trigger...
    Cordialement,
    Franck

Discussions similaires

  1. [ZEOSLIB] Problème Insertion dans une table
    Par moscovisci dans le forum Bases de données
    Réponses: 1
    Dernier message: 09/06/2005, 12h05
  2. [interbase6]probleme d'insertion dans une table
    Par macadam314 dans le forum Bases de données
    Réponses: 10
    Dernier message: 22/02/2005, 14h21
  3. [Sybase] Temps d'une insertion dans une table
    Par vsavoir dans le forum Décisions SGBD
    Réponses: 5
    Dernier message: 14/02/2005, 10h04
  4. Extraction d'un .txt et Insertion dans une table
    Par PoPmiSiR dans le forum Access
    Réponses: 8
    Dernier message: 28/10/2004, 19h13
  5. Détection insertion dans une Table
    Par abelman dans le forum MS SQL Server
    Réponses: 5
    Dernier message: 06/07/2004, 14h24

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