Problème avec trigger insert

**zaydoo** · 05/10/2011, 10h30

bonjour,
je veux faire un trigger qui bloque l'insertion dans une table pour certaine valeurs, et faire l'insertion dans une autre table. merci bcp

**ORA-007** · 05/10/2011, 10h34

Bonjour,

Qu'as tu déjà essayé ?

**islamov2000** · 05/10/2011, 10h40

quelle sont tes initiatives.

**zaydoo** · 05/10/2011, 10h54

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
CREATE OR REPLACE TRIGGER AR.RA_INTERFACE_AFTER_INSERT
BEFORE INSERT
    ON RA_INTERFACE_LINES_ALL REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
DECLARE   
 
    v_type varchar2(10);
    exception_1 exception;
 
BEGIN
 
select distinct ractta.type into v_type
  from ar.ra_batch_sources_all rbsa,ar.ra_cust_trx_types_all ractta
  where rbsa.name = :NEW.batch_source_name
  and ractta.cust_trx_type_id = :NEW.cust_trx_type_id
  and rbsa.org_id = :NEW.org_id;
 
  if ( :NEW.org_id=158 and :NEW.INTERFACE_LINE_ATTRIBUTE4='0' and :NEW.INTERFACE_LINE_CONTEXT='ORDER ENTRY' and v_type='INV') then
 
   INSERT INTO RANDA.SMT_RA_INTERFACE_LINES_ALL 
  (LINE_ID, ... )
        VALUES
  (:NEW.LINE_ID, ... );
 
   raise exception_1;
   end if;
 
    EXCEPTION
     WHEN exception_1 THEN RAISE;
END RA_INTERFACE_AFTER_INSERT;
/

**islamov2000** · 05/10/2011, 15h30

je ne voix pas le probleme

**ORA-007** · 05/10/2011, 15h52

Bonjour,

Je pense qu'il va te falloir 2 triggers + 1 collection pour gerer ce cas.

1) Creer une collection publique destinée à stocker les pk de la table RA_INTERFACE_LINES_ALL REFERENCING.

2) Créer un trigger sur before insert sur la table RA_INTERFACE_LINES_ALL REFERENCING.
Si tes conditions sont remplies, alimente la table SMT_RA_INTERFACE_LINES_ALL
et
alimente la collection avec de quoi identifier la ligne inserée sur RA_INTERFACE_LINES_ALL

3) creer un deuxieme trigger sur la table SMT_RA_INTERFACE_LINES_ALL
sur after insert
Recupere la collection alimentée par le premier trigger et drop les lignes de la table RA_INTERFACE_LINES_ALL

4) detruit la collection avec un .DELETE en fin de processus.

**zaydoo** · 05/10/2011, 15h52

Envoyé par boussafi

je ne voix pas le probleme

le problème c'est que dans le cas où la condition if est validé et le trigger goto exception_1 il ne commit pas l'insertion à l'autre table (RANDA.SMT_RA_INTERFACE_LINES_ALL) et on ne peut pas faire commit dans un trigger.

**islamov2000** · 05/10/2011, 16h09

le commit dans le trigger est permis.
ton trigger me semble correcte.
teste..

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
CREATE OR REPLACE TRIGGER AR.RA_INTERFACE_AFTER_INSERT
BEFORE INSERT
    ON RA_INTERFACE_LINES_ALL REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
DECLARE   
 
    v_type varchar2(10);
    exception_1 exception;
 
BEGIN
 
SELECT DISTINCT ractta.type INTO v_type
  FROM ar.ra_batch_sources_all rbsa,ar.ra_cust_trx_types_all ractta
  WHERE rbsa.name = :NEW.batch_source_name
  AND ractta.cust_trx_type_id = :NEW.cust_trx_type_id
  AND rbsa.org_id = :NEW.org_id;
 
  IF ( :NEW.org_id=158 AND :NEW.INTERFACE_LINE_ATTRIBUTE4='0' AND :NEW.INTERFACE_LINE_CONTEXT='ORDER ENTRY' AND v_type='INV') then
 
   INSERT INTO RANDA.SMT_RA_INTERFACE_LINES_ALL 
  (LINE_ID, ... )
        VALUES
  (:NEW.LINE_ID, ... );
 
commit;--ajouté
   end IF;
 
    EXCEPTION
     WHEN others THEN RAISE;--modifié
END RA_INTERFACE_AFTER_INSERT;
/

**skuatamad** · 05/10/2011, 16h26

Envoyé par ORA-007

1) Creer une collection publique destinée à stocker les pk de la table RA_INTERFACE_LINES_ALL REFERENCING.

Attention à la collection :Coding Triggers

BEFORE Triggers Fired Multiple Times

If an UPDATE or DELETE statement detects a conflict with a concurrent UPDATE, then Oracle Database performs a transparent ROLLBACK to SAVEPOINT and restarts the update. This can occur many times before the statement completes successfully. Each time the statement is restarted, the BEFORE statement trigger is fired again. The rollback to savepoint does not undo changes to any package variables referenced in the trigger. Your package should include a counter variable to detect this situation.

En plus bonjour la concurrence d'accès avec une collection publique.

Peut être que passer par une VUE et un TRIGGER INSTEAD OF permettrait de résoudre le problème.

Sinon le plus simple est de faire une procédure stocké qui s'occupe de l'insert et de ne jamais faire d'insertion directement sur la table, mais toujours exécuter la procédure, comme ça pas besoin de trigger

Envoyé par boussafi

le commit dans le trigger est permis.

Ben non !
CREATE TRIGGER

Restrictions on Trigger Implementation The implementation of a trigger is subject to the following restrictions:

The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) if the block is executed within the same transaction.

**ORA-007** · 05/10/2011, 16h38

En plus bonjour la concurrence d'accès avec une collection publique.

solution déjà rencontrée chez un client en environnement multi user.

La collection était déclarée dans un PKS.

**Mohamed.Houri** · 05/10/2011, 16h46

Quelques soit la solution que l’on vous proposera ici, n’oubliez pas de la considérer d’un point de vue concurrentiel. Vous savez bien qu’en Oracle les selects (sans clause for update) ne bloquent pas les update/delete/insert et vice versa. Imaginez un instant que lors de l’exécution de votre trigger par un utilisateur user1, le select suivant:

Code :

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

1
2
3
4
5
6
7
8
 
SELECT DISTINCT ractta.type 
              INTO v_type
  FROM ar.ra_batch_sources_all rbsa
      ,ar.ra_cust_trx_types_all ractta
  WHERE rbsa.name               = :NEW.batch_source_name
  AND   ractta.cust_trx_type_id = :NEW.cust_trx_type_id
  AND   rbsa.org_id             = :NEW.org_id;

retourne bien un v_type = ‘INV’ ceci malgré le fait qu’un autre utilisateur, user2, l’ait déjà modifiée entre temps en lui attribuant une valeur ‘VNI’ mais sans atteindre la partie commit de son code. Le select exécuté par user1 va se rendre compte que la valeur a été modifiée mais pas encore ‘’commitée’’ et va donc utiliser les ‘’rollbacks segments ‘’ correspondants pour reconstruire l’image qu’avait le type au moment de l’exécution du select ci-dessous c'est-à-dire ‘INV’. Votre trigger va alors continuer et insérer dans la table

Code :

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

1
2
 
SMT_RA_INTERFACE_LINES_ALL

Mais manque de chance, le user2, juste après cet insert commit son travail et hop !!! vous avez quand même inséré une ligne dans la table SMT_RA_INTERFACE_LINES_ALL alors qu’il ne le fallait pas.
D’une manière générale deux conseils sur les triggers:

N’oubliez jamais de penser à l’aspect concurrentiel lors du développement d’un trigger
Lorsque vous vous trouvez devant une logique qui vous semble compliquée à implémenter, il y a des chances dans ce cas que le trigger ne représente pas la place adéquate pour cette logique