Discussion :

[cmako]

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.

[mnitu]

Non.

[cmako]

Donc, il faudra checker dans l'application, et voir si l’erreur retournée est "normale"

[al1_24]

Idée peut-être farfelue
Pourquoi pas un delete de la ligne qui vient d'être créée dans un trigger after insert ?

[dariyoosh]

Bonjour,

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)

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?

[cmako]

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.

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.

[Baldrick]

Bonjour,

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

[mnitu]

Cherchez dans la documentation Oracle "DML Error Logging" et "FORALL SAVE EXCEPTIONS" et prenez la solution qui vous semble la plus appropriée.

[pachot]

Bonjour,

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

[Chtulus]

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 !

[mnitu]

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 ?

[cmako]

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.

[Scriuiw]

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 !

[Garuda]

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/

[al1_24]

En effet, j'avais oublié ça

[mnitu]

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.

[cmako]

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

[mnitu]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
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>

[cmako]

Ah

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.

[mnitu]

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