Discussion :

[4rocky4]

Bonjour tout le monde,

J'ai un script qui est généré par SQL Developer lors d'une migration de Database.
Ce script me permet de créer la structure de la base sous Oracle.
Et il y a plusieurs triggers qui attirent mon attention car j'ai du mal à comprendre ce qu'ils font.

Voilà un exemple :

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
CREATE SEQUENCE  T1_FAMILLE_ID_FAMILLE_SEQ  
  MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1  NOCYCLE ;
 
CREATE OR REPLACE TRIGGER T1_FAMILLE_ID_FAMILLE_TRG BEFORE INSERT OR UPDATE ON T1_FAMILLE
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.ID_FAMILLE IS NULL THEN
    SELECT  T1_FAMILLE_ID_FAMILLE_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID_FAMILLE) INTO v_newVal FROM T1_FAMILLE;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT T1_FAMILLE_ID_FAMILLE_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    -- save this to emulate @@identity
   sqlserver_utilities.identity := v_newVal; 
   -- assign the value from the sequence to emulate the identity column
   :new.ID_FAMILLE := v_newVal;
  END IF;
END;
 
/

A la première visualisation, j'ai pensé que c'était la gestion de l'auto-incrément mais vue les nombres que je vois dans ma base maintenant que j'ai importer les données, je ne sais plus ...

Si ça serait de l'auto-incrément, les valeurs commenceraient à 1 or là, cela commence parfois par des valeurs de plus de 2 000 000.

Pouvez-vous m'éclaircir un peu sur ce sujet ?

Je vous remercie par avance pour vos suggestions.

[4rocky4]

J'ai fais un test :
J'ai créer la table T1_FAMILLE puis j'y ai insérer une ligne.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE T1_FAMILLE (
  ID_FAMILLE NUMBER(10,0) NOT NULL,
  DATE_LAST_MODIF TIMESTAMP(6) NOT NULL,
  IDECLI VARCHAR2(40 CHAR) NOT NULL,
  NUM_LIGNE NUMBER(5,0) NOT NULL,
  REFERENCE VARCHAR2(30 CHAR) NOT NULL,
  LOT VARCHAR2(30 CHAR),
  ODF VARCHAR2(30 CHAR)
);
 
INSERT INTO "DBO_TEST"."T1_FAMILLE " (ID_FAMILLE, DATE_LAST_MODIF, IDECLI, NUM_LIGNE, REFERENCE, LOT, ODF) VALUES ('58', TO_TIMESTAMP('08/04/09 09:43:50,1', 'DD/MM/RR HH24:MI:SSXFF'), 'test', '1', 'test', 'test', 'test')

La valeur d'ID_FAMILLE est bien 58.
Si l'auto-incrément serait en place, sa valeur serait de 1.

Donc je ne vois pas ce que fait ce trigger ...

Quelqu'un peut me l'expliquer ?

[argoet]

A premiere Vue votre Trigger Remet d'applomb une sequence qui existait préalablement
Vue que vous etes sur une migration de base : votre Sequence à sans doute redemarrer à 0
donc ici une verification est réalisé par le Trigger pour savoir si l'on est sur la premiere itération de la séquence

Code :

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

IF v_newVal = 1 THEN

, si c'est le cas on rentre dans une boucle de nextval afin de se mettre en concordance avec la derniere valeur connue inserrée dans votre table

Code :

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

max(ID_FAMILLE)

Code :

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

1
2
3
4
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT T1_FAMILLE_ID_FAMILLE_SEQ.NEXTVAL INTO v_incval FROM dual;
      END LOOP;

[McM]

Si le champ id_famille est NULL => on le renseigne avec le prochain n° de séquence

Code :

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

1
2
3
4
5
IF INSERTING AND :new.ID_FAMILLE IS NULL
THEN
 SELECT  T1_FAMILLE_ID_FAMILLE_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
:new.ID_FAMILLE := v_newVal;
END IF;

Tout le reste est faux

Les erreurs sont :
Erreur de mutating table si la séquence vaut 1, tu n'as pas le droit de faire un SELECT sur la table du trigger.

Les absurdités :
Le trigger est aussi exécuté pour les UPDATE (et dans ce cas le trigger ne fait rien...)

Le code pourrait être simplifié à ça :

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
CREATE OR REPLACE TRIGGER T1_FAMILLE_ID_FAMILLE_TRG 
BEFORE INSERT ON T1_FAMILLE
FOR EACH ROW
WHEN (NEW.ID_FAMILLE IS NULL)
DECLARE 
	v_newVal NUMBER(12) := 0;
BEGIN
 
    SELECT T1_FAMILLE_ID_FAMILLE_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
 
     -- save this to emulate @@identity
   	sqlserver_utilities.identity := v_newVal; 
   	-- assign the value from the sequence to emulate the identity column
   :NEW.ID_FAMILLE := v_newVal;
 
END; 
/

Le coup de la séquence à 1 est fait pour remettre la séquence au niveau des données ID_FAMILLE de la table, mais tu ne peux le faire en trigger.
Il faut que ce soit un script exécuté en pl/sql

[4rocky4]

Je sais que je peux le remplacer par quelque chose de plus simple

Le problème c'est que le script généré par SQL Developer lors de la migration fait 5000 lignes.
De plus cette migration devra être faite plusieurs fois dans le mois donc il est un peu embêtant de tout modifier à chaque fois.
Moi qui pensais que la gestion de l'auto-incrément était géré ...
Donc il va falloir que je modifie tous les triggers à chaque fois ?

---------------

Sinon je ne vois toujours pas l'utilité de ces triggers générés dans le script de création de la structure. Les données migrent après la création de la structure donc on peut créer les triggers et séquence avant la migration des données. La séquence sera forcément à 0.

---------------

Erreur de mutating table si la séquence vaut 1, tu n'as pas le droit de faire un SELECT sur la table du trigger.

Je n'ai aucune erreur lors de l'insertion d'une ligne ...
L'outil Oracle SQL Developer écrirait des choses fausses ?

[McM]

Je n'ai aucune erreur lors de l'insertion d'une ligne ...
L'outil Oracle SQL Developer écrirait des choses fausses ?

Recrée ta séquence à 0. (Car elle est créée à 1 de départ. Donc le premier nextval va donner 2.)
Insère une ligne.

Bien sur, en mettant NULL dans id_famille, pas ton '58' (qui d'ailleurs n'est pas correct, faudrait mettre un NUMBER, soit 58)

Code :

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

1
2
INSERT INTO "DBO_TEST"."T1_FAMILLE " (ID_FAMILLE, DATE_LAST_MODIF, IDECLI, NUM_LIGNE, REFERENCE, LOT, ODF)
VALUES (NULL, TO_TIMESTAMP('08/04/09 09:43:50,1', 'DD/MM/RR HH24:MI:SSXFF'), 'test', '1', 'test', 'test', 'test')

[4rocky4]

J'ai toujours cette erreur quand je met NULL pour ID_FAMILLE:

Erreur lors de l'enregistrement de la table "DBO_TEST"."T1_FAMILLE" :
Ligne 2 : ORA-01400: impossible d'insérer NULL dans ("DBO_TEST"."T1_FAMILLE"."ID_FAMILLE")
ORA-06512: à ligne 1

De toute façon, lorsque je vais importer les données, il n'y aura pas de valeur NULL.

Donc le mieux à faire et de supprimer tous les triggers de ce genre puis de les recréer correctement non ?

[McM]

T'as bien le trigger qui est valide et enabled sur cette table ?

[4rocky4]

T'as bien le trigger qui est valide

Tu as dit tout à l'heure qu'il était faux.

et enabled sur cette table ?

oui.

----------------

J'ai tout supprimé pour recréer juste ce dont j'avais besoin. (j'utilise la table T1_PRODUIT pour mes tests)

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
CREATE TABLE T1_PRODUIT (
  ID_PRODUIT NUMBER(10,0) NOT NULL,
  DATE_LAST_MODIF TIMESTAMP(6) NOT NULL,
  IDECLI VARCHAR2(40 CHAR) NOT NULL,
  NUM_LIGNE NUMBER(5,0) NOT NULL,
  REFERENCE VARCHAR2(30 CHAR) NOT NULL,
  LOT VARCHAR2(30 CHAR),
  ODF VARCHAR2(30 CHAR)
);
 
CREATE SEQUENCE  T1_PRODUIT_ID_PRODUIT_SEQ  
  MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1  NOCYCLE ;
 
CREATE OR REPLACE TRIGGER T1_PRODUIT_ID_PRODUIT_TRG BEFORE INSERT OR UPDATE ON T1_PRODUIT
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.ID_PRODUIT IS NULL THEN
    SELECT  T1_PRODUIT_ID_PRODUIT_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID_PRODUIT) INTO v_newVal FROM T1_PRODUIT;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT T1_PRODUIT_ID_PRODUIT_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    -- save this to emulate @@identity
   sqlserver_utilities.identity := v_newVal; 
   -- assign the value from the sequence to emulate the identity column
   :new.ID_PRODUIT := v_newVal;
  END IF;
END;
 
/

Lorsque j'insère une ligne dans la table, j'ai l'erreur ORA-04098.

Code :

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

INSERT INTO "DBO_TEST"."T1_PRODUIT" (ID_PRODUIT, DATE_LAST_MODIF, IDECLI, NUM_LIGNE, REFERENCE, LOT, ODF) VALUES (NULL, TO_TIMESTAMP('08/04/09 09:43:50,1', 'DD/MM/RR HH24:MI:SS,FF'), 'test', '2', 'test', 'test', 'test')

Cela vient d'un problème dans le trigger ...

-----------------

J'ai donc changer le trigger et la séquence comme ceci :

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
CREATE SEQUENCE T1_PRODUIT_ID_PRODUIT_TRG
  START WITH 1
  MAXVALUE 999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;
 
 CREATE OR REPLACE TRIGGER T1_PRODUIT_ID_PRODUIT_TRG
   BEFORE INSERT
   ON T1_PRODUIT
   FOR EACH ROW
BEGIN
   SELECT T1_PRODUIT_ID_PRODUIT_TRG.NEXTVAL
     INTO :NEW.ID_PRODUIT
     FROM DUAL;
END;
/

Et là l'insertion marche parfaitement et l'auto-incrément est bien géré en commençant à 1 même si je met NULL dans l'insert.

Vous allez me dire : "Bah change tes triggers et séquences "
Mais vue le gros travail qui il y a à faire pour modifier tout cela, je préfère être sûr que cela fasse la même chose si je peux avoir un souci plus tard en faisant ça.

[McM]

ORA-04098: trigger 'string.string' is invalid and failed re-validation

En fait quand tu compiles ton trigger, fais un show_error après pour voir les erreurs, ou alors compiles le sous SQL_DEV et vois ce qu'il te dit.

[4rocky4]

Au final j'ai modifier ce trigger pour mettre uniquement l'essentiel et ça me va
Merci pour vos réponses