One place for hosting & domains

      Triggers

      Comment gérer et utiliser les triggers de base de données MySQL sur Ubuntu 18.04


      L’auteur a choisi the Apache Software Foundation pour recevoir un don dans le cadre du programme Write for DOnations.

      Introduction

      Dans MySQL, un trigger (ou déclencheur) est une commande SQL définie par l’utilisateur qui est automatiquement invoquée lors d’une opération INSERT, DELETE ou UPDATE. Le code trigger est associé à une table et est détruit une fois que la table est supprimée. Vous pouvez spécifier une heure de déclenchement de l’action et définir si elle sera activée avant ou après l’événement défini dans la base de données.

      Les triggers ont plusieurs avantages. Par exemple, vous pouvez les utiliser pour générer la valeur d’une colonne dérivée lors d’une instruction INSERT. Un autre cas d’utilisation consiste à faire respecter l’intégrité référentielle par laquelle vous pouvez utiliser un trigger pour sauvegarder un enregistrement dans plusieurs tables apparentées. D’autres avantages incluent la journalisation des actions des utilisateurs dans les tables d’audit ainsi que la copie en direct des données dans différents schémas de base de données à des fins de redondance, pour éviter un point de défaillance.

      Vous pouvez également utiliser des triggers pour maintenir des règles de validation au niveau de la base de données. Cela permet de partager la source de données entre plusieurs applications sans rompre la logique commerciale. Cela réduit considérablement les allers-retours vers le serveur de la base de données, ce qui améliore donc le temps de réponse de vos applications. Comme le serveur de base de données exécute des triggers, ils peuvent profiter de ressources serveur améliorées telles que la RAM et le processeur.

      Dans ce tutoriel, vous allez créer, utiliser et supprimer différents types de triggers dans votre base de données MySQL.

      Conditions préalables

      Avant de commencer, assurez-vous que vous disposez de ce qui suit :

      Étape 1 — Création d’un exemple de base de données

      Dans cette étape, vous allez créer un exemple de base de données client avec plusieurs tables, afin de démontrer le fonctionnement des triggers MySQL.

      Pour en savoir plus sur les requêtes MySQL, consultez notre Introduction aux requêtes dans MySQL.

      Tout d’abord, connectez-vous à votre serveur MySQL en tant que root :

      Entrez votre mot de passe root MySQL lorsqu’il vous est demandé et cliquez sur ENTER pour continuer. Lorsque vous voyez l’invite mysql>, exécutez la commande suivante pour créer une base de données test_db :

      Output
      Query OK, 1 row affected (0.00 sec)

      Ensuite, accédez à test_db avec :

      Output
      Database changed

      Vous commencerez par créer une table de clients customers. Cette table contiendra les enregistrements des clients (customers), y compris les customer_id, customer_name, et level. Il y aura deux niveaux de clients : BASIC et VIP.

      • Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

      Output
      Query OK, 0 rows affected (0.01 sec)

      Maintenant, ajoutez quelques enregistrements à la table customers. Pour ce faire, exécutez les commandes suivantes une par une :

      • Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');

      • Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');

      • Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

      Vous verrez la sortie suivante après avoir exécuté chacune des commandes INSERT :

      Output
      Query OK, 1 row affected (0.01 sec)

      Pour vous assurer que les échantillons d’enregistrements ont été insérés avec succès, exécutez la commande SELECT :

      Output
      +-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
|           1 | JOHN DOE      | BASIC |
|           2 | MARY ROE      | BASIC |
|           3 | JOHN DOE      | VIP   |
+-------------+---------------+-------+
3 rows in set (0.00 sec)

      Vous allez également créer une autre table pour conserver les informations relatives au compte de clients customers. La table contiendra les champs customer_id et status_notes

      Exécutez la commande suivante :

      • Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

      Ensuite, vous allez créer une table de ventes sales. Cette table contiendra les données de vente (sales) relatives aux différents clients via la colonne customer_id :

      • Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

      Output
      Query OK, 0 rows affected (0.01 sec)

      Vous allez ajouter des échantillons de données aux données de vente sales lors des étapes à venir, tout en testant les triggers. Ensuite, créez une table audit_log pour enregistrer les mises à jour apportées à la table des ventes sales lorsque vous implémenterez le trigger AFTER UPDATE à l’étape 5 :

      • Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

      Output
      Query OK, 0 rows affected (0.02 sec)

      Avec la base de données test_db et les quatre tables en place, vous allez maintenant travailler avec les différents triggers MySQL dans votre base de données.

      Étape 2 — Création d’un trigger Before Insert

      Dans cette étape, vous allez examiner la syntaxe d’un trigger MySQL avant d’appliquer cette logique pour créer un trigger BEFORE INSERT qui valide le champ sales_amount lorsque des données sont insérées dans la table sales.

      La syntaxe générale utilisée pour créer un trigger MySQL est illustrée dans l’exemple suivant :

      DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

      La structure du trigger comprend :

      DELIMITER // : le délimiteur MySQL par défaut est ; — il est nécessaire de le changer en autre chose afin que MySQL traite les lignes suivantes comme une seule commande jusqu’à ce qu’il atteigne votre délimiteur personnalisé. Dans cet exemple, le délimiteur est changé en // et ensuite le délimiteur ; est redéfini à la fin.

      [TRIGGER_NAME] : Un trigger doit avoir un nom et c’est là que vous incluez la valeur.

      [TRIGGER TIME] : Un trigger peut être invoqué à différents moments. MySQL vous permet de définir si le trigger démarrera avant ou après une opération de base de données.

      [TRIGGER EVENT]: Les triggers ne sont appelés que par les opérations INSERT, UPDATE et DELETE. Vous pouvez utiliser n’importe quelle valeur ici en fonction de ce que vous voulez réaliser.

      [TABLE]: Tout trigger que vous créez sur votre base de données MySQL doit être associé à une table.

      FOR EACH ROW : Cette instruction ordonne à MySQL d’exécuter le code du trigger pour chaque ligne que ce dernier affecte.

      [TRIGGER BODY] : le code qui est exécuté lorsque le trigger est invoqué est appelé_ trigger body​​_​. Ce peut être une seule instruction SQL ou plusieurs commandes. Notez que si vous exécutez plusieurs instructions SQL sur le trigger body, vous devez les inclure dans un bloc BEGIN... END.

      Remarque : Lorsque vous créez le trigger body, vous pouvez utiliser les mots-clés OLD et NEW pour accéder aux valeurs de colonnes anciennes et nouvelles entrées lors d’une opération INSERT, UPDATE et DELETE. Dans un déclencheur DELETE, seul le mot-clé OLD peut être utilisé (que vous utiliserez à l’étape 4).

      Vous allez maintenant créer votre premier trigger BEFORE INSERT. Ce trigger sera associé à la table des ventes sales et il sera invoqué avant l’insertion d’un enregistrement pour valider le sales_amount. La fonction du trigger consiste à vérifier si le sales_amount inséré dans la table des ventes est supérieur à 10000 et à signaler une erreur si cette valeur est évaluée comme vraie (true).

      Assurez-vous que vous êtes connecté au serveur MySQL. Ensuite, entrez les commandes MySQL suivantes une par une :

      • DELIMITER //

      • CREATE TRIGGER validate_sales_amount

      • BEFORE INSERT

      • ON sales

      • FOR EACH ROW

      • IF NEW.sales_amount>10000 THEN

      • SIGNAL SQLSTATE '45000'

      • SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

      • END IF//

      • DELIMITER ;

      Vous utilisez l’instruction IF... THEN... END IF pour évaluer si la quantité fournie lors de l’instruction INSERT est comprise dans votre plage. Le trigger est capable d’extraire la nouvelle valeur sales_amount fournie en utilisant le mot-clé NEW.

      Pour faire apparaître un message d’erreur générique, vous utilisez les lignes suivantes pour informer l’utilisateur de l’erreur :

      SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

      Ensuite, insérez un enregistrement avec un sales_amount de 11000 dans la table des ventes pour vérifier si le trigger arrêtera l’opération :

      • Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

      Output
      ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

      Cette erreur montre que le code du trigger fonctionne comme prévu.

      Essayez maintenant un nouvel enregistrement avec une valeur de 7500 pour vérifier si la commande aboutira :

      • Insert into  sales(sales_id, customer_id, sales_amount) values('1','1','7500');

      Comme la valeur est dans la plage recommandée, vous verrez la sortie suivante :

      Output
      Query OK, 1 row affected (0.01 sec)

      Pour confirmer que les données ont été insérées, exécutez la commande suivante :

      La sortie confirme que les données sont dans la table :

      Output
      +----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
|        1 |           1 |         7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

      Dans cette étape, vous avez testé des triggers pour valider les données avant leur insertion dans une base de données.

      Ensuite, vous allez travailler avec le trigger AFTER INSERT pour enregistrer les informations connexes dans différentes tables.

      Étape 3 — Création d’un trigger After Insert

      Les triggers AFTER INSERT sont exécutés lorsque les enregistrements sont insérés avec succès dans une table. Cette fonctionnalité peut être utilisée pour exécuter automatiquement d’autres logiques commerciales. Par exemple, dans une application bancaire, un déclencheur AFTER INSERT peut fermer un compte de prêt lorsqu’un client a fini de rembourser le prêt. Le trigger peut surveiller tous les paiements insérés dans une table de transactions et fermer le prêt automatiquement une fois que le solde du prêt est à zéro.

      Dans cette étape, vous travaillerez avec votre table customer_status en utilisant un trigger AFTER INSERT pour entrer des enregistrements de clients apparentés.

      Pour créer le trigger AFTER INSERT, entrez les commandes suivantes :

      • DELIMITER //

      • CREATE TRIGGER customer_status_records

      • AFTER INSERT

      • ON customers

      • FOR EACH ROW

      • Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//

      • DELIMITER ;

      Output
      Query OK, 0 rows affected (0.00 sec)

      Ici, vous demandez à MySQL d’enregistrer un autre enregistrement dans la table customer_status une fois qu’un nouvel enregistrement client est inséré dans la table de clients customers.

      Maintenant, insérez un nouvel enregistrement dans la table customers pour confirmer que votre code de trigger sera invoqué :

      • Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

      Output
      Query OK, 1 row affected (0.01 sec)

      Comme l’enregistrement a été inséré avec succès, vérifiez qu’un nouvel enregistrement de statut a été inséré dans la table customer_status :

      • Select * from customer_status;

      Output
      +-------------+-----------------------------+
| customer_id | status_notes                |
+-------------+-----------------------------+
|           4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)

      La sortie confirme que le trigger a bien été exécuté.

      Le trigger AFTER INSERT est utile pour surveiller le cycle de vie d’un client. Dans un environnement de production, les comptes clients peuvent subir différentes étapes telles que l’ouverture, la suspension et la fermeture du compte.

      Dans les étapes suivantes, vous allez travailler avec des triggers UPDATE.

      Étape 4 — Création d’un trigger Before Update

      Un trigger BEFORE UPDATE est similaire au trigger BEFORE INSERT — la différence est le moment où ils sont invoqués. Vous pouvez utiliser le trigger BEFORE UPDATE pour vérifier une logique commerciale avant de mettre à jour un enregistrement. Pour tester cela, vous utiliserez la table customers dans laquelle vous avez déjà inséré certaines données.

      Vous disposez de deux niveaux pour vos clients dans la base de données. Dans cet exemple, une fois qu’un compte client est mis à jour au niveau VIP, le compte ne peut pas être retrogradé au niveau BASIC. Pour faire appliquer une telle règle, vous allez créer un trigger BEFORE UPDATE qui s’exécutera avant l’instruction UPDATE, comme indiqué ci-dessous. Si un utilisateur de la base de données tente de rétrograder un client du niveau VIP au niveau BASIC, une exception définie par l’utilisateur sera déclenchée.

      Entrez les commandes SQL suivantes une par une pour créer le trigger BEFORE UPDATE :

      • DELIMITER //

      • CREATE TRIGGER validate_customer_level

      • BEFORE UPDATE

      • ON customers

      • FOR EACH ROW

      • IF OLD.level='VIP' THEN

      • SIGNAL SQLSTATE '45000'

      • SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';

      • END IF //

      • DELIMITER ;

      Vous utilisez le mot-clé OLD pour capturer le niveau fourni par l’utilisateur lors de l’exécution de la commande UPDATE. Encore une fois, vous utilisez l’instruction IF... THEN... END IF pour signaler à l’utilisateur une instruction d’erreur générique.

      Ensuite, exécutez la commande SQL suivante qui tente de rétrograder un compte client associé au customer_id 3 :

      • Update customers set level='BASIC' where customer_id='3';

      Vous verrez la sortie suivante fournissant le SET MESSAGE_TEXT: :

      Output
      ERROR 1644 (45000): A VIP customer can not be downgraded.

      Si vous exécutez la même commande pour un client de niveau BASIC, et que vous essayez de faire passer le compte au niveau VIP, la commande sera exécutée avec succès :

      • Update customers set level='VIP' where customer_id='1';

      Output
      Rows matched: 1  Changed: 1  Warnings: 0

      Vous avez utilisé le trigger BEFORE UPDATE pour faire appliquer une règle commerciale. Vous allez maintenant utiliser un trigger AFTER UPDATE pour l’enregistrement des audits.

      Étape 5 — Création d’un trigger After Update

      Un trigger AFTER UPDATE est invoqué une fois qu’un enregistrement de base de données est mis à jour avec succès. Ce comportement rend le trigger adapté à l’enregistrement des audits. Dans un environnement multi-utilisateurs, l’administrateur peut vouloir consulter un historique des modifications apportées par les utilisateurs dans une table particulière, à des fins d’audit.

      Vous allez créer un trigger qui enregistre l’activité de mise à jour de la table sales. Notre table audit_log contiendra des informations sur les utilisateurs MySQL qui mettent à jour la table sales, la date de la mise à jour, et les valeurs new et old de sales_amount.

      Pour créer le trigger, exécutez les commandes SQL suivantes :

      • DELIMITER //

      • CREATE TRIGGER log_sales_updates

      • AFTER UPDATE

      • ON sales

      • FOR EACH ROW

      • Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//

      • DELIMITER ;

      Vous insérez un nouvel enregistrement dans la table audit_log. Vous utilisez le mot-clé NEW pour récupérer la valeur du sales_id et du nouveau sales_amount En outre, vous utilisez le mot-clé OLD pour récupérer le sales_amount précédent puisque vous voulez enregistrer les deux montants à des fins d’audit.

      La commande SELECT User() récupère l’utilisateur actuel de l’opération et l’instruction NOW() extrait la valeur de la date et de l’heure actuelle du serveur MySQL

      Maintenant, si un utilisateur essaie de mettre à jour la valeur d’un enregistrement dans la table sales, le trigger log_sales_updates va insérer un nouvel enregistrement dans la table audit_log.

      Créons un nouvel enregistrement de ventes avec un sales_id aléatoire de 5 et essayons de le mettre à jour. En premier lieu, insérez l’enregistrement des ventes avec :

      • Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

      Output
      Query OK, 1 row affected (0.00 sec)

      Ensuite, mettez à jour l’enregistrement :

      • Update sales set sales_amount='9000' where sales_id='5';

      Vous verrez la sortie suivante :

      Output
      Rows matched: 1  Changed: 1  Warnings: 0

      Exécutez maintenant la commande suivante pour vérifier si le trigger AFTER UPDATE a pu enregistrer un nouvel enregistrement dans la table audit_log :

      Le trigger a enregistré la mise à jour. Votre sortie indique l’ancien et le nouveau sales_amount enregistrés avec l’utilisateur qui a mis à jour les enregistrements :

      Output
      +--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
+--------+----------+-----------------+------------+----------------+---------------------+
|      1 |        5 |            8000 |       9000 | root@localhost | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)

      Vous disposez également de la date et de l’heure auxquelles la mise à jour a été effectuée, ce qui est précieux à des fins d’audit.

      Ensuite, vous utiliserez le trigger DELETE pour faire respecter l’intégrité du référencement au niveau de la base de données.

      Étape 6 — Création d’un trigger Before Delete

      Les triggers BEFORE DELETE sont invoqués avant qu’une instruction DELETE ne s’exécute sur une table.  Ces types de triggers sont généralement utilisés pour faire respecter l’intégrité référentielle dans différentes tables apparentées. Par exemple, chaque enregistrement de la table sales concerne un customer_id de la table customers. Si un utilisateur de la base de données a supprimé un enregistrement de la table customers qui a un enregistrement lié dans la table sales, vous n’auriez aucun moyen de connaître le client associé à cet enregistrement.

      Pour éviter cela, vous pouvez créer un trigger BEFORE DELETE pour faire appliquer votre logique. Exécutez les commandes SQL suivantes une par une :

      • DELIMITER //

      • CREATE TRIGGER validate_related_records

      • BEFORE DELETE

      • ON customers

      • FOR EACH ROW

      • IF OLD.customer_id in (select customer_id from sales) THEN

      • SIGNAL SQLSTATE '45000'

      • SET MESSAGE_TEXT = 'The customer has a related sales record.';

      • END IF//

      • DELIMITER ;

      Maintenant, essayez de supprimer un client qui a un enregistrement de vente connexe :

      • Delete from customers where customer_id='2';

      Vous recevrez alors la sortie suivante :

      Output
      ERROR 1644 (45000): The customer has a related sales record.

      Le trigger BEFORE DELETE peut empêcher la suppression accidentelle d’informations connexes dans une base de données.

      Toutefois, dans certaines situations, vous pouvez vouloir supprimer tous les enregistrements associés à un enregistrement particulier dans les différentes tables connexes. Dans cette situation, vous utiliserez le trigger AFTER DELETE, que vous allez tester à l’étape suivante.

      Étape 7 — Création d’un trigger After Delete

      Les triggers AFTER DELETE sont activés une fois qu’un enregistrement a été supprimé avec succès. Vous pouvez par exemple utiliser un trigger AFTER DELETE dans une situation où le niveau de remise qu’un client particulier reçoit est déterminé par le nombre de ventes effectuées pendant une période définie. Si certains des enregistrements du client sont supprimés de la table sales, le niveau de réduction du client devrait être réduit.

      Une autre utilisation du trigger AFTER DELETE consiste à supprimer des informations connexes d’une autre table une fois qu’un enregistrement de table de base est supprimé.  Par exemple, vous allez définir un trigger qui supprime l’enregistrement du client si les enregistrements de ventes avec le customer_id correspondant sont supprimés de la table sales. Exécutez la commande suivante pour créer votre trigger :

      • DELIMITER //

      • CREATE TRIGGER delete_related_info

      • AFTER DELETE

      • ON sales

      • FOR EACH ROW

      • Delete from customers where customer_id=OLD.customer_id;//

      • DELIMITER ;

      Ensuite, exécutez ce qui suit pour supprimer tous les enregistrements de ventes associés à un customer_id de 2 :

      • Delete from sales where customer_id='2';

      Output
      Query OK, 1 row affected (0.00 sec)

      Vérifiez maintenant s’il existe des enregistrements pour le client dans la table sales :

      • Select * from customers where customer_id='2';

      Vous recevrez une sortie Empty Set puisque l’enregistrement client associé au customer_id de 2 a été supprimé par le trigger :

      Output
      Empty set (0.00 sec)

      Vous avez maintenant utilisé chacune des différentes formes de triggers pour effectuer des fonctions spécifiques. Ensuite, vous allez voir comment vous pouvez supprimer un trigger de la base de données si vous n’en avez plus besoin.

      Étape 8 — Suppression de triggers

      Comme pour tout autre objet de la base de données, vous pouvez supprimer des triggers à l’aide de la commande DROP. Voici la syntaxe de suppression d’un trigger :

      Drop trigger [TRIGGER NAME];

      Par exemple, pour supprimer le dernier trigger AFTER DELETE que vous avez créé, exécutez la commande suivante :

      • Drop trigger delete_related_info;

      Output
      Query OK, 0 rows affected (0.00 sec)

      La nécessité de supprimer des triggers se pose lorsque vous voulez en recréer la structure. Dans ce cas, vous pouvez supprimer le trigger et en redéfinir un nouveau avec les différentes commandes de trigger.

      Conclusion

      Dans ce tutoriel, vous avez créé, utilisé et supprimé les différents types de triggers d’une base de données MySQL. À l’aide d’un exemple de base de données relative aux clients, vous avez mis en place des triggers pour différents cas d’utilisation tels que la validation des données, une application de logique d’entreprise, l’enregistrement des audits et la mise en application de l’intégrité référentielle.

      Pour obtenir des informations supplémentaires sur l’utilisation de votre base de données MySQL, consultez ce qui suit :



      Source link

      Working with Triggers in a MySQL Database – A Tutorial


      Updated by Linode Contributed by Francis Ndungu

      A trigger is a pre-defined SQL command that is automatically executed when specific actions occur in the database. It can be fired either before or after an INSERT, UPDATE, or DELETE event.

      Triggers are mainly used to maintain software logic in the MySQL server, and they have several benefits:

      • Triggers help keep global operations centralized in one location.

      • They reduce client-side code and help minimize the round-trips made to the database server.

      • They help make applications more scalable across different platforms.

      Some common use-cases of triggers include audit logging, pre-computing database values (e.g. cumulative sums), and enforcing complex data integrity and validation rules.

      In this guide, you will learn:

      Before You Begin

      Make sure you have the following:

      1. A configured Linode server. You can learn how to create and setup a Linode server by reading our Getting Started with Linode guide.

      2. A MySQL server and client installed on the Linode server. Installation guides for MySQL are available for different distributions in our MySQL section.

      Prepare the Database

      To better understand how triggers work, we will create a sample database and add sample data into it. Later, we will create different triggers on the database as a proof of concept exercise.

      1. First, log in to your MySQL Server:

        mysql -u root -p

        Then, enter the root password of your MySQL server and hit Enter to proceed.

      2. Next, you will see a MySQL prompt similar to the one shown below:

          
mysql >

      3. Create a test_database by running the command below:

        CREATE DATABASE test_database;

        Output:

          
Query OK, 1 row affected (0.02 sec)

      4. Switch to the database:

        USE test_database;

        Output:

          
Database changed

      5. Once the database is selected, we will create some tables that we will use for demonstrating triggers. We will begin by creating the stores table. This table will hold information about two sample stores/offices where our hypothetical business operates from:

        CREATE TABLE stores
(
store_id BIGINT PRIMARY KEY AUTO_INCREMENT,
store_name VARCHAR(50)
) ENGINE=InnoDB;

        Output:

          
Query OK, 0 rows affected (0.07 sec)

      6. Next, add two records to the stores table by running the commands below:

        INSERT INTO stores (store_name) VALUES ('Philadelphia');
INSERT INTO stores (store_name) VALUES ('Galloway');

        After each command, you will get the below output:

          
Query OK, 1 row affected (0.08 sec)
...

      7. Confirm the records by running the command below:

        SELECT * FROM stores;

        Output:

          
+----------+--------------+
| store_id | store_name   |
+----------+--------------+
|        1 | Philadelphia |
|        2 | Galloway     |
+----------+--------------+
2 rows in set (0.01 sec)

      8. Next, create the products table. The table will hold different products being offered in the store:

        CREATE TABLE products
(
product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(40),
cost_price DOUBLE,
retail_price DOUBLE,
availability VARCHAR(5)
) ENGINE=InnoDB;

        Output:

          
Query OK, 0 rows affected (0.13 sec)

        • Each product will be uniquely identified by a product_id.

        • A product_name field will specify the names of the items.

        • The cost_price and retail_price fields will determine the buying and selling price respectively.

        • An availability column will define the product availability in the different stores. If the product is only available in our local store (Philadelphia), we will denote it with a LOCAL value. Else, we will use the value of ALL to signify a product that is available in both stores (Philadelphia and Galloway).

      9. Add sample data to the products table:

        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('WIRELESS MOUSE', '18.23', '30.25','ALL');

INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('8 MP CAMERA', '60.40', '85.40','ALL');

INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('SMART WATCH', '189.60', '225.30','LOCAL');

        You will get the output shown below after each insert command:

          
Query OK, 1 row affected (0.02 sec)
...

      10. Confirm if the products were inserted by running the command below:

        SELECT * FROM products;

        Output:

          
+------------+----------------+------------+--------------+--------------+
| product_id | product_name   | cost_price | retail_price | availability |
+------------+----------------+------------+--------------+--------------+
|          1 | WIRELESS MOUSE |      18.23 |        30.25 | ALL          |
|          2 | 8 MP CAMERA    |       60.4 |         85.4 | ALL          |
|          3 | SMART WATCH    |      189.6 |        225.3 | LOCAL        |
+------------+----------------+------------+--------------+--------------+
3 rows in set (0.00 sec)

      11. Next, the products’ availability will be mapped to another table named products_to_stores. This table will just reference the product_id from the products table and the store_id from the stores table where the item is available.

        Create the products_to_stores table by running the code below:

        CREATE TABLE products_to_stores
(
ref_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id BIGINT,
store_id BIGINT
) ENGINE=InnoDB;

        Output:

          
Query OK, 0 rows affected (0.14 sec)

      12. Next, we will create an archived_products table. The table will hold information about deleted products for future reference:

        CREATE TABLE archived_products
(
product_id BIGINT PRIMARY KEY ,
product_name VARCHAR(40),
cost_price DOUBLE,
retail_price DOUBLE,
availability VARCHAR(5)
) ENGINE=InnoDB;

        Output:

          
Query OK, 0 rows affected (0.14 sec)

      13. Lastly, we will create a products_price_history table for tracking the different prices of each product over time:

        CREATE TABLE products_price_history
(
product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
price_date DATETIME,
retail_price DOUBLE
) ENGINE=InnoDB;

        Output:

          
Query OK, 0 rows affected (0.14 sec)

      Once our database structure is in place, we can now go ahead and learn the basic syntax of a MySQL database trigger in order to create our first sample.

      Trigger Syntax

      As indicated earlier, triggers are fired automatically either before or after an SQL command is run in the database. The basic syntax for creating triggers is as follows:

      CREATE TRIGGER TRIGGER_NAME

TRIGGER_TIME TRIGGER_EVENT

ON TABLE_NAME FOR EACH ROW

[TRIGGER BODY];

      • TRIGGER_NAME: Each trigger must have a unique name and you should define it here.

      • TRIGGER_TIME: Either BEFORE or AFTER.

      • TRIGGER_EVENT: You need to specify the database event that will invoke the trigger: INSERT, UPDATE, or DELETE.

      • TRIGGER BODY: This specifies the actual SQL command (or commands) that you want to be run by your trigger.

      If a trigger body has more than one SQL statement, you must enclose it within a BEGIN...END block. As well, you will need to temporarily change the DELIMITER that signals the end of the trigger body to a new value. This ensures that the statements within the body are not prematurely interpreted by your MySQL client. An example of this looks like the following:

      DELIMITER &&

CREATE TRIGGER TRIGGER_NAME

TRIGGER_TIME TRIGGER_EVENT

ON TABLE_NAME FOR EACH ROW

BEGIN

[TRIGGER BODY]

END &&

DELIMITER ;

      Note

      The last line of this example changes the DELIMITER back to the default ; value.

      Creating Before Event Triggers

      In this section, we will look into the different types of triggers that are fired before a database operation. These include the BEFORE INSERT, BEFORE UPDATE, and BEFORE DELETE triggers.

      Creating a Before Insert Trigger

      We will create our first BEFORE INSERT trigger. The trigger will make sure that the retail price of a product is greater than the cost price whenever items are inserted into the products table. Otherwise, the database user will get an error.

      1. While still on the mysql > prompt, enter the command below:

        DELIMITER $$

CREATE TRIGGER price_validator

BEFORE INSERT

ON products FOR EACH ROW

IF NEW.cost_price>=NEW.retail_price

THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Retail price must be greater than cost price.';

END IF $$

DELIMITER ;

        • The above code defines the trigger name (price_validator), time (BEFORE), event (INSERT), and the table (products) to be affected.

        • Our trigger uses the NEW keyword to check the cost_price and retail_price before a record is inserted to the products table, using the IF...THEN...END IF statement.

        • If the cost_price is greater or equal to the retail price, our triggers tells MySQL to throw a custom exception instructing the user to rectify the error.

      2. To test the trigger above, try inserting a product that violates the validation rule:

        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('GAMING MOUSE PAD', '145.00', '144.00','LOCAL');

        Output:

          
ERROR 1644 (45000): Retail price must be greater than cost price.

        The above insert commands should fail because the retail_price (144.00) is not greater than the cost_price (145.00).

      Creating a Before Update Trigger

      Next, we will create a BEFORE UPDATE trigger. This trigger will prevent database users from editing a product name once a product has been inserted into the database. If you have multiple users working in the database, a BEFORE UPDATE trigger may be used to make values read-only, and this can prevent malicious or careless users from modifying records unnecessarily.

      1. Create a new product_name_validator trigger with the command below:

        DELIMITER $$

CREATE TRIGGER product_name_validator

BEFORE UPDATE

ON products FOR EACH ROW

IF NEW.product_name<>OLD.product_name

THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product name is read-only and it can not be changed.';

END IF $$

DELIMITER ;

        This trigger compares the values of the new product_name (NEW.product_name) and the old name already in the database (OLD.product_name). If there is a mismatch, an exception is thrown.

      2. To invoke the product_name_validator trigger, we can attempt to update the name of the product with the ID 1:

        UPDATE products SET product_name='WIRELESS BLUETOOTH MOUSE' WHERE product_id='1';

        Output:

          
ERROR 1644 (45000): Product name is read-only and it can not be changed.

      Defining a Before Delete Trigger

      In this section, you will see how you can define a BEFORE DELETE trigger to prevent users from deleting specific records from a table.

      1. To create the prevent_delete trigger, run the command below:

        DELIMITER $$

CREATE TRIGGER prevent_delete

BEFORE DELETE

ON products FOR EACH ROW

IF OLD.availability='ALL'

THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The product can not be deleted because it is available in ALL stores.';

END IF $$

DELIMITER ;

        This trigger will prevent products marked with a value of ALL in the availability column from being deleted.

      2. Next, try to delete the first product from the products table and see if the trigger will be invoked:

        DELETE FROM products WHERE product_id='1';

        Output:

          
ERROR 1644 (45000): The product can not be deleted because it is available in ALL stores.

      We have looked at the different triggers that are invoked before a database operation. Next, we will look into the other types of triggers that are fired after database events.

      Creating After Event Triggers

      In a production environment, you may want some triggers to be automatically executed after a database event occurs (for example, inserting records into different tables). The examples below demonstrate how these kinds of triggers can be used in our sample database.

      Creating an After Insert Trigger

      This example creates a trigger named product_availability that inserts mapping records into the products_to_stores table. This trigger is used to enforce business logic; in particular, it helps define the product availability for the different stores.

      1. Run the code below to create the product_availability trigger. Since we have multiple lines of code in the trigger body, we will use a BEGIN...END block:

        DELIMITER $$

CREATE TRIGGER product_availability

AFTER INSERT

ON products FOR EACH ROW

BEGIN

IF NEW.availability='LOCAL' then

INSERT INTO products_to_stores (product_id, store_id) VALUES (NEW.product_id, '1');

ELSE

INSERT INTO products_to_stores (product_id, store_id) VALUES (NEW.product_id, '1');

INSERT INTO products_to_stores (product_id, store_id) VALUES (NEW.product_id, '2');

END IF;

END $$

DELIMITER ;

        • When an item is being inserted into the products table, the trigger will check the availability field.

        • If it is marked with the LOCAL value, the product will be made available in one store only.

        • Any other value will instruct the trigger to make the product available to the two stores that we created earlier.

      2. To see the product_availability trigger in action, insert the two records to the products table:

        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('BLUETOOTH KEYBOARD', '17.60', '23.30','LOCAL');
INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('DVB-T2 RECEIVE', '49.80', '53.40','ALL');

      3. Then, query the products_to_stores table:

        SELECT * FROM products_to_stores;

        You should see an output similar to the one shown below:

          
+--------+------------+----------+
| ref_id | product_id | store_id |
+--------+------------+----------+
|      1 |          4 |        1 |
|      2 |          5 |        1 |
|      3 |          5 |        2 |
+--------+------------+----------+
3 rows in set (0.00 sec)

      Defining an After Update Trigger

      A trigger can also be fired after an UPDATE event. We will see how we can leverage this type of trigger to keep track of price changes in our store over time.

      1. Create a product_history_updater trigger by running the command below:

        CREATE TRIGGER product_history_updater

AFTER UPDATE

ON products FOR EACH ROW

INSERT INTO products_price_history (product_id, price_date, retail_price) VALUES (OLD.product_id, NOW(), NEW.retail_price);

        This trigger records changes to a product’s retail_price in the products_price_history table.

        Note

        Unlike previous examples, this trigger only has one statement in the trigger’s body, so we do not need to change the DELIMITER.

      2. Then, try updating the price of the first product by running the command below:

        UPDATE products SET retail_price='36.75' WHERE product_id='1';

      3. Next, query the products_price_history table to see if the price change was logged:

        SELECT * FROM products_price_history;

        If the trigger worked as expected, you should get the below output:

          
+------------+---------------------+--------------+
| product_id | price_date          | retail_price |
+------------+---------------------+--------------+
|          1 | 2020-01-28 11:46:21 |        36.75 |
+------------+---------------------+--------------+
1 row in set (0.00 sec)

      Creating an After Delete Trigger

      In some cases, you might want to log delete operations after a specific action has occurred in the database. You can achieve this by using the AFTER DELETE trigger.

      1. Create a new the product_archiver trigger with the command below:

        CREATE TRIGGER product_archiver

AFTER DELETE

ON products FOR EACH ROW

INSERT INTO archived_products (product_id, product_name, cost_price, retail_price, availability) VALUES (OLD.product_id, OLD.product_name, OLD.cost_price, OLD.retail_price, OLD.availability);

        This trigger archives deleted products in a separate table named archived_products. When an item is deleted from the main products table, our trigger will automatically log it to the archived_products table for future reference.

      2. Next, delete a product from the products table and see if the trigger will be invoked:

        DELETE FROM products WHERE product_id='3';

      3. Now, if you check the archived_products table, you should see one record:

        SELECT * FROM archived_products;

        Output:

          
+------------+--------------+------------+--------------+--------------+
| product_id | product_name | cost_price | retail_price | availability |
+------------+--------------+------------+--------------+--------------+
|          3 | SMART WATCH  |      189.6 |        225.3 | LOCAL        |
+------------+--------------+------------+--------------+--------------+
1 row in set (0.00 sec)

      Deleting a Trigger

      You have seen the different types of triggers and how they can be used in a production environment. Sometimes, you may want to remove a trigger from the database.

      You can delete a trigger if you don’t want to use it anymore using the syntax below:

      DROP TRIGGER IF EXISTS TRIGGER_NAME;

      Note

      The IF EXISTS keyword is an optional parameters that only deletes a trigger if it exists.

      For example, to delete the product_archiving trigger that we defined above, use the below command:

      DROP TRIGGER IF EXISTS product_archiver;

      Output:

        
Query OK, 0 rows affected (0.00 sec)

      Caution

      Be cautious when deleting tables associated with triggers. Once a table is dropped from the MySQL database, the related triggers are also automatically deleted.

      More Information

      You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

      This guide is published under a CC BY-ND 4.0 license.





      Source link

      How To Manage and Use MySQL Database Triggers on Ubuntu 18.04


      The author selected the the Apache Software Foundation to receive a donation as part of the Write for DOnations program.

      Introduction

      In MySQL a trigger is a user-defined SQL command that is invoked automatically during an INSERT, DELETE, or UPDATE operation. The trigger code is associated with a table and is destroyed once a table is dropped. You can specify a trigger action time and set whether it will be activated before or after the defined database event.

      Triggers have several advantages. For instance, you can use them to generate the value of a derived column during an INSERT statement. Another use case is enforcing referential integrity where you can use a trigger to save a record to multiple related tables. Other benefits include logging user actions to audit tables as well as live-copying data across different database schemas for redundancy purposes to prevent a single point of failure.

      You can also use triggers to keep validation rules at the database level. This helps in sharing the data source across multiple applications without breaking the business logic. This greatly reduces round-trips to the database server, which therefore improves the response time of your applications. Since the database server executes triggers, they can take advantage of improved server resources such as RAM and CPU.

      In this tutorial, you’ll create, use, and delete different types of triggers on your MySQL database.

      Prerequisites

      Before you begin, make sure you have the following:

      Step 1 — Creating a Sample Database

      In this step, you’ll create a sample customer database with multiple tables for demonstrating how MySQL triggers work.

      To understand more about MySQL queries read our Introduction to Queries in MySQL.

      First, log in to your MySQL server as root:

      Enter your MySQL root password when prompted and hit ENTER to continue. When you see the mysql> prompt, run the following command to create a test_db database:

      Output
      Query OK, 1 row affected (0.00 sec)

      Next, switch to the test_db with:

      Output
      Database changed

      You’ll start by creating a customers table. This table will hold the customers’ records including the customer_id, customer_name, and level. There will be two customer levels: BASIC and VIP.

      • Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

      Output
      Query OK, 0 rows affected (0.01 sec)

      Now, add a few records to the customers table. To do this, run the following commands one by one:

      • Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');

      • Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');

      • Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

      You’ll see the following output after running each of the INSERT commands:

      Output
      Query OK, 1 row affected (0.01 sec)

      To make sure that the sample records were inserted successfully, run the SELECT command:

      Output
      +-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
|           1 | JOHN DOE      | BASIC |
|           2 | MARY ROE      | BASIC |
|           3 | JOHN DOE      | VIP   |
+-------------+---------------+-------+
3 rows in set (0.00 sec)

      You’ll also create another table for holding related information about the customers account. The table will have a customer_id and status_notes fields.

      Run the following command:

      • Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

      Next, you’ll create a sales table. This table will hold sales data related to the different customers through the customer_id column:

      • Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

      Output
      Query OK, 0 rows affected (0.01 sec)

      You’ll add sample data to the sales data in the coming steps while testing the triggers. Next, create an audit_log table to log updates made to the sales table when you implement the AFTER UPDATE trigger in Step 5:

      • Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

      Output
      Query OK, 0 rows affected (0.02 sec)

      With the test_db database and the four tables in place, you’ll now move on to work with the different MySQL triggers in your database.

      Step 2 — Creating a Before Insert Trigger

      In this step, you’ll examine the syntax of a MySQL trigger before applying this logic to create a BEFORE INSERT trigger that validates the sales_amount field when data is inserted into the sales table.

      The general syntax for creating a MySQL trigger is shown in the following example:

      DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

      The structure of the trigger includes:

      DELIMITER //: The default MySQL delimiter is ;—it’s necessary to change it to something else in order for MySQL to treat the following lines as one command until it hits your custom delimiter. In this example, the delimiter is changed to // and then the ; delimiter is redefined at the end.

      [TRIGGER_NAME]: A trigger must have a name and this is where you include the value.

      [TRIGGER TIME]: A trigger can be invoked during different timings. MySQL allows you to define if the trigger will initiate before or after a database operation.

      [TRIGGER EVENT]: Triggers are only invoked by INSERT, UPDATE, and DELETE operations. You can use any value here depending on what you want to achieve.

      [TABLE]: Any trigger that you create on your MySQL database must be associated with a table.

      FOR EACH ROW: This statement tells MySQL to execute the trigger code for every row that the trigger affects.

      [TRIGGER BODY]: The code that is executed when the trigger is invoked is called a trigger body. This can be a single SQL statement or multiple commands. Note that if you are executing multiple SQL statements on the trigger body, you must wrap them between a BEGIN...END block.

      Note: When creating the trigger body, you can use the OLD and NEW keywords to access the old and new column values entered during an INSERT, UPDATE, and DELETE operation. In a DELETE trigger, only the OLD keyword can be used (which you’ll use in Step 4).

      Now you’ll create your first BEFORE INSERT trigger. This trigger will be associated with the sales table and it will be invoked before a record is inserted to validate the sales_amount. The function of the trigger is to check if the sales_amount being inserted to the sales table is greater than 10000 and raise an error if this evaluates to true.

      Make sure you’re logged in to the MySQL server. Then, enter the following MySQL commands one by one:

      • DELIMITER //

      • CREATE TRIGGER validate_sales_amount

      • BEFORE INSERT

      • ON sales

      • FOR EACH ROW

      • IF NEW.sales_amount>10000 THEN

      • SIGNAL SQLSTATE '45000'

      • SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

      • END IF//

      • DELIMITER ;

      You’re using the IF...THEN...END IF statement to evaluate if the amount being supplied during the INSERT statement is within your range. The trigger is able to extract the new sales_amount value being supplied by using the NEW keyword.

      To raise a generic error message, you use the following lines to inform the user about the error:

      SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

      Next, insert a record with a sales_amount of 11000 to the sales table to check if the trigger will stop the operation:

      • Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

      Output
      ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

      This error shows that the trigger code is working as expected.

      Now try a new record with a value of 7500 to check if the command will be successful:

      • Insert into  sales(sales_id, customer_id, sales_amount) values('1','1','7500');

      Since the value is within the recommended range, you’ll see the following output:

      Output
      Query OK, 1 row affected (0.01 sec)

      To confirm that the data was inserted run the following command:

      The output confirms that the data is in the table:

      Output
      +----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
|        1 |           1 |         7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

      In this step you’ve tested triggers to validate data before insertion into a database.

      Next, you’ll work with the AFTER INSERT trigger to save related information into different tables.

      Step 3 — Creating an After Insert Trigger

      AFTER INSERT triggers are executed when records are successfully inserted into a table. This functionality can be used to run other business-related logics automatically. For instance, in a bank application, an AFTER INSERT trigger can close a loan account when a customer finishes paying off the loan. The trigger can monitor all payments inserted to a transaction table and close the loan automatically once the loan balance is zero.

      In this step, you’ll work with your customer_status table by using an AFTER INSERT trigger to enter related customer records.

      To create the AFTER INSERT trigger, enter the following commands:

      • DELIMITER //

      • CREATE TRIGGER customer_status_records

      • AFTER INSERT

      • ON customers

      • FOR EACH ROW

      • Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//

      • DELIMITER ;

      Output
      Query OK, 0 rows affected (0.00 sec)

      Here you instruct MySQL to save another record to the customer_status table once a new customer record is inserted to the customers table.

      Now, insert a new record in the customers table to confirm your trigger code will be invoked:

      • Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

      Output
      Query OK, 1 row affected (0.01 sec)

      Since the record was inserted successfully, check that a new status record was inserted into the customer_status table:

      • Select * from customer_status;

      Output
      +-------------+-----------------------------+
| customer_id | status_notes                |
+-------------+-----------------------------+
|           4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)

      The output confirms that the trigger ran successfully.

      The AFTER INSERT trigger is useful in monitoring the lifecycle of a customer. In a production environment, customers’ accounts may undergo different stages such as account opening, suspension, and closing.

      In the following steps you’ll work with UPDATE triggers.

      Step 4 — Creating a Before Update Trigger

      A BEFORE UPDATE trigger is similar to the BEFORE INSERT trigger—the difference is when they are invoked. You can use the BEFORE UPDATE trigger to check a business logic before a record is updated. To test this, you’ll use the customers table in which you’ve inserted some data already.

      You have two levels for your customers in the database. In this example, once a customer account is upgraded to the VIP level, the account can not be downgraded to the BASIC level. To enforce such a rule, you will create a BEFORE UPDATE trigger that will execute before the UPDATE statement as shown following. If a database user tries to downgrade a customer to the BASIC level from the VIP level, a user-defined exception will be triggered.

      Enter the following SQL commands one by one to create the BEFORE UPDATE trigger:

      • DELIMITER //

      • CREATE TRIGGER validate_customer_level

      • BEFORE UPDATE

      • ON customers

      • FOR EACH ROW

      • IF OLD.level='VIP' THEN

      • SIGNAL SQLSTATE '45000'

      • SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';

      • END IF //

      • DELIMITER ;

      You use the OLD keyword to capture the level that the user is supplying when running the UPDATE command. Again, you use the IF...THEN...END IF statement to signal a generic error statement to the user.

      Next, run the following SQL command that tries to downgrade a customer account associated with the customer_id of 3:

      • Update customers set level='BASIC' where customer_id='3';

      You’ll see the following output providing the SET MESSAGE_TEXT:

      Output
      ERROR 1644 (45000): A VIP customer can not be downgraded.

      If you run the same command to a BASIC level customer, and try to upgrade the account to the VIP level, the command will execute successfully:

      • Update customers set level='VIP' where customer_id='1';

      Output
      Rows matched: 1  Changed: 1  Warnings: 0

      You’ve used the BEFORE UPDATE trigger to enforce a business rule. Now you’ll move on to use an AFTER UPDATE trigger for audit logging.

      Step 5 — Creating an After Update Trigger

      An AFTER UPDATE trigger is invoked once a database record is updated successfully. This behavior makes the trigger suitable for audit logging. In a multi-user environment, the administrator may want to view a history of users updating records in a particular table for audit purposes.

      You’ll create a trigger that logs the update activity of the sales table. Our audit_log table will contain information about the MySQL users updating the sales table, the date of the update, and the new and old sales_amount values.

      To create the trigger, run the following SQL commands:

      • DELIMITER //

      • CREATE TRIGGER log_sales_updates

      • AFTER UPDATE

      • ON sales

      • FOR EACH ROW

      • Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//

      • DELIMITER ;

      You insert a new record to the audit_log table. You use the NEW keyword to retrieve the value of the sales_id and the new sales_amount. Also, you use the OLD keyword to retrieve the previous sales_amount since you want to log both amounts for audit purposes.

      The command SELECT USER() retrieves the current user performing the operation and the NOW() statement retrieves the value of the current date and time from the MySQL server.

      Now if a user tries to update the value of any record in the sales table, the log_sales_updates trigger will insert a new record to the audit_log table.

      Let’s create a new sales record with a random sales_id of 5 and try to update it. First, insert the sales record with:

      • Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

      Output
      Query OK, 1 row affected (0.00 sec)

      Next, update the record:

      • Update sales set sales_amount='9000' where sales_id='5';

      You’ll see the following output:

      Output
      Rows matched: 1  Changed: 1  Warnings: 0

      Now run the following command to verify if the AFTER UPDATE trigger was able to register a new record into the audit_log table:

      The trigger logged the update. Your output shows the previous sales_amount and new amount registered with the user that updated the records:

      Output
      +--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
+--------+----------+-----------------+------------+----------------+---------------------+
|      1 |        5 |            8000 |       9000 | root@localhost | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)

      You also have the date and time the update was performed, which are valuable for audit purposes.

      Next you’ll use the DELETE trigger to enforce referencing integrity at the database level.

      Step 6 — Creating a Before Delete Trigger

      BEFORE DELETE triggers invoke before a DELETE statement executes on a table. These kinds of triggers are normally used to enforce referential integrity on different related tables. For example, each record on the sales table relates to a customer_id from the customers table. If a database user deleted a record from the customers table that has a related record in the sales table, you would have no way of knowing the customer associated with that record.

      To avoid this, you can create a BEFORE DELETE trigger to enforce your logic. Run the following SQL commands one by one:

      • DELIMITER //

      • CREATE TRIGGER validate_related_records

      • BEFORE DELETE

      • ON customers

      • FOR EACH ROW

      • IF OLD.customer_id in (select customer_id from sales) THEN

      • SIGNAL SQLSTATE '45000'

      • SET MESSAGE_TEXT = 'The customer has a related sales record.';

      • END IF//

      • DELIMITER ;

      Now, try to delete a customer that has a related sales record:

      • Delete from customers where customer_id='2';

      As a result you’ll receive the following output:

      Output
      ERROR 1644 (45000): The customer has a related sales record.

      The BEFORE DELETE trigger can prevent accidental deletion of related information in a database.

      However, in some situations, you may want to delete all the records associated with a particular record from the different related tables. In this situation you would use the AFTER DELETE trigger, which you’ll test in the next step.

      Step 7 — Creating an After Delete Trigger

      AFTER DELETE triggers are activated once a record has been deleted successfully. An example of how you can use an AFTER DELETE trigger is a situation in which the discount level a particular customer receives is determined by the number of sales made during a defined period. If some of the customer’s records are deleted from the sales table, the customer discount level would need to be downgraded.

      Another use of the AFTER DELETE trigger is deleting related information from another table once a record from a base table is deleted. For instance, you’ll set a trigger that deletes the customer record if the sales records with the related customer_id are deleted from the sales table. Run the following command to create your trigger:

      • DELIMITER //

      • CREATE TRIGGER delete_related_info

      • AFTER DELETE

      • ON sales

      • FOR EACH ROW

      • Delete from customers where customer_id=OLD.customer_id;//

      • DELIMITER ;

      Next, run the following to delete all sales records associated with a customer_id of 2:

      • Delete from sales where customer_id='2';

      Output
      Query OK, 1 row affected (0.00 sec)

      Now check if there are records for the customer from the sales table:

      • Select * from customers where customer_id='2';

      You will receive an Empty Set output since the customer record associated with the customer_id of 2 was deleted by the trigger:

      Output
      Empty set (0.00 sec)

      You’ve now used each of the different forms of triggers to perform specific functions. Next you will see how you can remove a trigger from the database if you no longer need it.

      Step 8 — Deleting Triggers

      Similarly to any other database object, you can delete triggers using the DROP command. The following is the syntax for deleting a trigger:

      Drop trigger [TRIGGER NAME];

      For instance, to delete the last AFTER DELETE trigger that you created, run the following command:

      • Drop trigger delete_related_info;

      Output
      Query OK, 0 rows affected (0.00 sec)

      The need to delete triggers arises when you want to recreate its structure. In such a case, you can drop the trigger and redefine a new one with the different trigger commands.

      Conclusion

      In this tutorial you’ve created, used, and deleted the different kinds of triggers from a MySQL database. Using an example customer-related database you’ve implemented triggers for different use cases such as data validation, business-logic application, audit logging, and enforcing referential integrity.

      For further information on using your MySQL database, check out the following:



      Source link

      Forgot Your Password ?