creation de trigger

Version imprimable

Voir 40 message(s) de cette discussion en une page

creation de trigger

bonjour,
je souhaite écrire un trigger dans une table, dont la clé est (LOGIN, CODE) , concernant un champ donné (FLAG)
FLAG = 0 ou 1

si on met à FLAG à 1, tous les autres enregistrements vont avoir FLAG = 0..
comment je peux écrire ça..??

j'ai fait ceci mais ça me génère une erreur comme quoi il est déjà en train de bidouiller dans la table..(MyTable en mutation.. le déclencheur ne peut la voir..)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE OR REPLACE TRIGGER TRIG_MyTable BEFORE INSERT OR UPDATE ON MyTable FOR EACH ROW BEGIN IF UPDATING THEN IF :New.FLAG = 1 THEN UPDATE MyTable SET FLAG= 0 WHERE Login = :NEW.Login AND Code <> :NEW.Code; END IF; END IF; END; /

27/11/2006, 16h42
VinceTlse

Tu ne pourras pas faire de trigger car ta table est en cours de modification (Table Mutating ...).
Pourquoi ne pas le faire en 2 passes?
1ere requete qui met a jour le falg pour (LOGIN, CODE)
2 ieme requete qui met le flag à 0 pour tous les autres couple différent de (LOGIN, CODE)
27/11/2006, 16h53
plaineR

Merci d'utiliser la fonction rechercher vous trouverez toutes les informations nécessaires pour résoudre ce problème, cette question étant maintes fois posée.
28/11/2006, 11h03
freestyler

c'est vrai..
à chaque fois, on me renvoie sur un autre lien, et chacun propose une méthode..
28/11/2006, 11h11
plaineR

Eh bien toutes les solutions pour éviter l'erreur ORA-04091 sont là :
http://sgbd.developpez.com/oracle/ora-04091/
28/11/2006, 11h28
freestyler

j'ai déjà vu ce lien..

d'abord, je cherche à mettre à jour toute ma table excepté l'enregistrement qui a changé..

ensuite, comme je suis loin d'etre expert en bases de données, j'ignore quelles conséquences a le fait d'ajouter des tables temporaires, ou l'ajout d'un 2ème ou 3ème trigger..(problème de performance)

par ailleurs, le coup des exceptions n'est pas du tout intéressant pour ce cas là car oracle intercepte des ecceptions qui n'ont pas lieu d'être..

voila..
Ce n'est pas faute de pas avoir cherché, et excuser mon manque de compétences dans ce domaine..
28/11/2006, 12h19
nuke_y

Citation:

Envoyé par freestyler

ensuite, comme je suis loin d'etre expert en bases de données, j'ignore quelles conséquences a le fait d'ajouter des tables temporaires, ou l'ajout d'un 2ème ou 3ème trigger..(problème de performance)

Ca complique l'architecture, les perfs etc.

Citation:

Envoyé par freestyler

par ailleurs, le coup des exceptions n'est pas du tout intéressant pour ce cas là car oracle intercepte des ecceptions qui n'ont pas lieu d'être..

Comme dit dans le tutoriel, l'exception levée est une information, libre à toi de la considérer ou pas. Mais il faut l'intercepter si tu veux pouvoir faire ce que tu veux.
28/11/2006, 12h32
McM
Plutôt que de créer une usine à gaz, tu peux pas créer une sous table
Code:

1 2 3 4 5 CREATE TABLE FLAG ( LOGIN NUMBER PRIMARY KEY, CODE VARCHAR2(10) )
Et dans code tu mets le code du flag = 1 (par trigger sur ta table login)
28/11/2006, 12h35
nuke_y

Ou modifier la façon dont les données sont insérées ? Au lieu d'un INSERT ou UPDATE tout bête tu peux imaginer de créer des fonctions spécifiques à ces tables qui, respectivement, insère la nouvelles ligne PUIS update les autres lignes et update la nouvelles valeur PUIS update les autres lignes.
28/11/2006, 14h10
freestyler

excusez moi mais je croyais qu'il s'agissait d'un simple problème sous oracle..
et chacun continue de me balancer une idée et jamais une solution que l'on sait qui fonctionne..

en fait..
je me suis renseigné auprès de notre admin
--> pas moyen d'ajouter une nouvelle table..

c'est pourtant simple.. si FLAG passe à 1, tous les autres FLAG passent à 0!
28/11/2006, 14h14
nuke_y

Moi je me pose une question :
tu modifies une ligne
|-> trigger se déclenche
|---> modification de toutes les autres lignes
|-----> pour chaque ligne le trigger se déclenche.
|-------> etc.

non ?
28/11/2006, 14h20
plaineR

Les solutions que l'on te donne fonctionnent, mais tu n'as pas l'air d'être intéressé.

Je te donne mon avis après tu en fais ce que tu veux : dans ton cas, puisque tu ne peux pas créer de table temporaire, j'opterai pour une des méthodes suivantes :
- trigger instead of
- gestion d'exception dans le trigger

avec une préférence pour cette dernière solution, puisque c'est celle qui est la plus proche de ce que tu as déjà fait (il ne reste qu'à ajouiter un bloc exception ce qui ne me semble pas insurmontable).
28/11/2006, 14h24
plaineR

Citation:

Envoyé par freestyler

par ailleurs, le coup des exceptions n'est pas du tout intéressant pour ce cas là car oracle intercepte des ecceptions qui n'ont pas lieu d'être..

8O Peux-tu donner un exemple ? Je n'ai jamais vu oracle intecepter une autre exception que celle qu'on lui a demandé d'intercepter.
28/11/2006, 14h33
freestyler

Citation:

Moi je me pose une question :
tu modifies une ligne
|-> trigger se déclenche
|---> modification de toutes les autres lignes
|-----> pour chaque ligne le trigger se déclenche.
|-------> etc.

effectivement..
sauf que le trigger se déclenche 1 seule fois !
cela veut dire que je ne peux avoir qu'un seul FLAG à 1 dans toute la table..

Par contre,
le trigger instead of ne se fait que sur une vue..
la gestion des exceptions, je n'arrive pas à la faire marcher finalement.. je suis encore en train d'essayer..
28/11/2006, 14h35
nuke_y

Euh je croyais qu'un trigger instead of ne pouvait se faire que sur une vue. S'il ne peut pas créer de table, je ne suis pas sur qu'il puisse créer une vue.

Sinon je vote aussi pour la gestion de l'exception.

Et le problème que j'ai soulevé au dessus ne risque pas de se produire puisque il a déjà géré un update de toutes les lignes seulement si la ligne mise à jour a le flag=1.

EDIT:
vi vi freestyler, j'ai relu ton code et j'ai vu que tu l'avais déjà géré ;)

bon..
j'ai écrit ceci..
mais je ne sais pas quoi faire quand l'exception se produit..
comme il est écrit, le nouveau champ est bien mis à 1, mais les autres champs ne sont pas remis à 0 (on dirait que le trigger n'existe pas!)

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
 
CREATE OR REPLACE trigger TRIG_MyTable_Active 
AFTER UPDATE
on MyTable
For each row
DECLARE
    TABLE_MUTANTE EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_MUTANTE, -4091);
begin
 
if :New.FLAG= 1 then
 
		UPDATE MyTable
		SET FLAG= 0
		WHERE :New.Login = Login 
		AND :New.Code <> Code;
 
END IF;
 
EXCEPTION
		 WHEN TABLE_MUTANTE THEN 
                               DBMS_OUTPUT.PUT_LINE('Table Mutante...');
 
END;
/

Citation:

Envoyé par freestyler

le trigger instead of ne se fait que sur une vue..

Je n'ai pas dit le contraire, mais tu n'as pas dit que tu ne pouvais pas créer de vue.

Citation:

Envoyé par nuke_y

Euh je croyais qu'un trigger instead of ne pouvait se faire que sur une vue. S'il ne peut pas créer de table, je ne suis pas sur qu'il puisse créer une vue.

Les conséquences de création d'une vue ne sont pas les mêmes, c'est juste une requête que tu stockes en base et pas un doublons de données ;)

Essaie :
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 CREATE OR REPLACE TRIGGER TRIG_MyTable BEFORE INSERT OR UPDATE ON MyTable FOR EACH ROW DECLARE err_table_mutante exception; pragma exception_init(err_table_mutante, -4091); BEGIN IF UPDATING THEN IF :New.FLAG = 1 THEN begin UPDATE MyTable SET FLAG= 0 WHERE Login = :NEW.Login AND Code <> :NEW.Code; exception when err_table_mutante then null; end; END IF; END IF; END; /

28/11/2006, 14h56
freestyler

en gros t'as remplacé mon after par un before..
Ainsi que le coup du NULL..

Ca marche toujours pas.. je veux dire rien ne se passe quand FLAG passe à 1 !
28/11/2006, 15h21
freestyler

Vous me demandez de gérer des exceptions pour ignorer les messages d'erreur et non pas pour pouvoir effectuer des traitements sur la table dans le cas où une exception se produit..

donc si je comprends bien, il faudra que je passe par la création d'objets supplémentaires!

qqn peut-il m'aider pour un trigger INSTEAD OF??

Citation:

Envoyé par freestyler

en gros t'as remplacé mon after par un before..
Ainsi que le coup du NULL..

Ca marche toujours pas.. je veux dire rien ne se passe quand FLAG passe à 1 !

Excuse nos 2 messages ont du se croiser. ;)

Citation:

Envoyé par freestyler

Vous me demandez de gérer des exceptions pour ignorer les messages d'erreur et non pas pour pouvoir effectuer des traitements sur la table dans le cas où une exception se produit..

donc si je comprends bien, il faudra que je passe par la création d'objets supplémentaires!

qqn peut-il m'aider pour un trigger INSTEAD OF??

Oui, en fait à bien y réfléchir tu n'as pas le choix... Voici un exemple de ce que tu peux faire :

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
SQL> create or replace trigger trg_vue_login instead of update on vue_login for each row
  2  begin
  3    if :new.flag = 1 then
  4       update login set flag = 0
  5       where log != :new.log;
  6       update login set flag = 1
  7       where log = :new.log;
  8    end if;
  9  end;
 10  /
Trigger created.
 
SQL> update vue_login set flag = 1 where log = 'a';
1 row updated.
 
SQL> select * from login;
LOG                                 FLAG
------------------------------ ---------
a                                      1
b                                      0
c                                      0
 
SQL> update vue_login set flag = 1 where log = 'b';
1 row updated.
 
SQL> select * from login;
LOG                                 FLAG
------------------------------ ---------
a                                      0
b                                      1
c                                      0

28/11/2006, 16h18
freestyler

bon..
j'ai essayé d'adapter tout ça..

je suis sur TOAD en ce moment..
donc les requetes UPDATE je les fais à la main..

si j'ai bien compris, il faut que j'agisse sur la vue pour faire des modifications????
29/11/2006, 11h19
freestyler

le trigger instead of marche très bien..
je modifie les champs dans ma vue..et le résultat est bel et bien dans la table principale (login)

Le problème, c'est que je touche à la base depuis des DLLs..et je peux pas attaquer des vues pour faire des modifications..
29/11/2006, 15h32
plaineR

Dans ce cas, je ne vois que la dernière méthode proposée dans l'article de Pomalaix qui puisse te convenir :
- tu crées un entête de package avec trois variables (login, code, flag)
- un declencheur before update for each row qui mémorise ton flag et ton login et ton code dans les variables de package
- un déclencheur after update qui met à jour ta table suivant les variables de package mémorisée.
29/11/2006, 16h10
freestyler

Je me perds là 8O

d'abord, dans le before update, il faut que je mémorise quelles variables?? là où le flag est à 1 (donc la seule ligne)

puis dans le after update..j'écris le code que j'avais déja écrit ??

excuse moi mais ça fait un petit moment que je puise dans l'exemple du lien que tu m'as donné..
je te dis franchement.. c'est bp trop compliqué pour moi!

Comme PlaineR :
Je vais développer mon idée des 2 tables :

Table TLOGIN (login, code, flag) PK Login/Code
Table TLOGIN_FLAG (login, code) PK Login
FK de login/code vers TLOGIN (login/code)

Le flag de TLOGIN ne sert qu'à déclencher le trigger, on peut aussi utiliser une variable de session dans un package

Trigger sur TLOGIN UPDATE FOR EACH ROWS
Code:

1 2 3 4 5 6 7 8 9 IF :new.flag = 1 THEN :new.flag := null; -- la colonne ne sert à rien UPDATE TLOGIN_FLAG set code = :new.code WHERE login = :new.login; IF SQL%ROWCOUNT = 0 THEN INSERT INTO TLOGIN_FLAG (login, code) VALUES (:new_login, :new_code); END IF; END IF;
Pour savoir quel login/code est au flag = 1, ce'st celui qui est dans la table TLOGIN_FLAG

29/11/2006, 17h18
freestyler
mais dans ton script, où est-ce qu'on met la colonne FLAG à 0 pour tous les autres enregistrements??

je précise que ma table principale c'est:
MyTable(Login, Code, Flag)

et que en principe je devrais faire:
Code:

1 2 3 4 5 6 7 For each row... If :New.Flag = 1 THEN UPDATE MyTable SET Flag = 0 where login = :New.Login And code <> :New.Code; END IF;
29/11/2006, 19h23
remi4444

idée...

Tiens, j'ai une idée rigolote pour économiser les packages et/ou tables temporaires.....

je vois le truc en 2 temps
- Dans un trigger before insert/update "for each row" tu capte la modif de flag à 1, et au lieu de mettre 1 tu remplace par 2!
- Dans un trigger after statement insert/update (il suffit de ne pas mettre le "for each row") ta table n'est plus en mutation donc tu peux agir dessus... et là tu commence par chercher et remplacer le 1 par 0, puis le 2 par 1, clic/clac l'affaire est dans le sac ;)

Reste à afiner pour les cas ou on fait une modif de plus d'une ligne à la fois, il faudra choisir arbitrairement, une des lignes qui va rester flaguée à 1 quand toutes les autres seront passeront à 0....
29/11/2006, 19h51
pifor

Sans trigger

On pourrait aussi envisager de se passer de triggers si on encapsule toutes les écritures (INSERT/UPDATE/DELETE) dans la table en question dans des procédures stockées et si on se pose comme contrainte de ne pas faire d'INSERT/UPDATE/DELETE directement mais de toujours passer par les procédures stockées.

Cette contrainte peut être réalisée en interdisant la connexion dans l'application par le compte propriétaire du schéma et ne donnant que les droits d'exécution sur les procédures à d'autres comptes distincts (en plus du SELECT).
29/11/2006, 21h05
nuke_y

C'est ce que je proposais ici :

Citation:

Envoyé par nuke_y

Ou modifier la façon dont les données sont insérées ? Au lieu d'un INSERT ou UPDATE tout bête tu peux imaginer de créer des fonctions spécifiques à ces tables qui, respectivement, insère la nouvelles ligne PUIS update les autres lignes et update la nouvelles valeur PUIS update les autres lignes.
30/11/2006, 00h31
remi4444

c'est un peu l'esprit du "instead of" c'est vrai que c'est peut etre mieux d'un point de vue programmation mais la table n'a plus sa consistance propre... tant qu'à passer par des fonctions, à ce moment là, autant le faire directement depuis l'applicatif....
30/11/2006, 10h06
freestyler
Citation:

- Dans un trigger before insert/update "for each row" tu capte la modif de flag à 1, et au lieu de mettre 1 tu remplace par 2!

la modification du flag à 1, c'est moi qui la fait!
tu me demandes dans un premier temps d'écrire ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE OR REPLACE TRIGGER TRIGGER_1 BEFORE UPDATE ON MyTable FOR EACH ROW BEGIN IF :New.FLAG =1 then UPDATE MyTable SET FLAG= 2; END IF; END;
ce qui à mon avis n'a pas d'intéret car la table est toujours en mutation..

donc à mon avis, tu veux dire autre chose que je n'ai pas compris :lol:
30/11/2006, 10h14
orafrance
plutôt :
Code:

1 2 3 4 IF :New.FLAG =1 then :New.FLAG := 2; END IF;
mutation de table = SELECT, INSERT ou UPDATE sur la table qui déclenche le trigger.

Code du 1er trigger..

Code:

1
2
3
4
5
6
7
8
9
10
 
CREATE OR REPLACE TRIGGER TRIGGER_1
BEFORE UPDATE
ON MyTable
FOR EACH ROW
BEGIN
	   IF :New.Active =1 then 
	   	  :New.Active :=2;	  	
	   END IF;  	  
END;

Code du 2ème trigger..

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
 
CREATE OR REPLACE TRIGGER TRIGGER_2
AFTER UPDATE
ON MyTable 
DECLARE
	   tmpvar  INTEGER;
	   tmpvar2 INTEGER;
	   tmpstr  VARCHAR2(50);
	   tmpstr2 VARCHAR2(50);
 
BEGIN
	 Select login, code
	 into tmpvar, tmpstr
	 FROM MyTable
	 where Flag= 1;
 
	 Select login, code
	 into tmpvar2, tmpstr2
	 FROM MyTable
	 where Flag= 2;
 
	 UPDATE MyTable
	 SET Flag= 0
	 WHERE Login = tmpvar
	 AND Code= tmpstr;
 
 	 UPDATE MyTable
	 SET Flag= 1
	 WHERE Login = tmpvar2
	 AND Code= tmpstr2; 
END ;

Le premier fonctionne bien..
J'ai toujours le problème de la table mutante pour le 2ème trigger..
Je dois avoir loopé un truc dans ton idée..

30/11/2006, 10h57
remi4444

En fait il y a un problème de récursivité chez moi....

Il faut faire gaffe à pas faire des updates en cascades.... :koi:
30/11/2006, 11h13
plaineR

Citation:

Envoyé par remi4444

je vois le truc en 2 temps
- Dans un trigger before insert/update "for each row" tu capte la modif de flag à 1, et au lieu de mettre 1 tu remplace par 2!
- Dans un trigger after statement insert/update (il suffit de ne pas mettre le "for each row") ta table n'est plus en mutation donc tu peux agir dessus... et là tu commence par chercher et remplacer le 1 par 0, puis le 2 par 1, clic/clac l'affaire est dans le sac ;)

Non, ça ne peut pas marcher : ton trigger after update va redéclencher ton trigger for each row et soit cela va boucler, soit tu vas te retrouver avec un flag égal à 2.

[EDIT] Excuse je n'avais pas vu ton dernier post ;) [/EDIT]

Oui c'était le soucis, j'avais pas fait gaffe que meme quand l'update se fait sur 0 ligne, le trigger "after statement" se déclenche quand meme :aie:

Mais je crois que j'ai trouvé:

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
 
CREATE TABLE TOTO (LOGIN varchar2(50),CODE varchar2(50),FLAG NUMBER(1) default 0);
 
 
CREATE OR REPLACE TRIGGER TRG_TOTO_BIU_ROW
BEFORE INSERT OR UPDATE ON TOTO 
FOR EACH ROW
BEGIN
   -- test si il y a un changement de flag vers la valeur 1 
   -- dans ce cas on le passe à 2 pour signaler que c'est une nouvelle valeur 
   --return;
   IF :new.FLAG = 1 
     and (INSERTING 
	      OR (UPDATING  AND  nvl(:old.FLAG,0) != 1 AND nvl(:old.FLAG,0) != 2 /*pour pas tourner en rond */ )
         )
	  then
	     :new.FLAG := 2;
	  end if;
  END ;
/
 
 
CREATE OR REPLACE TRIGGER TRG_TOTO_AIU_STATEMENT
AFTER INSERT OR UPDATE ON TOTO 
  DECLARE
  l_login TOTO.LOGIN%TYPE;
  l_code TOTO.CODE%TYPE;
  BEGIN
   -- apres le statement, la table n'est plus en mutation, on peut donc agir dessus.
   -- recherche d'une ligne flaguée à 2 
   select CODE,LOGIN into l_code,l_login from TOTO where FLAG = 2 and rownum = 1;
 
    -- FLAG 1 sur la ligne modifiée 
	-- il faut d'abord faire cette modif sinon ça tourne en rond....
   update TOTO set FLAG = 1 where CODE = l_code  and LOGIN = l_login;
 
  -- FLAG 0 sur toutes les autres lignes 
   update TOTO set FLAG = 0 where FLAG in (1,2) and (CODE != l_code  or LOGIN != l_login);
 
 
  EXCEPTION WHEN NO_DATA_FOUND THEN
    -- pas d'action si il n'y a pas de 2 
	null;    
  END ;
/

Un problème digne des meilleures écoles d'informatique... ;)

30/11/2006, 11h57
freestyler

Chapeau Remi...

ça marche à merveille..

Il faut vraiment le marquer ce post là, parce que il n'existe nulle part..
Parfois, il suffit d'une petite bidouille pour contourner des problèmes de création de tables suppémentaires ou je ne sais quoi d'autres..

Merci à tout le monde d'avoir échangé ces quelques propos très intéressants..

;)

Salut

Le trigger suivant qui en cencé générer un message d'erreur est accepté mais avec des erreurs de compilation je ne comprends pas du tout pourquoi est ce que qlq un a une idée?
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE OR REPLACE TRIGGER tg_integrite BEFORE INSERT OR UPDATE ON VOYAGES FOR EACH ROW BEGIN IF :NEW.id_client NOT IN (SELECT NUM_client FROM CLIENTS, VOYAGES WHERE CLIENTS.NUM_CLIENT = VOYAGES.:NEW.id_client) THEN RAISE_APPLICATION_ERROR(-20001,'Le client n''existe pas'); END IF; END tg_integrite;
merci d'avance

11/12/2006, 08h43
orafrance

recherche table mutante ou mutating table. Tu n'as pas le droit de sélectionner la table qui porte le trigger ;)

Enléve VOYAGES du SELECT et ça ira beaucoup mieux :mrgreen:

helas tjs meme erreur de compilation mais trigger créé.

Code:

1
2
3
4
5
6
7
8
9
10
 
CREATE OR REPLACE TRIGGER tg_integrite
   BEFORE INSERT OR UPDATE ON VOYAGES
FOR EACH ROW
BEGIN
   IF :NEW.id_client not in (SELECT NUM_client FROM CLIENTS);
THEN
      RAISE_APPLICATION_ERROR(-20001,'Le client n'existe pas');
   END IF;
END tg_integrite;

Je sèche complètement

S.o.S

Voir 40 message(s) de cette discussion en une page

Fuseau horaire GMT +2. Il est actuellement 12h29.