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.
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.
Vous trouverez les deux fichiers la base de données du dossier appelé 'Schema.txt de bus »et« data load.txt'. Le premier fichier contient des instructions Create pour créer les tables de la base de dépôts de bus et de définir des contraintes clés primaires et étrangères. Vous devez copier et coller le contenu de ce fichier dans iSQL*Plus, puis d'exécuter l'instruction CREATE - que ce besoin se fait une fois. Le deuxième fichier contient la déclaration insert pour ajouter des lignes de données dans les tableaux créés précédemment.
Encore une fois de copier et coller et exécuter une fois.Écrire les requêtes suivantes en utilisant SQL.
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.
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.
3. Création des déclencheurs pour maintenir la validité des données
Dans votre module de base de données précédente, vous avez peut-être rencontré des contraintes CHECK. Ceci est similaire à 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.
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.
Vous trouverez les deux fichiers la base de données du dossier appelé 'Schema.txt de bus »et« data load.txt'. Le premier fichier contient des instructions Create pour créer les tables de la base de dépôts de bus et de définir des contraintes clés primaires et étrangères. Vous devez copier et coller le contenu de ce fichier dans iSQL*Plus, puis d'exécuter l'instruction CREATE - que ce besoin se fait une fois. Le deuxième fichier contient la déclaration insert pour ajouter des lignes de données dans les tableaux créés précédemment.
Encore une fois de copier et coller et exécuter une fois.Écrire les requêtes suivantes en utilisant SQL.