Procédures stockées et déclencheurs dans MySQL

Certains outils fournis par le moteur de base de données MySQL sont les procédures stockées, fonctions et déclencheur, qui sont utilisés pour effectuer des transactions ou des opérations telles que l'insertion ou la modification d'enregistrements.

Les procédures stockées sont de petits programmes développés en code SQL. Une procédure stockée est un ensemble de commandes SQL stockées avec la base de données.

L'avantage d'une procédure stockée est qu'on peut la créer dans n'importe quel éditeur de texte et même sur le serveur, elle est exécutée par le moteur de la base de données et elle n'est pas accessible aux utilisateurs mais uniquement à l'administrateur.

Une procédure stockée envoie ses résultats à une application pour qu'elle les affiche à l'écran en évitant de surcharger le serveur, dans le tutoriel :

• Procédures stockées MYSQL - Création, requêtes et insertions de données

J'avais expliqué comment les créer, ici nous allons ajouter des fonctions et des déclencheurs. Nous verrons un exemple sur une base de données d'un bien immobilier que nous appellerons lieu de location puis nous créerons les tableaux.

 - Structure de la table pour la table `real estate` CREATE TABLE IF NOT EXISTS` real estate` (`id` int (11) NOT NULL,` userid` int (11) DEFAULT NULL, `idtype property` int (6) DEFAULT '0 ', `prix` décimal (10,2) DEFAULT' 0.00 ',` commission` décimal (10,0) NON NULL, `description` texte,` highdate` date DEFAULT' 0000-00-00 ', `province id` int (10) DEFAULT NULL, `idlocalidad` int (10) DEFAULT NULL,` address` varchar (150) DEFAULT NULL, `floor and apartment` varchar (100) DEFAULT NULL,` between_streets` text, `idoperation` int (100) ) DEFAULT NULL , `featured` char (3) DEFAULT 'no',` image1` varchar (255) DEFAULT NULL, `image2` varchar (255) DEFAULT NULL,` image3` varchar (255) DEFAULT NULL, `image4` varchar (255) DEFAULT NULL, `old` varchar (100) DEFAULT NULL,` mt2covered` int (11) DEFAULT NULL, `lot_surface` int (11) DEFAULT NULL,` enabled` enum ('yes',' no ') NOT NULL DEFAULT' si ') ENGINE = MyISAM AUTO_INCREMENT = 196 DEFAULT CHARSET = latin1; - Indices de la table `immobilier` ALTER TABLE` immobilier` ADD PRIMARY KEY (`id`);

Nous allons maintenant développer une procédure stockée pour chaque transaction pour interroger, insérer, modifier et supprimer un enregistrement.

On peut utiliser Phpmyadmin ou un gestionnaire comme Heidisql qui est gratuit et fonctionne sous Windows ou Linux avec Wine.

Nous créons une procédure stockée pour interroger la table immobilière :

 DELIMITER // CREATE PROCEDURE pa_listainmuebles () BEGIN SELECT * FROM properties; FIN // DELIMITER;

MYSQL comprend qu'une instruction se termine par un point-virgule. La Instruction DELIMITER remplacez le caractère de fin par tout autre caractère, par convention // est utilisé pour indiquer la fin de la procédure stockée afin que MySQL ne termine pas la procédure stockée lorsqu'il rencontre le premier point-virgule.

Nous pouvons aller au Onglet Routines pour voir chaque transaction que nous avons créée et à partir de là, nous pouvons modifier, exécuter, exporter ou supprimer le code.

Pour exécuter une procédure en stockant, nous utilisons le Commande APPEL à partir de Onglet SQL ou encore d'un langage de programmation comme .NET ou Java. Ensuite, nous invoquons la procédure stockée créée avec la commande.

 APPEL pa_listainmuebles ();

Ensuite, nous allons créer une procédure stockée pour insérer une propriété, pour cela nous aurons besoin de paramètres de type IN, c'est-à-dire que nous affecterons des données et des variables d'entrée à la procédure stockée pour effectuer une transaction, dans ce cas, enregistrez-les dans la base de données.

 DELIMITER // CREATE PROCEDURE pa_nuevoinmueble (IN id INT, IN userid INT, IN price DECIMAL, IN commission DECIMAL) BEGIN INSERT INTO property` (`id`,` userid`, `price`,` commission`) VALEURS (id, userid ) , prix, commission) END // DELIMITER;

AGRANDIR

Ensuite, nous pouvons exécuter la procédure stockée en appelant et en attribuant les paramètres.

 APPEL `pa_newinmueble` ('12 ',' 15 ',' 10.00 ',' 0.05 ')

Nous pouvons également saisir des données en exécutant la routine depuis Phpmyadmin.

AGRANDIR

Ensuite, nous allons créer la procédure stockée pour éditer une propriété depuis l'éditeur Phpmyadmin, dans ce cas nous ne modifierons que le prix.

Nous pouvons créer des rôles à partir du champ Definer où nous pouvons affecter un utilisateur défini dans le serveur Mysql, dans ce cas l'utilisateur root de l'hôte localhost, afin qu'il puisse accéder à la procédure stockée.
Si nous voulons le faire à partir de code SQL, nous devons exécuter les commandes suivantes :

 CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_editarinmueble` (IN` new property` DECIMAL (10,2), IN `property id` INT (11)) BEGIN UPDATE property SET price = new property WHERE id = property id; FINIR

Vous l'exécutez et vous avez terminé.

Utiliser Trigger ou Triggers dans Mysql
Un déclencheur ou un déclencheur dans MySQL est un ensemble d'instructions SQL qui dépendent d'une procédure stockée et sont utilisées pour s'exécuter automatiquement lorsqu'un certain événement se produit dans notre base de données. Ces événements sont déclenchés par des transactions ou des instructions telles que INSERT, UPDATE et DELETE.

Par exemple, lorsqu'une modification est enregistrée dans un registre, nous effectuons automatiquement une sauvegarde ou enregistrons un fichier d'audit pour savoir quelles données ont été modifiées, quand et qui les a modifiées. Ils peuvent être utilisés pour toute manipulation affectant les données, pour soutenir ou générer de nouvelles informations.

Nous allons créer le tableau d'audit immobilier ci-dessous :

 CREATE TABLE `audit` (` user` VARCHAR (200) NULL DEFAULT NULL, `description` TEXT NULL,` date` DATETIME NULL DEFAULT NULL) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDB

Nous allons créer un déclencheur qui enregistre un message en audit si quelqu'un modifie le prix d'une propriété.

 CREATE DEFINER = `root` @` localhost` TRIGGER `real estate_after_update` APRÈS MISE À JOUR SUR` immobilier` POUR CHAQUE LIGNE INSÉRER DANS audit (utilisateur, description, date) VALEURS (utilisateur (), CONCAT ('Prix immobilier modifié', NEW.id, '(', OLD.price, ') par (', NEW.price, ')'), NOW ())

Ce déclencheur est exécuté automatiquement après une mise à jour du prix, nous pouvons inclure plus de champs si nous le souhaitons, avec OLD nous spécifions le champ avec la valeur avant la modification et avec NEW nous spécifions la nouvelle valeur entrée, avec NOW () nous spécifions la date et l'heure actuelle.

Nous créons un déclencheur qui aura After Update on properties en tant qu'événement, c'est-à-dire qu'après une mise à jour dans la table des propriétés, dans ce cas, nous ajouterons l'utilisateur qui a effectué la modification, le nouveau prix et le prix précédent.

J'effectue une mise à jour sur une propriété :

 APPEL `pa_editarinmueble` ('80000', '170')

Ensuite, nous allons à la table d'audit et nous pouvons voir le changement :

Nous pouvons également voir les résultats dans un rapport en vue d'impression depuis Phpmyadmin. Nous pouvons voir comment les données qui identifient la propriété, la modification apportée et l'utilisateur qui l'a effectuée ont été enregistrées, nous avons également la date et l'heure de la modification.

Ensuite, nous verrons une autre application possible si une propriété est louée, cela change automatiquement son statut en non actif ou nous la rendrons indisponible.

Pour cela nous devons avoir un tableau simple où stocker quel bien est loué, pour un exemple pratique nous ne prendrons pas beaucoup de rigueur dans les données.

 CREATE TABLE `locations` (` id` INT (10) NOT NULL, `property id` INT (10) NOT NULL,` locataire id` INT (11) NOT NULL, PRIMARY KEY (`id`)) COLLATE = 'latin1_swedish_ci ' MOTEUR = InnoDB; 

Ensuite, nous allons créer la procédure stockée pour insérer un nouvel enregistrement dans la table des locations.

 CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_newrental` (IN` property id` INT, IN `tenant id` INT) LANGUE SQL NON DETERMINISTIQUE CONTIENT SQL SQL SECURITY DEFINER COMMENT '' INSERT INTO` locations` (`property id `,` id du locataire `) VALEURS (identifiant du locataire, identifiant du locataire)

Et puis le déclencheur pour modifier les propriétés activé :

 CREATE DEFINER = `root` @` localhost` TRIGGER `rentals_after_insert` APRÈS INSERT ON` locations` POUR CHAQUE LIGNE MISE À JOUR immobilier SET activé = 'no' où id = NEW. propertyid

Ensuite, nous invoquons la procédure stockée où nous attribuons l'identifiant de la propriété et l'identifiant du client ou du locataire que je loue.

 APPEL pour nouvelle location (170.11)

Ensuite, nous allons à la table des biens immobiliers et nous devrions voir que le champ activé change d'état S'il est actif à NON, il n'est pas actif.

Nous avons vu les avantages de l'utilisation de trigger avec des procédures stockées dans MySQL pour :

• Auditez et enregistrez les événements ou les activités de modification des données dans un tableau.
• Modifier l'état d'un champ en activant ou en refusant des autorisations et des actions sur une table
• Il permet également de préserver la cohérence des données en exécutant des actions en fonction d'événements qui affectent une ou plusieurs tables.

Dans un autre tutoriel, nous continuerons à programmer des structures conditionnelles et des structures répétitives dans des procédures stockées.

Avez-vous aimé et aidé ce tutoriel ?Vous pouvez récompenser l'auteur en appuyant sur ce bouton pour lui donner un point positif