Empêcher un insert dans une table par un Trigger

Version imprimable

03/10/2013, 09h42
cmako

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.
03/10/2013, 10h37
mnitu

Non.
03/10/2013, 10h49
cmako

Donc, il faudra checker dans l'application, et voir si l’erreur retournée est "normale"
03/10/2013, 11h01
al1_24

Idée peut-être farfelue :oops:
Pourquoi pas un delete de la ligne qui vient d'être créée dans un trigger after insert ?
03/10/2013, 13h41
dariyoosh

Bonjour,

Citation:

Envoyé par cmako

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

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?
03/10/2013, 15h02
cmako

Citation:

Envoyé par al1_24

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

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.

;)

Bonjour,

Citation:

Envoyé par cmako

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:

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

03/10/2013, 15h21
mnitu

Cherchez dans la documentation Oracle "DML Error Logging" et "FORALL SAVE EXCEPTIONS" et prenez la solution qui vous semble la plus appropriée.
03/10/2013, 21h05
pachot

Bonjour,

Citation:

Envoyé par cmako

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
04/10/2013, 00h25
Chtulus

Citation:

Envoyé par mnitu

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 !

;)
04/10/2013, 09h48
mnitu

Citation:

Envoyé par Chtulus

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,

Citation:

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

Citation:

Non.

Que voulez-vous que je développe ?
04/10/2013, 11h25
cmako

Merci Messieurs pour vos réponses. :ccool:

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.

;)
07/10/2013, 17h51
Scriuiw

Citation:

Envoyé par cmako

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.

8O8O8O

:aie::aie::aie:

:cry::cry::cry:

Ca pique un peu les yeux tout ça !
08/10/2013, 09h37
Garuda

Citation:

Envoyé par al1_24

Idée peut-être farfelue :oops:
Pourquoi pas un DELETE de la ligne qui vient d'être créée dans un trigger after INSERT ?

8O
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/
08/10/2013, 10h51
al1_24

En effet, j'avais oublié ça :oops:

Citation:

Envoyé par Garuda

8O
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:

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.

Citation:

Envoyé par Garuda

8O
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:

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

Non ça ne marche pas, voici une démonstration sur un exemple simplifié

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Create Table t_trg (
  2    id int primary key,
  3    lib    varchar2(10) not null
  4  )
  5  /
 
Table created
SQL> insert into t_trg values (1, 'lib1')
  2  /
 
1 row inserted
SQL> insert into t_trg values (2, 'lib2')
  2  /
 
1 row inserted
SQL> commit
  2  /
 
Commit complete
SQL> Create Or Replace Trigger bi_t_trg
  2  Before Insert On t_trg
  3  For Each Row
  4  Declare
  5    rt_trg t_trg%Rowtype;
  6  Begin
  7    Select * Into rt_trg From t_trg where id = :new.id;
  8    Delete t_trg where id = :new.id;
  9  Exception
 10    When No_Data_Found Then
 11      Null;
 12  End;
 13  /
 
Trigger created
 
SQL> 
SQL> insert into t_trg values (1,'lib11')
  2  /
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from t_trg
  2  /
 
                                     ID LIB
--------------------------------------- ----------
                                      2 lib2
                                      1 lib11
 
 
 
SQL> insert into t_trg select level, 'lib'||level from dual connect by level <= 2;
 
insert into t_trg select level, 'lib'||level from dual connect by level <= 2
 
ORA-04091: la table MNI.T_TRG est en mutation ; le déclencheur ou la fonction ne peut la voir
ORA-06512: à "MNI.BI_T_TRG", ligne 4
ORA-04088: erreur lors d'exécution du déclencheur 'MNI.BI_T_TRG'
 
SQL>

16/10/2013, 10h20
cmako

Ah :aie:

En faite, ça ne marche pas lorsqu'on insert plusieurs lignes. C'est effectivement problématique, mais l'application ne fait que les INSERT ligne par ligne, donc le problème ne sera pas rencontré. J'aurais bien voulu éviter ce problème, mais je ne vois pas trop comment. :(
16/10/2013, 15h42
mnitu

Citation:

Envoyé par cmako

...mais l'application ne fait que les INSERT ligne par ligne, donc le problème ne sera pas rencontré. ...

== Bug en attente de se manifester