Discussion :

[StringBuilder]

Bonjour,

Après une discussion il y a quelques temps avec SQL Pro sur la section SQL Server, j'ai été convaincu qu'un trigger ne doit pas traiter les lignes modifiées "une à une", mais toutes ensembles en une seule fois.

Pour rappel, dans SQL Server "for each row" n'existe pas, et en lieu et place, il faut faire un curseur, ce qui est contre-performant.

Donc, pour un trigger sur la table "produit" sur la suppression, on va faire par exemple :

Code sql :

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

1
2
3
4
5
6
7
8
 
create trigger mon_trigger
on produit
before delete
as
begin
   delete prix where product_id in (select id from deleted);
end;

Donc pour 1000 produits supprimés par exemple, une seule instruction DELETE sur la table prix pour supprimer l'ensemble des lignes.

Comment faire avec Oracle ?

Car jusqu'à présent, je n'ai pas trouvé de doc qui explique comment faire.

A chaque fois (y compris sur le site de la doc officielle) soit je trouve des exemple "for each row", soit des exemples où on n'a pas accès aux tables virtuelles inserted et deleted.

C'est possible ou si je dois me résigner ?

Je pose cette question car j'ai un DEV à faire où je vais potentiellement avoir des dizaines de milliers de lignes à traiter dans un trigger, et l'opération étant très simple, j'aurais préféré pouvoir faire la modification en masse plutôt que ligne à ligne.

[StringBuilder]

Grmpf

Je croyais avoir trouvé la solution en utilisant l'assistant de SQL Developer, mais visiblement il me manque un truc...

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
 CREATE OR REPLACE TRIGGER GD_T_TAL_AFT_D 
AFTER DELETE ON TAL 
REFERENCING OLD AS deleted 
BEGIN
  update zod
  set valzod = ' '
  where zod.typzod = 'PRO'
  and zod.numzod = 997
  and (zod.codsoc, zod.clezod) in (select deleted.codsoc, deleted.codpro from deleted inner join tal on tal.codsoc = deleted.codsoc and tal.numelabo = deleted.numelabo and tal.achvte = deleted.achvte and tal.typenr = 'E' where deleted.typenr = 'L' and deleted.achvte = 'V' and tal.typela = 'PER');
END;

=> Il me dit que la table "deleted" n'existe pas, marlgré le "referencing"...

Aussi, je me rends compte que je vais me bouffer un souci de table mutating dans la tête non ? Vu que je tente de faire une jointure avec la table sur laquelle porte le trigger...

[skuatamad]

Il faut bien comprendre que la gestion de la concurrence d'accès d'oracle (MVCC) et celle de sqlserver en mode classique (non snapshot même si très différent de l'implémentation d'oracle) sont totalement différentes.

Chaque implémentation a ses avantages et ses inconvénients.
L'architecture MVCC (en général et en particulier celle d'oracle) évite les problèmes d'escalade de verrous, en contre partie par exemple l'écriture des triggers est plus complexe, souvent moins performantes car il n'existe pas de table inserted et autre. Donc on ne peut généralement pas travailler en mode ensembliste dans un trigger oracle.

D'ailleurs certains experts recommandent d'éviter les triggers à cause de la difficulté pour gérer la concurrence d'accès correctement, notamment quand le développeur ne maitrise pas bien le concept de lecture consistante.

Votre premier trigger s'écrirait de façon classique :

Code :

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

1
2
3
4
5
6
CREATE TRIGGER mon_trigger 
before DELETE ON produit 
for each row
begin
   DELETE prix WHERE product_id = :old.id;
end;

Cependant à partir de la 11GR2 vous pouvez développer des COMPOUND TRIGGERS qui permettent de lever certaines restrictions et d'améliorer la performance en travaillant de façon plus ensembliste.

Je vous laisse regarder les exemples, revenez avec un test case compilable en cas de soucis dans l'implémentation du dit trigger.

[skuatamad]

Je pose cette question car j'ai un DEV à faire où je vais potentiellement avoir des dizaines de milliers de lignes à traiter dans un trigger, et l'opération étant très simple, j'aurais préféré pouvoir faire la modification en masse plutôt que ligne à ligne.

Utiliser un trigger pour faire des milliers de modifications est effectivement une approche douteuse sur oracle.
Pourquoi ne pas faire ces modifications dans la transaction elle même ? (à moins que pas la main sur le code de l'appli)

[Pomalaix]

...REFERENCING OLD AS deleted...

La clause REFERENCING n'a de sens que dans le contexte d'un déclencheur de niveau ligne, le fameux FOR EACH ROW. De plus, elle ne sert qu'à donner un nom personnalisé aux pseudo variables :NEW et :OLD.
NEW et OLD, ou tout autre nom que vous leur donnerez à travers la clause REFERENCING, sont des variables monolignes (équivalent d'un %ROWTYPE) qui contiennent les valeurs de la ligne en cours de traitement respectivement après et avant la modification.
NEW et OLD ne sont en aucun cas des variables multilignes.

D'ailleurs, la clause REFERENCING, en toute généralité, n'a aucun intérêt pratique.

[pachot]

Bonjour,
Quel est le besoin initial ? Ce n'est probablement pas un trigger qui est la bonne solution sous Oracle.
Si c'est pour faire un delete cascade, c'est possible via contrainte d'intégrité.
Cordialement,
Franck.