Comment configurer des déclencheurs postgresql

«Le déclencheur est utilisé dans n'importe quelle base de données pour effectuer une tâche automatisée lorsqu'un événement de base de données particulier apparaît. Il est appelé la fonction de rappel de la base de données qui est appelée automatiquement en fonction de l'événement. Il y a de nombreux avantages à utiliser des déclencheurs dans la base de données PostgreSQL. Trois types de déclencheurs peuvent être créés, qui sont décrits dans ce tutoriel en utilisant plusieurs exemples."

Certains avantages de l'utilisation de déclencheurs

• La validation des données peut être effectuée facilement.
• La valeur unique d'un nouvel enregistrement peut être générée.
• Le développement des applications devient plus rapide.
• Les règles commerciales de l'application peuvent être appliquées.
• La maintenance de l'application devient plus facile.

Syntaxe

La syntaxe de la création d'un déclencheur est donnée ci-dessous.

Créer [ou remplacer] [CONSTRAINT] Nom du déclenchement avant | Après | Au lieu de événement [ou…]
Sur table_name
[À partir de référencé_table_name]
[Non déférable | [Déférable] [Initialement immédiat | Initialement différé]]
[Référence old | Nouveau table [as] transition_relation_name […]]
[Pour [chacun] Row | DÉCLARATION ]
[Quand (condition)]
Exécuter fonction | Procédure function_name (arguments)

• Créer un mot clé est utilisé pour créer un nouveau déclencheur et remplacer le mot clé est utilisé pour modifier un déclencheur existant.
• L'option de contrainte est utilisée pour créer un déclencheur de contrainte qui est licencié en fonction de la valeur de contrainte.
• Un nom de déclenchement unique est utilisé pour créer le déclencheur.
• Quatre types d'événements peuvent être utilisés pour créer un déclencheur. Ce sont des insert, mettre à jour, supprimer et tronquer.
• Trois types de déclencheurs qui peuvent être créés sont AVANT, APRÈS, et AU LIEU DE. Les déclencheurs peuvent être créés pour la table, la table étrangère et la vue. AVANT et APRÈS déclenche des opérations d'insertion, de mise à jour et de suppression de la table et de la table étrangère au niveau des lignes, et la vue également au niveau de la déclaration. AU LIEU DE Le déclencheur prend en charge les opérations d'insertion, de mise à jour et de suppression de la vue uniquement au niveau des lignes. L'événement tronqué peut être utilisé pour le tableau uniquement au niveau de la déclaration.
• Après avoir défini l'événement, le nom de la table ou de la table ou de la vue étrangère est utilisée pour laquelle le déclencheur sera créé.
• Le référencé_table_name est utilisé pour les contraintes de touche étrangère et le déclencheur de contrainte uniquement.
• Le timing par défaut du déclencheur est défini par Pas reproduit ou Reportable ou Initialement immédiat ou Initialement différé.
• Référence Le mot-clé est utilisé pour déclarer un ou deux noms de relation.
• Vieille table ou Nouveau tabLa clause E définit la relation de transition avant-l'image ou la relation de transition après-image.
• transition_relation_name est utilisé dans le déclencheur de la relation de transition.
• Pour chaque ligne ou Pour chaque déclaration Définit si la fonction de déclenchement doit être licenciée une fois pour chaque ligne affectée par l'événement de déclenchement ou une seule fois par instruction SQL. Pour chaque déclaration est la valeur par défaut.
• condition détermine si la fonction de déclenchement sera exécutée ou non.
• function_name est une fonction définie par l'utilisateur qui ne prend aucun argument, et le type de retour est le déclencheur qui est exécuté lorsque le déclencheur se déclenche.
• La liste d'arguments séparés par des virgules est transmis à la fonction lorsque le déclencheur est exécuté.

Conditions préalables

UN. Installez le postgresql

Vous devez installer la dernière version des packages PostgreSQL sur le système d'exploitation Linux avant d'exécuter les instructions SQL affichées dans ce tutoriel. Exécutez les commandes suivantes pour installer et démarrer le postgresql:

$ sudo apt-get -y installer postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

B. Exécutez la commande suivante pour vous connecter à PostgreSQL avec Root Privilege.

$ sudo -u postgres psql

C. Vous pouvez créer une base de données nommée «testdb " En exécutant l'instruction SQL suivante.

# Créer une base de données testdb;

Si vous souhaitez créer une table dans le testdb base de données, alors vous devez taper «\ C» Pour modifier la base de données. Mais dans ce tutoriel, je n'ai pas modifié la base de données, et le tableau et toutes les fonctions ont été créés dans la base de données par défaut nommée postgres.

Créer une table nommée enseignants et cours avec certains enregistrements pour tester l'utilisation des déclencheurs qui seront créés plus tard dans ce tutoriel.

Créer des professeurs de table (
Clé primaire en série ID,
Nom Varchar (30),
Adresse Varchar (100),
contact_no varchar (20),
Disponible Bool par défaut True);
Créer des cours de table (
Course_id Serial Primary Key,
Nom Varchar (30),
Credit_hour Float,
professeur_id int de défaut 0);
Insérer dans les enseignants (nom, adresse, contact_no, disponible)
valeurs
('Nasir Uddin', 'Dhanmondi 15, Dhaka', '+8801947854523', false),
('Farzana Mahmud', '39 / 1, Jigatola, Dhaka ',' +88018553426754 ', false);
Insérer dans les cours (Nom, Credit_Hour, Teacher_id)
valeurs
(«Physique», 2.0, 1),
(«Chimie», 2.0, 2),
(«Mathématiques», 3.0, 1);

Exemples de déclenchement

Chaque déclencheur est associé à une fonction postgresql. Vous devez donc créer la fonction avant de créer le déclencheur. La façon de créer différents types de déclencheurs a été montrée dans cette partie du tutoriel.

Exemple-1: Créer avant d'insérer le déclencheur

L'insertion avant déclenche des incendies avant d'effectuer une opération d'insertion. Exécutez l'instruction SQL suivante pour créer une fonction nommée avant_insert () qui sera utilisé dans un Avant d'insérer déclencher. L'un des objectifs principaux de l'utilisation d'un déclencheur est la validation des données. La fonction suivante vérifiera si les champs particuliers sont vides ou non. Si l'une des valeurs de champ reste vide, alors un message d'erreur particulier sera augmenté. Une autre validation sera vérifiée pour le nom champ, et un message d'erreur sera augmenté si la longueur de ce champ est inférieure à 5. L'instruction conditionnelle suivante est utilisée pour concaténer la valeur de la chaîne «+88» avec le contact_no Si la longueur du contact_no le champ est 11.

Créer ou remplacer la fonction avant_insert ()
Renvoie le déclencheur comme
$$
COMMENCER
Si nouveau.name = "ou nouveau.adresse = "ou nouveau.contact_no = "alors
le nom ou l'adresse ou l'adresse ou le champ Contact_No est vide.';;
FIN SI;
Si la longueur (Nouveau.nom) < 5 THEN
Le champ de nom de soulèvement doit contenir plus de 5 caractères.';;
FIN SI;
Si la longueur (Nouveau.contact_no) = 11 alors
NOUVEAU.contact_no = '+88' || NOUVEAU.contact_no;
FIN SI;
Retourner nouveau;
FIN;
$$
Langue «plpgsql»;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez l'instruction SQL suivante pour créer un déclencheur nommé check_before_insert qui exécutera le avant_insert () fonction avant d'insérer le nouvel enregistrement au enseignants tableau.

Créer un déclencheur Check_before_insert
Avant d'insérer
Sur les enseignants
Pour chaque ligne
Exécuter la fonction avant_insert ();

La sortie suivante apparaîtra si le déclencheur est créé avec succès.

Exécutez l'instruction d'insertion suivante qui contient toutes les données valides.

Insérer dans les enseignants (nom, adresse, contact_no)
Valeurs («zafar iqbal», «mirpur, dhaka», «01738975634»);

La sortie suivante apparaîtra si le nouvel enregistrement est inséré avec succès.

Exécutez l'instruction d'insertion suivante qui contient les données non valides pour le champ Nom.

Insérer dans les enseignants (nom, adresse, contact_no)
Valeurs («Lina», «Malibag, Dhaka», «01790975630»);

Le message d'erreur suivant apparaîtra pour la valeur de nom non valide.

Exécutez l'instruction d'insertion suivante qui contient la valeur vide pour le adresse et contact_no champs, et la valeur vide de ces champs n'est pas valide.

Insérer dans les enseignants (nom, adresse, contact_no)
Valeurs ('Nilufar Rahman', ",");

Le message d'erreur suivant apparaîtra pour les valeurs vides.

Exécutez l'instruction SELECT suivante pour vérifier quels enregistrements sont insérés après avoir utilisé le déclencheur avant l'insertion.

Sélectionner * parmi les enseignants;

La sortie suivante montre qu'un seul enregistrement est inséré après avoir utilisé le déclencheur.

Exemple-2: Créer après la suppression de suppression

Le déclencheur après la suppression des incendies après avoir supprimé tout enregistrement du tableau particulier. Exécutez l'instruction SQL suivante pour créer une fonction nommée After_delete () qui sera utilisé dans un Après la suppression déclencher pour supprimer les lignes connexes du cours table lorsqu'un enregistrement est supprimé du enseignants' tableau. Ici, un vieux.identifiant a été utilisé pour tracer le supprimé identifiant du enseignants' tableau.

Créer une fonction après_delete ()
Renvoie le déclencheur comme
$$
COMMENCER
Supprimer des cours où enseignant_id = vieux.identifiant;
Retourne vieux;
FIN;
$$
Langue PLPGSQL;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez l'instruction SQL suivante pour créer un déclencheur nommé Delete_teacher qui exécutera le After_delete () fonction après avoir supprimé un enregistrement du enseignants' table qui supprimera les enregistrements du cours table où le professeur_id la valeur correspond à la suppression identifiant valeur du enseignants' tableau.

Créer un déclencheur de suppression_teacher
Après la suppression
Sur les enseignants
Pour chaque ligne
Exécuter la fonction after_delete ();

La sortie suivante apparaîtra si le déclencheur est créé avec succès.

Exécutez la déclaration de suppression suivante qui supprimera le dossier du tableau des enseignants où le identifiant la valeur est 1. Le Delete_teacher Le déclencheur sera tiré si un enregistrement est supprimé du enseignants' tableau.

Supprimer des enseignants où id = 1;

La sortie suivante montre qu'un enregistrement a été supprimé du tableau des enseignants. Alors le Delete_teacher Le déclencheur sera tiré et supprimera tous les enregistrements du tableau des cours qui contient 1 dans le professeur_id champ.

Maintenant, exécutez les commandes de sélection suivantes pour vérifier quels enregistrements sont supprimés du enseignants et cours les tables.

Sélectionner * parmi les enseignants;
Sélectionner * à partir des cours;

La sortie montre qu'un enregistrement a été supprimé du tableau des enseignants et que deux enregistrements ont été supprimés du tableau des cours en utilisant le déclencheur.

Exemple-3: Créer au lieu du déclencheur de mise à jour

Le déclencheur au lieu de mise à jour peut être appliqué sur les vues de la table uniquement. Vous devez donc créer une vue de la table et une fonction pour tester l'utilisation de ce type de déclencheur. Exécutez l'instruction SQL suivante pour créer une fonction nommée insert_course () qui mettra à jour les valeurs du enseignants et le cours table après avoir inséré un nouveau record au cours tableau. Le but de cette fonction est d'attribuer un enseignant au nouveau cours si l'enseignant est disponible.

Créer ou remplacer la fonction insert_course ()
Renvoie le déclencheur comme
$$
DÉCLARER
TEC_ID INT;
COMMENCER
Insérer dans les cours (nom, Credit_Hour)
Valeurs (Nouveau.nom, nouveau.Credit_Hour);
Sélectionnez ID dans TEC_ID dans les enseignants où disponible = True Limit 1;
Mettre à jour les enseignants set disponibles = false où id = tec_id;
Mettre à jour les cours SET Teacher_id = Tec_id où enseign_id = 0;
Retourner nouveau;
FIN
$$
Langue PLPGSQL;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez la commande SQL suivante pour créer une vue du tableau des cours.

Créer des cours de vue_view comme
Sélectionner * à partir des cours;

La sortie suivante apparaîtra si la vue est créée avec succès.

Maintenant, exécutez l'instruction SQL suivante pour créer le déclencheur d'insertion de l'insertion nommé update_data qui sera licencié lorsqu'un nouvel enregistrement sera inséré en utilisant le create_view voir.

Créer un déclencheur Update_data
Au lieu d'insérer sur Courses_View
Pour chaque ligne
Exécuter la fonction insert_course ();

La sortie suivante apparaîtra si le déclencheur est créé avec succès.

Exécutez la déclaration d'insertion suivante pour insérer un nouvel enregistrement dans le tableau des cours en utilisant cours_view voir.

Insérer dans Courses_view (nom, Credit_Hour)
valeurs («mathématiques supérieures», 3.0);

La sortie suivante apparaîtra si les données sont insérées avec succès à l'aide de la vue.

Maintenant, exécutez les relevés de sélection suivants pour vérifier comment les cours et les tables des enseignants ont été modifiés après avoir inséré un nouvel enregistrement dans le tableau des cours en utilisant la vue.

Sélectionner * parmi les enseignants;
SELECT * FROM Courses_View;

La sortie suivante montre qu'un nouvel enregistrement a été inséré dans le tableau des cours et le champ disponible du enseignants' Le tableau a été mis à jour de «T» à «F», où la valeur d'ID est de 3, et cette valeur d'identification a été mise à jour dans le cours table pour attribuer ce professeur au cours nouvellement inséré.

Conclusion

De nombreuses tâches de base de données peuvent être effectuées automatiquement en utilisant un déclencheur dans la base de données PostgreSQL. Chaque déclencheur est exécuté en utilisant une fonction particulière. De nombreuses objectifs d'utilisation des déclencheurs ont été expliqués dans ce tutoriel en créant plusieurs déclencheurs avec la fonction. Trois différents types de déclencheurs ont été créés ici qui aideront les nouveaux utilisateurs de PostgreSQL à connaître la façon de créer et d'utiliser des déclencheurs dans la base de données PostgreSQL.