One place for hosting & domains

      Database

      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 Use Database Migrations and Seeders to Abstract Database Setup in Laravel


      Migrations and seeders are powerful database utilities provided by the Laravel PHP framework to allow developers to quickly bootstrap, destroy and recreate an application’s database. These utilities help to minimize database inconsistency problems that can arise with multiple developers working on the same application: new contributors need only to run a couple artisan commands to set the database up on a fresh install.

      In this guide, we’ll create migrations and seeders to populate a Laravel demo application’s database with sample data. At the end, you will be able to destroy and recreate your database tables as many times as you want, using only artisan commands.

      Prerequisites

      In order to follow this guide, you’ll need:

      Note: In this guide, we’ll use a containerized development environment managed by Docker Compose to run the application, but you may also opt to run the application on a LEMP server. To set this up, you can follow our guide on How to Install and Configure Laravel with LEMP on Ubuntu 18.04.

      Step 1 — Obtaining the Demo Application

      To get started, we’ll fetch the demo Laravel application from its GitHub repository. We’re interested in the tutorial-02 branch, which includes a Docker Compose setup to run the application on containers. In this example, we’ll download the application to our home folder, but you can use any directory of your choice:

      • cd ~
      • curl -L https://github.com/do-community/travellist-laravel-demo/archive/tutorial-2.0.1.zip -o travellist.zip

      Because we downloaded the application code as a .zip file, we’ll need the unzip command to unpack it. If you haven’t done so recently, update your machine’s local package index:

      Then install the unzip package:

      Following that, unzip the contents of the application:

      Then rename the unpacked directory to travellist-demo for easier access:

      • mv travellist-laravel-demo-tutorial-2.0.1 travellist-demo

      In the next step, we’ll create a .env configuration file to set up the application.

      Step 2 — Setting Up the Application’s .env File

      In Laravel, a .env file is used to set up environment-dependent configurations, such as credentials and any information that might vary between deployments. This file is not included in revision control.

      Warning: The environment configuration file contains sensitive information about your server, including database credentials and security keys. For that reason, you should never share this file publicly.

      The values contained in the .env file will take precedence over the values set in regular configuration files located in the config directory. Each installation on a new environment requires a tailored environment file to define things such as database connection settings, debug options, and the application URL, among other items that may vary depending on which environment the application is running.

      Navigate to the travellist-demo directory:

      We’ll now create a new .env file to customize the configuration options for the development environment we’re setting up. Laravel comes with an example.env file that we can copy to create our own:

      Open this file using nano or your text editor of choice:

      This is how your .env file looks like now:

      .env

      APP_NAME=Travellist
      APP_ENV=dev
      APP_KEY=
      APP_DEBUG=true
      APP_URL=http://localhost:8000 
      
      LOG_CHANNEL=stack
      
      DB_CONNECTION=mysql
      DB_HOST=db
      DB_PORT=3306
      DB_DATABASE=travellist
      DB_USERNAME=travellist_user
      DB_PASSWORD=password

      The current .env file from the travellist demo application contains settings to use the containerized environment we’ve created with Docker Compose in the last part of this series. You don’t need to change any of these values, but you are free to modify the DB_DATABASE, DB_USERNAME and DB_PASSWORD if you wish, since these are pulled by our docker-compose.yml file automatically to set up the development database. Just make sure the DB_HOST variable remains unchanged, since it references the name of our database service within the Docker Compose environment.

      If you make any changes to the file, make sure to save and close it by pressing CTRL + X, Y, then ENTER.

      Note: If you have opted to run the application on a LEMP server, you’ll need to change the highlighted values to reflect your own database settings, including the DB_HOST variable.

      Step 3 — Installing Application Dependencies with Composer

      We’ll now use Composer, PHP’s dependency management tool, to install the application’s dependencies and make sure we’re able to execute artisan commands.

      Bring up your Docker Compose environment with the following command.
      This will build the travellist image for the app service and pull in the additional Docker images required by the nginx and db services, in order to create the application environment:

      Output

      Creating network "travellist-demo_travellist" with driver "bridge" Building app Step 1/11 : FROM php:7.4-fpm ---> fa37bd6db22a Step 2/11 : ARG user ---> Running in 9259bb2ac034 … Creating travellist-app ... done Creating travellist-nginx ... done Creating travellist-db ... done

      This operation might take a few minutes to complete. Once the process is finished, we can run Composer to install the application’s dependencies.

      To execute composer and other commands in the app service container, we’ll use docker-compose exec. The exec command allows us to execute any command of our choice on containers managed by Docker Compose. It uses the following syntax: docker-compose exec service_name command.

      Note: In case you have opted to use a LEMP server to run the demo application, you should ignore the docker-compose exec app portion of the commands listed throughout this guide. For example, instead of running the following command as it’s written, you would just run:

      To execute composer install in the app container, run:

      • docker-compose exec app composer install

      Output

      Loading composer repositories with package information Installing dependencies (including require-dev) from lock file Package operations: 85 installs, 0 updates, 0 removals - Installing doctrine/inflector (1.3.1): Downloading (100%) - Installing doctrine/lexer (1.2.0): Downloading (100%) - Installing dragonmantank/cron-expression (v2.3.0): Downloading (100%) …

      When Composer is finished installing the application’s dependencies, you’ll be able to execute artisan commands. To test that the application is able to connect to the database, run the following command which will clean up any pre-existing tables:

      • docker-compose exec app php artisan db:wipe

      This command will drop any pre-existing tables on the configured database. If it ran successfully and the application is able to connect to the database, you’ll see output like this:

      Output

      Dropped all tables successfully.

      Now that you have installed the application dependencies with Composer, you can use the artisan tool to create migrations and seeders.

      Step 4 — Creating Database Migrations

      The artisan command line tool that ships with Laravel contains a series of helper commands that can be used to manage the application and bootstrap new classes. To generate a new migration class, we can use the make:migration command as follows:

      • docker-compose exec app php artisan make:migration create_places_table

      Laravel infers the operation to be executed (create), the name of the table (places), and whether this migration will create a new table or not, based on the descriptive name provided to the make:migration command.

      You’ll see output similar to this:

      Output

      Created Migration: 2020_02_03_143622_create_places_table

      This will generate a new file in the application’s database/migrations directory. The timestamp included in the auto-generated file is used by Laravel to determine in which order migrations should be executed.

      Use your text editor of choice to open the generated migration file. Remember to replace the highlighted value with your own migration file name:

      • nano database/migrations/2020_02_03_143622_create_places_table.php

      The generated migration file contains a class called CreatePlacesTable:

      database/migrations/2020_02_03_143622_create_places_table.php

      <?php
      
      use IlluminateDatabaseMigrationsMigration;
      use IlluminateDatabaseSchemaBlueprint;
      use IlluminateSupportFacadesSchema;
      
      class CreatePlacesTable extends Migration
      {
          /**
           * Run the migrations.
           *
           * @return void
           */
          public function up()
          {
              Schema::create('places', function (Blueprint $table) {
                  $table->bigIncrements('id');
                  $table->timestamps();
              });
          }
      
          /**
           * Reverse the migrations.
           *
           * @return void
           */
          public function down()
          {
              Schema::dropIfExists('places');
          }
      }
      
      

      This class has two methods: up and down. Both methods contain bootstrap code that you can extend to customize what happens when that migration is executed and also what happens when it is rolled back.

      We’ll modify the up method so that the places table reflects the structure we’re already using in the current application’s version:

      • id: primary key field.
      • name: name of the place.
      • visited: whether or not this place was already visited.

      The Laravel schema builder exposes methods for creating, updating and deleting tables in a database. The Blueprint class defines the table’s structure and it provides several methods to abstract the definition of each table field.

      The auto-generated code sets up a primary id field called id. The timestamps method creates two datetime fields that are automatically updated by the underlying database classes when data is inserted or updated within that table. In addition to these, we’ll need to include a name and a visited field.

      Our name field will be of type string, and our visited field will be set with the boolean type. We’ll also set a default value of 0 for the visited field, so that if no value is passed, it means the place was not visited yet. This is how the up method will look like now:

      database/migrations/2020_02_03_143622_create_places_table.php

      …
          public function up()
          {
              Schema::create('places', function (Blueprint $table) {
                  $table->bigIncrements('id');
                  $table->string('name', 100);
                  $table->boolean('visited')->default(0);
                  $table->timestamps();
              });
          }
      …
      

      Note: You can find the full list of available column types in the Laravel documentation.

      After including the two highlighted lines on your own migration script, save and close the file.

      Your migration is now ready to be executed via artisan migrate. However, that would only create an empty table; we also need to be able to insert sample data for development and testing. In the next step, we’ll see how to do that using database seeders.

      Step 5 — Creating Database Seeders

      A seeder is a special class used to generate and insert sample data (seeds) in a database. This is an important feature in development environments, since it allows you to recreate the application with a fresh database, using sample values that you’d otherwise have to manually insert each time the database is recreated.

      We’ll now use the artisan command to generate a new seeder class for our places table called PlacesTableSeeder:

      • docker-compose exec app php artisan make:seeder PlacesTableSeeder

      The command will create a new file called PlacesTableSeeder.php inside the database/seeds directory. Open that file using your text editor of choice:

      • nano database/seeds/PlacesTableSeeder.php

      This is what the auto-generated PlacesTableSeeder.php file looks like:

      database/seeds/PlacesTableSeeder.php

      <?php
      
      use IlluminateDatabaseSeeder;
      
      class PlacesTableSeeder extends Seeder
      {
          /**
           * Run the database seeds.
           *
           * @return void
           */
          public function run()
          {
              //
          }
      }
      
      

      Our new seeder class contains an empty method named run. This method will be called when the db:seed Artisan command is executed.

      We need to edit the run method in order to include instructions to insert sample data in the database. We’ll use the Laravel query builder to streamline this process.

      The Laravel query builder offers a fluent interface for database operations such as inserting, updating, deleting, and retrieving data. It also introduces safeguards against SQL injection attacks. The query builder is exposed by the DB facade - a static proxy to underlying database classes in the service container.

      To get started, we’ll create a static class variable to hold all the sample places we want to insert into the database as an array. This will allow us to use a foreach loop to iterate through all values, inserting each one in the database using the query builder.

      We’ll call this variable $places:

      database/seeds/PlacesTableSeeder.php

      <?php
      
      use IlluminateDatabaseSeeder;
      
      class PlacesTableSeeder extends Seeder
      {
          static $places = [
              'Berlin',
              'Budapest',
              'Cincinnati',
              'Denver',
              'Helsinki',
              'Lisbon',
              'Moscow',
              'Nairobi',
              'Oslo',
              'Rio',
              'Tokyo'
          ];

      Next, we’ll need to include a use statement at the top of our PlacesTableSeeder class to facilitate referencing the DB facade throughout the code:

      database/seeds/PlacesTableSeeder.php

      <?php
      
      use IlluminateDatabaseSeeder;
      use IlluminateSupportFacadesDB;
      
      class PlacesTableSeeder extends Seeder
      …
      

      We can now iterate through the $places array values using a foreach loop, and insert each one in our places table using the query builder:

      database/seeds/PlacesTableSeeder.php

      …
          public function run()
          {
              foreach (self::$places as $place) {
                  DB::table('places')->insert([
                      'name' => $place,
                      'visited' => rand(0,1) == 1
                  ]);
              }
          }
      
      

      The foreach loop iterates through each value of the $places static array. At each iteration, we use the DB facade to insert a new row at the places table. We set the name field to the name of the place we just obtained from the $places array, and we set the visited field to a random value of either 0 or 1.

      This is what the full PlacesTableSeeder class will look like after all the updates:

      database/seeds/PlacesTableSeeder.php

      <?php
      
      use IlluminateDatabaseSeeder;
      use IlluminateSupportFacadesDB;
      
      class PlacesTableSeeder extends Seeder
      {
          static $places = [
              'Berlin',
              'Budapest',
              'Cincinnati',
              'Denver',
              'Helsinki',
              'Lisbon',
              'Moscow',
              'Nairobi',
              'Oslo',
              'Rio',
              'Tokyo'
          ];
      
          /**
           * Run the database seeds.
           *
           * @return void
           */
          public function run()
          {
              foreach (self::$places as $place) {
                  DB::table('places')->insert([
                      'name' => $place,
                      'visited' => rand(0,1) == 1
                  ]);
              }
          }
      }
      

      Save and close the file when you’re done making these changes.

      Seeder classes aren’t automatically loaded in the application. We need to edit the main DatabaseSeeder class to include a call to the seeder we’ve just created.

      Open the database/seeds/DatabaseSeeder.php file using nano or your favorite editor:

      • nano database/seeds/DatabaseSeeder.php

      The DatabaseSeeder class looks like any other seeder: it extends from the Seeder class and has a run method. We’ll update this method to include a call to PlacesTableSeeder.

      Update the current run method inside your DatabaseSeeder class by deleting the commented-out line and replacing it with the following highlighted code:

      database/seeds/DatabaseSeeder.php

      …
          public function run()
          {
              $this->call(PlacesTableSeeder::class);
          }
      ...
      

      This is how the full DatabaseSeeder class will look like after the update:

      database/seeds/DatabaseSeeder.php

      <?php
      
      use IlluminateDatabaseSeeder;
      
      class DatabaseSeeder extends Seeder
      {
          /**
           * Seed the application's database.
           *
           * @return void
           */
          public function run()
          {
              $this->call(PlacesTableSeeder::class);
          }
      }
      
      
      

      Save and close the file when you’re done updating its content.

      We have now finished setting up both a migration and a seeder for our places table. In the next step, we’ll see how to execute them.

      Step 6 — Running Database Migrations and Seeders

      Before proceeding, we need to make sure your application is up and running. We’ll set up the application encryption key and then access the application from a browser to test the web server.

      To generate the encryption key required by Laravel, you can use the artisan key:generate command:

      • docker-compose exec app php artisan key:generate

      Once the key has been generated, you’ll be able to access the application by pointing your browser to your server hostname or IP address on port 8000:

      http://server_host_or_ip:8000
      

      You’ll see a page like this:

      MySQL error

      That means the application is able to connect to the database, but it couldn’t find a table called places. We’ll create the places table now, using the following migrate artisan command:

      • docker-compose exec app php artisan migrate

      You’ll get output similar to this:

      Output

      Migration table created successfully. Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (0.06 seconds) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (0.06 seconds) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (0.03 seconds) Migrating: 2020_02_10_144134_create_places_table Migrated: 2020_02_10_144134_create_places_table (0.03 seconds)

      You’ll notice that a few other migrations were executed along with the create_places_table migration we’ve set up. These migrations are auto generated when Laravel is installed. Although we won’t be using these additional tables now, they will be needed in the future when we expand the application to have registered users and scheduled jobs. For now, you can just leave them as is.

      At this point our table is still empty. We need to run the db:seed command to seed the database with our sample places:

      • docker-compose exec app php artisan db:seed

      This will run our seeder and insert the sample values we defined within our PlacesTableSeeder class. You’ll see output similar to this:

      Output

      Seeding: PlacesTableSeeder Seeded: PlacesTableSeeder (0.06 seconds) Database seeding completed successfully.

      Now, reload the application page on your browser. You’ll see a page similar to this:

      Demo Laravel Application

      Whenever you need to start from scratch, you can drop all your database tables with:

      • docker-compose exec app php artisan db:wipe

      Output

      Dropped all tables successfully.

      To run the app migrations and seed the tables in a single command, you can use:

      • docker-compose exec app php artisan migrate --seed

      Output

      Migration table created successfully. Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (0.06 seconds) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (0.07 seconds) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (0.03 seconds) Migrating: 2020_02_10_144134_create_places_table Migrated: 2020_02_10_144134_create_places_table (0.03 seconds) Seeding: PlacesTableSeeder Seeded: PlacesTableSeeder (0.06 seconds) Database seeding completed successfully.

      If you want to roll back a migration, you can run:

      • docker-compose exec app php artisan migrate:rollback

      This will trigger the down method for each migration class inside the migrations folder. Typically, it will remove all the tables that were created through migration classes, leaving alone any other tables that might have been manually created. You’ll see output like this:

      Output

      Rolling back: 2020_02_10_144134_create_places_table Rolled back: 2020_02_10_144134_create_places_table (0.02 seconds) Rolling back: 2019_08_19_000000_create_failed_jobs_table Rolled back: 2019_08_19_000000_create_failed_jobs_table (0.02 seconds) Rolling back: 2014_10_12_100000_create_password_resets_table Rolled back: 2014_10_12_100000_create_password_resets_table (0.02 seconds) Rolling back: 2014_10_12_000000_create_users_table Rolled back: 2014_10_12_000000_create_users_table (0.02 seconds)

      The rollback command is especially useful when you’re making changes to application models and a db:wipe command can’t be used - for instance, if multiple systems depend on the same database.

      Conclusion

      In this guide, we’ve seen how to use database migrations and seeders to facilitate setting up development and testing databases for a Laravel 6 application.

      As a next step, you might want to check the Laravel documentation for more details on how to use the query builder, and how to use Eloquent models to abstract your application’s database schema even further.



      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