Lamiae
La programmation SQL les transactions et les déclencheurs, tutoriel bases de données document PDF.
A.4.4 Particularités d’Oracle
1. Un déclencheur ligne ne peut pas accéder à une table mutante. Dans [4], on peut lire : « A mutating table is a table that is being modified by an UPDATE, DELETE or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint. (…) The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from being seeing inconsistent set of data. (…) »
Il faut donc comprendre qu’il n’est pas permis de consulter ou modifier une table mutante. Cette restriction préservera le déclencheur ligne de lire des données inconsistantes. Donc votre trigger ligne ne devrait jamais accéder à la table sur laquelle il porte autrement que par OLD et NEW.
2. Dans un déclencheur ligne, il est possible de modifier les éléments de NEW. Ainsi, on peut lire dans [4] que : « Old and new values are available in both BEFORE and AFTER row triggers. A new column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired). If a BEFORE row trigger changes the value of new.column, then an AFTER row trigger fired by the same statement sees the change assigned by the BEFORE row trigger. »
Il est par conséquent autorisé de modifier les valeurs des colonnes au travers de NEW (et donc écrire dans le code PL/SQL :new.val :=2 par exemple), uniquement dans des déclencheurs lignes BEFORE.
3. Si vous spécifiez dans un déclencheur ligne que son déclenchement dépend d’une colonne précise dans une table (via la clause WHEN), il serait logique que l’élément OLD et NEW ne porte que sur cette colonne. Puisque c’est cet élément précis qui intervient dans votre déclencheur, il serait très étrange d’utiliser OLD ou NEW pour atteindre une autre colonne de la table même si le SGBD autorise cela.
A.4.5 Exemple
CREATE OR REPLACE TRIGGER upd_qt_produit
AFTER UPDATE OF QSTOCK ON produit
FOR EACH ROW
DECLARE
quantite_insuffisante exception;
quantite_com number;
quantite_stock number;
BEGIN
SELECT MAX(qcom) INTO quantite_com
FROM lignecom
WHERE npro = :new.npro;
quantite_stock := :new.qstock;
IF quantite_stock < quantite_com THEN
RAISE quantite_insuffisante;
END IF;
EXCEPTION
WHEN quantite_insuffisante THEN
raise_application_error (-20001, ‘Quantite de stock
insuffisante’);
END;
A.5 Transactions en PL/SQL (Oracle)
Oracle gère les transactions de manière implicite. Ainsi, toute instruction de manipulation des données (comme SELECT, UPDATE ou DELETE) démarre une transaction. La transaction se termine automatiquement lorsque :
• COMMIT ou ROLLBACK est rencontré
• Lorsqu’une instruction appartenant au LDD (langage de définition de données comme CREATE, DROP, ALTER) ou au LCD (langage de contrôle de données gérant l’accès comme GRANT, REVOKE, …) est exécutée ( COMMIT)
• Le système tombe en panne ( ROLLBACK)
Dans l’interface web, les commandes SQL s’exécutent en mode autocommit. Cela signifie qu’une instruction COMMIT implicite est automatiquement effectuée après chaque instruction SQL de modification des données.
En fait, l’option Validation automatique assure ce comportement par défaut. C’est pourquoi il n’a jamais été nécessaire d’exécuter un COMMIT.
A.6 Exercice en PL/SQL (Oracle)
1. (Sous Oracle uniquement) La suppression d’une catégorie principale (i. e. ayant des sous-catégories) est interdite. La suppression d’une catégorie enfant entraîne le changement de catégorie pour tous les livres concernés vers la catégorie parent.
Reprendre les exercices précédents (§ 8) et les réaliser sous Oracle.
…….
Si le lien ne fonctionne pas correctement, veuillez nous contacter (mentionner le lien dans votre message)La programmation SQL les transactions et les déclencheurs (280 KO) (Cours PDF)
