La solution d'Exercice SQL Corrigé : PL / SQL déclencheurs

1. l'utilisation de déclencheurs pour maintenir les règles d'affaires
Supposons que le transport Middlesex a une règle stipulant que le salaire d'un chauffeur de bus ne peut pas être changé par plus de 20% du salaire d'origine. Créer un déclencheur 'salaryChangeMonitoring' pour appliquer cette contrainte. Les feux de déclenchement chaque fois qu'il ya une mise à jour à la table Busdriver et délivre un message d'erreur approprié lorsque la règle est violée.

Dans ce cas, nous pouvons définir un déclencheur sur la table Busdriver de la façon suivante:

create or replace trigger salaryChangeMonitoring

before update on BusDriver

for each row

begin

if ((:new.bdSalary/:old.bdSalary) >= 1.2) or

((:old.bdSalary/:new.bdSalary) >= 1.2)

then

RAISE_APPLICATION_ERROR(-20002, 'Warning: Large percentage change in salary prohibited.');

end if;

end;

/

 
Exemple de mise à jour sur Busdriver donnant Jane Brown une augmentation> 20%:

Update BusDriver

set bdSalary = 3600

where bdName = 'Jane Brown';

Update BusDriver

       *

ERROR at line 1:
ORA-20002: Warning: Large percentage change in salary prohibited.
ORA-06512: at "PATRICIA.SALARYCHANGEMONITORING", line 5
ORA-04088: error during execution of trigger 'PATRICIA.SALARYCHANGEMONITORING'

 
2. créer des déclencheurs pour éviter les mises à jour et les suppressions 

Dans la base de données Busdriver, nous pouvons voir que les lignes de la table Depot sont souvent référencés par de nombreuses lignes enfant dans un certain nombre d'autres tables (par exemple, Bus, Cleaner and Busdriver). . Bien qu'il existe des contraintes FOREIGN KEY déclarés sur les tables enfant pour maintenir l'intégrité référentielle, nous pouvons encore définir un déclencheur dans la table parent (i.e., Depot) pour arrêter toute tentative de changer le nom du dépôt et / ou de retirer tout de les rangées de dépôt. Ceci est correspondant à la règle de l'entreprise indiquant que, une fois un dépôt est établi, il sera là «pour toujours» et ne sera pas autorisé à changer de nom (bien que réaliste, nous supposons qu'une telle règle est nécessaire).
Écrire des déclarations PL / SQL appropriées pour créer le déclencheur. Notez que le déclencheur que vous créez est un statement level trigger  donc 'pour chaque ligne' ne doit pas être utilisé. Après le déclencheur est créé, essayez de changer le nom de certains dépôts et de supprimer une ligne de dépôt, et de voir ce qui va arriver. 

Le déclencheur dont nous avons besoin est un déclencheur de état-niveau. Les événements déclencheurs sont UPDATE de DNAME et DELETE. Les états / SQL PL appropriées sont les suivantes:

create or replace trigger DepotTrigger

before update of dName or delete on Depot

begin

RAISE_APPLICATION_ERROR (-20501, 'You are not allowed to change the value of depot name or delete a depot row');

end;

Trigger created.

 
Après avoir créé le déclencheur, si vous essayez d'effectuer une opération de de mise à jour sur gname tels que:

update Depot

set dName = 'Crouch End'

where dName = 'Hornsey';

 
L'erreur suivante vous avertit que l'opération est interdite:

Update Depot

       *

ERROR at line 1:
ORA-20501: You are not allowed to change the value of depot name or delete a depot row
ORA-06512: at "PATRICIA.DEPOTTRIGGER", line 2
ORA-04088: error during execution of trigger 'PATRICIA.DEPOTTRIGGER'

 

3. Création des déclencheurs pour maintenir la validité des données

Une contrainte CHECK est semblable à une règle de validation et est une option dans la commande CREATE TABLE lequel vous pouvez spécifier quelles données peuvent être saisies dans une colonne particulière. Donc, si nous voulions ajouter une contrainte dans une table Cleaner que le salaire doit être, dans certaines limites, nous pourrions créer la table ainsi:

 

 

Create table Cleaner

                (cNo                       varchar2(5),

                cName                  varchar2(20),                     

                cSalary                  number(6,2),

                dNo                        varchar2(5),

 

             constraint pk_clno primary key(cNo),

                  constraint fk_deNo1 foreign key(dNo) references depot(dNo),

             check ( cSalary >0  and cSalary < 5000 ) ); 

 Voici un salaire qui est inférieur à zéro et plus de 5000 va entraîner une violation de la contrainte.

en appliquant la contrainte CHECK, cependant, nous ne saurions pas si le salaire est supérieur à 5000 ou inférieure à 0 (ie, un nombre négatif).

Nous pouvons créer un déclencheur au lieu d'une contrainte CHECK, qui peut nous dire comment la restriction sur "cSalary 'est violée. Chaque fois que la valeur de 'cSalary' est au-delà de la plage valide (0-5000), Le déclencheur va générer un message d'erreur informant les utilisateurs si elle est supérieure à 5000 ou un nombre négatif. (Si une contrainte de vérification existe déjà, il doit être abandonné en premier.)

Donnez votre PL / SQL pour créer le déclencheur, et d'utiliser certaines mise à jour de SQL et insérer des instructions pour le tester.

 

Le déclencheur dont nous avons besoin est un déclencheur de niveau ligne. Les événements déclencheurs sont UPDATE du salaire et INSERT. Les états / SQL PL appropriées sont les suivantes:

 

create or replace trigger cleanerSalaryTrigger

before update of cSalary or insert on Cleaner

for each row

when((new.cSalary < 0) or (new.cSalary > 5000))

begin

if :new.cSalary < 0 then

RAISE_APPLICATION_ERROR(-20511, 'The salary cannot be negative.');

end if;

if :new.cSalary > 5000 then

RAISE_APPLICATION_ERROR(-20512, 'The salary cannot exceed 5000. ');

end if;

end;

Trigger created.

 Après avoir créé le déclencheur, si vous essayez d'effectuer une opération de mise à jour ou insérer une ligne propre avec un salaire pas entre 0 et 5000, une erreur se produit. Par exemple, lors de l'exécution

 

update Cleaner

set cSalary = 6000

where cNo = '114';

where cNo = '114'

  *

ERROR at line 3:
ORA-20512: The salary cannot exceed 5000.
ORA-06512: at "PATRICIA.CLEANERSALARYTRIGGER", line 6
ORA-04088: error during execution of trigger 'PATRICIA.CLEANERSALARYTRIGGER'

 

 

 

►Retour à l'exercice