Utilisation des triggers

[Golgotha]

Bonjour,

Quelqu'un pourrait m'en dire plus sur les triggers...

J'ai voulu faire quelques chose mais apparemment ce n'est pas possible, je pense que c'est dû à une méconnaissance des principes sur les triggers.

j'expose mon cas :

En théorie, je voulais donc changer un champs sur le déclenchement de mon trigger avec ":new", sur le "befor update", jusque la pas de problème.

Ensuite, je voulais utiliser le "after update" sur le même trigger pour lire la table mais je ne peux pas : "table en mutation"...

D'ou ma question : pourquoi un AFTER update, si la table est toujours en modification, pourquoi a ce moment la le commit n'as pas été fait.

Question bonus : j'ai essayé de forcer le commit avec des transaction autonome dans le trigger, mais même avec ça, quand je lis la même table dans le trigger avec un autre transaction autonome, je ne voie la table qu'a sont stade n-1.

[mnitu]

Prenez le temps de lire "Triggers considered harmful, considered harmful" c'est assez bien expliqué.

[Soutou]

/*
Ensuite, je voulais utiliser le "after update" sur le même trigger pour lire la table mais je ne peux pas : "table en mutation"...
*/

Table en mutation c'est par en rapport avec AFTER UPDATE, ça provient du fait qu'Oracle interdise l'accès en lecture à la table dans le code du déclencheur.

/*
D'ou ma question : pourquoi un AFTER update, si la table est toujours en modification, pourquoi a ce moment la le commit n'as pas été fait.
*/

le déclencheur s'exécute après l'update, le commit n'a rien à voir.

D'ailleurs un déclencheur ne doit pas commiter une transaction car ça n'a pas
de sens. Tu veux commiter :

Code :

1
2
3
4
5
6
begin
 UPDATE ...;
 COMMIT;
exception 
 when ... then ...
end;

le déclencheur éventuel se charge d'autoriser ou pas l'update si oui, commit
passe, sinon tu arrives dans la zone d'exceptions.

[remi4444]

Bon, il faut être au clair sur ce que tu veux faire vraiment. Tout dépend si tu fait un des trigger "For Each Row" ou Pas.

Dans le premier cas, tu n'auras pas le droit de lire la table que tu es en train de modifier (car lorsque tu fait une modif globale, le système ne maitrisera pas quelle ligne est modifiée avant quelle autre, et le résultat produit serait totalement aléatoire) Tu as cependant accès à ta seule ligne en modification :new les valeurs d'après, :old les valeurs d'avant.

Dans le 2ième cas, tu auras le droit de lire ta table MAIS, tu n'auras pas accès au :new ou :old pour la simple raison que ces termes désignent des lignes, donc pour des modifs globales ça n'a plus de sens.

L'idéal serait qu'Oracle fournisse un tableau de ":new" ou de ":old" qui représenterait l'ensemble des lignes modifiées, à l'image de la pseudo table "inserted" de SQL-Server. Malheureusement (et pour des raisons que j'ignore) Oracle n'a jamais daigné fournir ça au développeurs. Il est cependant possible de la constituer soit même cette table en empilant les données par un trigger "for each row" soit dans une table temporaire, soit dans une variable tableau d'un package, puis disposer de cette table dans un 2ième trigger qui lui ne sera pas "for each row"

Tout est expliqué ici:
http://sgbd.developpez.com/oracle/ora-04091/

[SQLpro]

Mon cher Christian...

Envoyé par Soutou

Table en mutation c'est par en rapport avec AFTER UPDATE, ça provient du fait qu'Oracle interdise l'accès en lecture à la table dans le code du déclencheur.

Spécificté Oraclienne... C'est pas ce que font la plupart des SGBDR !
Les tables mutantes sont une invention d'Oracle.. Et pas la meilleure (incapacité de mettre à jour à la volée la table du trigger...)

Envoyé par Soutou

D'ailleurs un déclencheur ne doit pas commiter une transaction car ça n'a pas de sens.

Là encore, pas d'accord. Si l'on veut simuler une contrainte, lancer un ROLLBACK dans un trigger BEFORE ou AFTER à beaucoup de sens !!!
C'est ce que font SQL Server et ASE...

A +

[remi4444]

Envoyé par SQLpro

Spécificté Oraclienne... C'est pas ce que font la plupart des SGBDR !
Les tables mutantes sont une invention d'Oracle.. Et pas la meilleure (incapacité de mettre à jour à la volée la table du trigger...)

La on ne parle que des trigger "For each row" pas des trigger after-statement. Ces premiers ne sont même pas proposés par SQL-Server, donc il ne risque pas d'y avoir de soucis dessus, vu qu'ils n'existent pas! Pour les seconds, meme dans oracle, on a le droit d'accéder à la table. Le seul défaut d'oracle à mon avis est de ne pas fournir de tableau des lignes modifiées en natif dans les trigger after-statement, mais il y a toujours la possibilité de se les fabriquer à la mimine comme expliqué plus haut.

Envoyé par SQLpro

Là encore, pas d'accord. Si l'on veut simuler une contrainte, lancer un ROLLBACK dans un trigger BEFORE ou AFTER à beaucoup de sens !!!
C'est ce que font SQL Server et ASE...

Il y a toujours la possibilité de lancer une exception, ce qui va annuler le uniquement le statement, charge à la procédure appelante de faire un commit ou un rollback de la transaction dans son ensemble. Donc, c'est pareil, mais plus souple.

Attention, il ne faut pas perdre de vue la différence fondamentale qu'il y a entre un SGBD comme Oracle qui copie les données avant de les modifier et un autre comme SQL-Server ou Sybase qui lock les données en lecture pendant les modifications. Dans le premier cas, c'est plus compliqué et y'a quelques contraintes, mais ça a l’énorme avantage de n'être quasiment jamais bloqué en lecture.

[Pomalaix]

Envoyé par SQLpro

Là encore, pas d'accord. Si l'on veut simuler une contrainte, lancer un ROLLBACK dans un trigger BEFORE ou AFTER à beaucoup de sens !!!

(Sous Oracle du moins) Un déclencheur est un prolongement de l'instruction déclenchante, c'est à dire que (UPDATE + trigger) sont indécomposables et constituent une opération unitaire.
A ce titre, une opération de fin de transaction n'a rien à y faire.

De plus, dans un déclencheur FOR EACH ROW, j'ai du mal à imaginer à quoi servirait de mettre un ROLLBACK ou un COMMIT. Une opération unitaire touchant 10 lignes se traduirait par 10 transactions ?

Si on veut gérer une contrainte fonctionnelle par déclencheur, alors on utilisera, comme l'a expliqué Christian, le mécanisme des exceptions, qui permettra d'annuler l'instruction déclenchante et les effets du trigger, sans nécessairement annuler la transaction complète.

[Pomalaix]

Envoyé par Golgotha

Question bonus : j'ai essayé de forcer le commit avec des transaction autonome dans le trigger, mais même avec ça, quand je lis la même table dans le trigger avec un autre transaction autonome, je ne voie la table qu'a sont stade n-1.

C'est tout à fait normal !
Une transaction autonome, est, comme son nom l'indique, autonome. Comme si elle faisait partie d'une autre session.
De ce fait, elle est soumise aux mécanismes d'isolation, à savoir READ COMMITTED par défaut. Autrement dit, une transaction T2 ne peut pas voir les effets d'une autre transaction T1, tant que T1 n'a pas été validée. Or T1 a été mise en suspens pour que T2 s'exécute ; aucun COMMIT n'a eu lieu. T2 n'a donc aucune chance de voir les effets de T1.

Conclusion : les transactions autonomes ne sont quasiment jamais une solution valable pour contourner un problème de "table mutante".

[Mohamed.Houri]

Envoyé par remi4444

Attention, il ne faut pas perdre de vue la différence fondamentale qu'il y a entre un SGBD comme Oracle qui copie les données avant de les modifier et un autre comme SQL-Server ou Sybase qui lock les données en lecture pendant les modifications. Dans le premier cas, c'est plus compliqué et y'a quelques contraintes, mais ça a l’énorme avantage de n'être quasiment jamais bloqué en lecture.

Bien résumé. C'est justement la principale différence entre Oracle et les autres bases de données. "The read consistency" ou la possibilité de la coexistence simultanée de plusieurs valeurs différentes pour une information unique. Grâce à ce concept Oracle arrive toujours (et pas pratiquement car je ne considère pas un select for update comme un select simple) à garantir ceci

"Readers do not block writers and writers do not block readers"

Ce qui veut dire que l'on n'est jamais bloqué en cas de select et qu'en faisant des selects (pas for update) nous bloquons personne. Ce qui semble t-il n’est pas le cas dans tous les autres SGBD

[remi4444]

Envoyé par Mohamed.Houri

Ce qui veut dire que l'on n'est jamais bloqué en cas de select et qu'en faisant des selects (pas for update) nous bloquons personne. Ce qui semble t-il n’est pas le cas dans tous les autres SGBD

Absolument, c'est quelque chose auquel ne pensent pas naturellement les DBA ou les concepteurs ayant l'habitude de travailler avec Oracle. Pour être intervenu parfois sur un gros système appuyé sur Sybase, je tombais des nues à chaque fois que j'étais confronté au problème.

[mnitu]

Envoyé par remi4444

...L'idéal serait qu'Oracle fournisse un tableau de ":new" ou de ":old" qui représenterait l'ensemble des lignes modifiées, à l'image de la pseudo table "inserted" de SQL-Server. Malheureusement (et pour des raisons que j'ignore) Oracle n'a jamais daigné fournir ça au développeurs. Il est cependant possible de la constituer soit même cette table en empilant les données par un trigger "for each row" soit dans une table temporaire, soit dans une variable tableau d'un package, puis disposer de cette table dans un 2ième trigger qui lui ne sera pas "for each row"

...

A partir de la 11g, Using Compound DML Triggers to Avoid Mutating-Table Error