Problème trigger débutant

Version imprimable

Bonjour,

J'ai un petit problème sur le trigger "AFTER INSERT" suivant.

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
CREATE OR REPLACE TRIGGER TRIG_INS_SAPRECRUITTRANS
AFTER INSERT ON SAPRECRUITMENTTRANSFER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
	-- heure d'appel de la procedure
	begin_date_proc DATE;
	-- Variables utilis¿pour le reporting
	begin_time    NUMBER;
	-- variable utilis¿comme reference du record
	record_key    VARCHAR2(200);
	--tmpVar NUMBER;
	NONOK NUMBER(4);

BEGIN
	-- Demarre le Timer
	begin_time       := DBMS_UTILITY.GET_TIME;
	begin_date_proc  := SYSDATE;	
	-- defini la clé l'enregistrement en cours
	record_key := 'CANDIDAT ID :' || :NEW.candidat_id|| ' / REQUESTOR : ' || :OLD.requestor;
	--tmpVar := 0;
	
	NONOK := 0;	

	SELECT COUNT(*) INTO NONOK 
	FROM SAPRECRUITMENTTRANSFER	
	WHERE status <> 1 ;
	
	IF NONOK > 4 THEN
	   UPDATE SAPRECRUITMENTTRANSFER
	   SET status = '8'
	   WHERE candidat_id = :NEW.candidat_id;
	END IF;
	
	

  EXCEPTION
    WHEN OTHERS THEN
	    INSERT INTO SAPTRIGGERTRANSFER(TRIGGER_NAME, RECORD_KEY, ERROR_DATE, ERROR_TIME)
	    VALUES ('TRIG_INS_SAPRECRUITTRANS',record_key,begin_date_proc,begin_time);


END TRIG_INS_SAPRECRUITTRANS;
/

Le problème concerne les lignes en gras.
L'update ne s'exécute pas car la clause where ne semble pas fonctionner.
Et quand je regarde dans la table remplie par l'exception le record_key ne contient pas les données de la table.

Quelqu'un peut-il m'aider ?

Citation:
Envoyé par haddock333
Bonjour,

J'ai un petit problème sur le trigger "AFTER INSERT" suivant.
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 CREATE OR REPLACE TRIGGER TRIG_INS_SAPRECRUITTRANS AFTER INSERT ON SAPRECRUITMENTTRANSFER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE -- heure d'appel de la procedure begin_date_proc DATE; -- Variables utilis¿pour le reporting begin_time NUMBER; -- variable utilis¿comme reference du record record_key VARCHAR2(200); --tmpVar NUMBER; NONOK NUMBER(4); BEGIN -- Demarre le Timer begin_time := DBMS_UTILITY.GET_TIME; begin_date_proc := SYSDATE; -- defini la clé l'enregistrement en cours record_key := 'CANDIDAT ID :' || :NEW.candidat_id|| ' / REQUESTOR : ' || :OLD.requestor; --tmpVar := 0; NONOK := 0; SELECT COUNT(*) INTO NONOK FROM SAPRECRUITMENTTRANSFER WHERE status <> 1 ; IF NONOK > 4 THEN UPDATE SAPRECRUITMENTTRANSFER SET status = '8' WHERE candidat_id = :NEW.candidat_id; END IF; EXCEPTION WHEN OTHERS THEN INSERT INTO SAPTRIGGERTRANSFER(TRIGGER_NAME, RECORD_KEY, ERROR_DATE, ERROR_TIME) VALUES ('TRIG_INS_SAPRECRUITTRANS',record_key,begin_date_proc,begin_time); END TRIG_INS_SAPRECRUITTRANS; /
Le problème concerne les lignes en gras.
L'update ne s'exécute pas car la clause where ne semble pas fonctionner.
Et quand je regarde dans la table remplie par l'exception le record_key ne contient pas les données de la table.

Quelqu'un peut-il m'aider ?
2 remarques :
- tu fais un set à '8' (chaine de caractères) et avant tu testes <> 1 => NUMBER. Mets toi en cohérence avec ton test
- Apres cette correction est ce que ta requete suivante renvoie quelque chose ?
Code:

1 2 3 SELECT COUNT(*) INTO NONOK FROM SAPRECRUITMENTTRANSFER WHERE status <> 1 ;
essaie celle la aussi
Code:

1 2 3 SELECT COUNT(*) INTO NONOK FROM SAPRECRUITMENTTRANSFER WHERE status <> '1' ;
(Je me méfie toujours des conversions nombre <=> chaine) Surtout est ce que ce résultat est supérieur strictement à 4 ? ( s'il ne fait pas de MAJ et pas d'erreurs, ca veut dire qu'il rentre tout simplement pas dans le IF)

19/06/2008, 09h39
haddock333

Les 2 requêtes me renvoient un résultat mais effectivement je dois tout mettre sans les quotes.

Mon problème vient plutot des :OLD et :NEW, car je ne sais pas vraiment les utiliser.

Mon premier problèm est résolu, miantenant j'ai une erreur avec un second trigger.

ORA-04091: table SAPRECRUITMENTTRANSFER is mutating, trigger/function may not see it
ORA-06512: at "TRIG_UPDT_SAPRECRUITTRANS", line 18
ORA-04088: error during execution of trigger 'TRIG_UPDT_SAPRECRUITTRANS'

En fait j'essaye de modifier le champs STATUS d'un autre record que celui qui est mis à jour.

Voici le code de mon 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
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
70
71
72
73
 
CREATE OR REPLACE TRIGGER TRIG_UPDT_SAPRECRUITTRANS
AFTER UPDATE
ON SAPRECRUITMENTTRANSFER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
	STANDBY_STATUS NUMBER(4);
	BIGGER_DATE DATE;
	BIGGER_DATE_NB NUMBER(4);
	record_key VARCHAR2(200);
	MAX_CANDIDAT_ID NUMBER(4);
 
 
BEGIN
	 -- defini la clé l'enregistrement en cours
	record_key := 'CANDIDAT ID :' || :OLD.candidat_id|| ' / REQUESTOR : ' || :OLD.requestor;	
 
	--Vérifie que le status de départ est bien 0
	-- et qu'on l'udpdate vers une autre valeur que 1
	IF /*(:OLD.STATUS = 0) AND */:NEW.status = 1
	THEN	
		--Compte le nombre de record ayant un status égal à 8 (StandBy)
		SELECT COUNT(*) INTO STANDBY_STATUS
		FROM   SAPRECRUITMENTTRANSFER
		WHERE  status=8;
 
			IF STANDBY_STATUS > 0	
				THEN
					--Récupère la date la plus ancienne des records en StandBy
					SELECT MIN(request_date) INTO BIGGER_DATE
					FROM   SAPRECRUITMENTTRANSFER
					WHERE  status=8;
 
					--Récupère le nombre de record dont la date min est égale	
					SELECT  COUNT(*) INTO BIGGER_DATE_NB
					FROM   (SELECT MIN(request_date) 
					FROM   SAPRECRUITMENTTRANSFER
					WHERE  status=8);				
 
						IF BIGGER_DATE_NB > 1
							   THEN
							   	   	--Récupére le plus petit des candidat_id s'il ya plusieurs
									--dates max
									SELECT MIN(CANDIDAT_ID) INTO MAX_CANDIDAT_ID
									FROM   SAPRECRUITMENTTRANSFER
									WHERE  STATUS=8;
 
									--update le record de 8 à 0
									UPDATE SAPRECRUITMENTTRANSFER
									SET    STATUS=0
									WHERE  REQUEST_DATE=BIGGER_DATE
									AND    CANDIDAT_ID=MAX_CANDIDAT_ID
									AND    CANDIDAT_ID <> :OLD.CANDIDAT_ID;
 
									ELSE			
									--update le record de 8 à 0
									UPDATE SAPRECRUITMENTTRANSFER
									SET    STATUS=0
									WHERE  REQUEST_DATE=BIGGER_DATE
									AND	   CANDIDAT_ID <> :OLD.CANDIDAT_ID;
						END IF;	
			END IF; 
END IF;
-- 
-- 		EXCEPTION
-- 		WHEN OTHERS THEN
-- 			INSERT INTO SAPTRIGGERTRANSFER(TRIGGER_NAME, RECORD_KEY, ERROR_DATE, ERROR_TIME)
-- 			VALUES ('TRIG_UPDT_SAPRECRUITTRANS',record_key,SYSDATE,DBMS_UTILITY.GET_TIME);
 
 
 
END TRIG_UPDT_SAPRECRUITTRANS;

En fait le but est de modifier le status du record le plus ancien lorsque qu'un status est passé à 0...

25/06/2008, 16h33
orafrance

Citation:

Envoyé par haddock333

En fait j'essaye de modifier le champs STATUS d'un autre record que celui qui est mis à jour.

ce n'est pas possible :?

Ou alors il faut faire une procedure AUTONOMOUS_TRANSACTION mais ça pose d'autre problème (genre l'update de l'autre ligne commité même si le trigger est en erreur) :?
25/06/2008, 16h38
haddock333

Citation:

Envoyé par orafrance

ce n'est pas possible :?

Ou alors il faut faire une procedure AUTONOMOUS_TRANSACTION mais ça pose d'autre problème (genre l'update de l'autre ligne commité même si le trigger est en erreur) :?

Donc la seule solution est d'utiliser une table temporaire ??
25/06/2008, 17h02
orafrance

oui... et encore... j'vois pas bien ce que tu vas en faire de ta table temporaire sachant que même un SELECT n'est pas autorisée sur la table qui porte le trigger :?
25/06/2008, 17h04
haddock333

Citation:

Envoyé par orafrance

oui... et encore... j'vois pas bien ce que tu vas en faire de ta table temporaire sachant que même un SELECT n'est pas autorisée sur la table qui porte le trigger :?

Mais un insert depuis la table temporaire fonctionnerait ?

Ou alors si je fais appel à une procédure ?
25/06/2008, 17h22
orafrance

essaye :)
25/06/2008, 22h40
HLM59

Une procédure n'y changera rien , à part externaliser le code.

Une autre piste : créer un job avec une date d'exécution décaler de qqs secondes.
26/06/2008, 16h39
gaboo_bl

Sinon pour un problème plus ou moins similaire j'avais exploré une autre piste, mais c'était y a longtemps et c'est compliqué donc désolé si je suis pas super précis:

le principe c'est de stocker les données identifiant les lignes à mettre à jour dans ton trigger mais sans faire la mise à jour. Il me semble qu'on peut stocker çà dans une variable de package. ensuite il suffit de déclencher la mise à jour de toutes les lignes dans un deuxième trigger de niveau ordre (qui ne se déclenche qu'une fois que l'ordre sql est entièrement passé et pas à chaque ligne).

J'avais trouvé çà là dedans
26/06/2008, 17h11
mnitu

Pour tous les autres débutants qui aiment les déclencheurs et se débrouillent un peu en anglais, lisez ce blog de Tom Kyte, et demandez vous pour quoi il hais les déclencheurs, pragma autonomous trnsaction, etc.