One place for hosting & domains

      Database

      How To Manage a Redis Database eBook


      Download the Complete eBook!

      How To Manage a Redis Database eBook in EPUB format

      How To Manage a Redis Database eBook in PDF format

      Introduction to the eBook

      This book aims to provide an approachable introduction to Redis concepts by outlining many of the key-value store’s commands so readers can learn their patterns and syntax, thus building up readers’ understanding gradually. The goal for this book is to serve as an introduction to Redis for those interested in getting started with it, or key-value stores in general. For more experienced users, this book can function as a collection of helpful cheat sheets and in-depth reference.

      This book is based on the How To Manage a Redis Database tutorial series found on DigitalOcean Community. The topics that it covers include how to:

      1. Connect to a Redis database

      2. Create and use a variety of Redis data types, including strings, sets, hashes, and lists

      3. Manage Redis clients and replicas

      4. Run transactions in Redis

      5. Troubleshoot issues in a Redis installation

      Each chapter is self-contained and can be followed independently of the others. By reading through this book, you’ll become acquainted with many of Redis’s most widely used commands, which will help you as you begin to build applications that take advantage of its power and speed.

      Download the eBook

      You can download the eBook in either the EPUB or PDF format by following the links below.

      Download the Complete eBook!

      How To Manage a Redis Database eBook in EPUB format

      How To Manage a Redis Database eBook in PDF format

      If you’d like to learn more about how to use Redis, visit the DigitalOcean Community’s Redis section. Alternatively, if you want to learn about other open-source database management systems, like MySQL, PostgreSQL, MariaDB, or MongoDB, we encourage you to check out our full library of database-related content.



      Source link

      How To Use One-to-Many Database Relationships with Flask and SQLite


      The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      Flask is a framework for building web applications using the Python language, and SQLite is a database engine that can be used with Python to store application data. In this tutorial, you will use Flask with SQLite to create a to-do application where users can create lists of to-do items. You will learn how to use SQLite with Flask and how one-to-many database relationships work.

      A one-to-many database relationship is a relationship between two database tables where a record in one table can reference several records in another table. For example, in a blogging application, a table for storing posts can have a one-to-many relationship with a table for storing comments. Each post can reference many comments, and each comment references a single post; therefore, one post has a relationship with many comments. The post table is a parent table, while the comments table is a child table—a record in the parent table can reference many records in the child table. This is important to be able to have access to related data in each table.

      We’ll use SQLite because it is portable and does not need any additional set up to work with Python. It is also great for prototyping an application before moving to a larger database such as MySQL or Postgres. For more on how to choose the right database system read our SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems article.

      Prerequisites

      Before you start following this guide, you will need:

      Step 1 — Creating the Database

      In this step, you will activate your programming environment, install Flask, create the SQLite database, and populate it with sample data. You’ll learn how to use foreign keys to create a one-to-many relationship between lists and items. A foreign key is a key used to associate a database table with another table, it is the link between the child table and its parent table.

      If you haven’t already activated your programming environment, make sure you’re in your project directory (flask_todo) and use this command to activate it:

      Once your programming environment is activated, install Flask using the following command:

      Once the installation is complete, you can now create the database schema file that contains SQL commands to create the tables you need to store your to-do data. You will need two tables: a table called lists to store to-do lists, and an items table to store the items of each list.

      Open a file called schema.sql inside your flask_todo directory:

      Type the following SQL commands inside this file:

      flask_todo/schema.sql

      DROP TABLE IF EXISTS lists;
      DROP TABLE IF EXISTS items;
      
      CREATE TABLE lists (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          title TEXT NOT NULL
      );
      
      CREATE TABLE items (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          list_id INTEGER NOT NULL,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          content TEXT NOT NULL,
          FOREIGN KEY (list_id) REFERENCES lists (id)
      );
      

      Save and close the file.

      The first two SQL command are DROP TABLE IF EXISTS lists; and DROP TABLE IF EXISTS items;, these delete any already existing tables named lists and items so you don’t see confusing behavior. Note that this will delete all of the content you have in the database whenever you use these SQL commands, so ensure you don’t write any important content in the web application until you finish this tutorial and experiment with the final result.

      Next, you use CREATE TABLE lists to create the lists table that will store the to-do lists (such as a study list, work list, home list, and so on) with the following columns:

      • id: An integer that represents a primary key, this will get assigned a unique value by the database for each entry (i.e. to-do list).
      • created: The time the to-do list was created at. NOT NULL signifies that this column should not be empty and the DEFAULT value is the CURRENT_TIMESTAMP value, which is the time at which the list was added to the database. Just like id, you don’t need to specify a value for this column, as it will be automatically filled in.
      • title: The list title.

      Then, you create a table called items to store to-do items. This table has an ID, a list_id integer column to identify which list an item belongs to, a creation date, and the item’s content. To link an item to a list in the database you use a foreign key constraint with the line FOREIGN KEY (list_id) REFERENCES lists (id). Here the lists table is a parent table, which is the table that is being referenced by the foreign key constraint, this indicates a list can have multiple items. The items table is a child table, which is the table the constraint applies to. This means items belong to a single list. The list_id column references the id column of the lists parent table.

      Since a list can have many items, and an item belongs to only one list, the relationship between the lists and items tables is a one-to-many relationship.

      Next, you will use the schema.sql file to create the database. Open a file named init_db.py inside the flask_todo directory:

      Then add the following code:

      flask_todo/init_db.py

      import sqlite3
      
      connection = sqlite3.connect('database.db')
      
      
      with open('schema.sql') as f:
          connection.executescript(f.read())
      
      cur = connection.cursor()
      
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Work',))
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Home',))
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Study',))
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (1, 'Morning meeting')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (2, 'Buy fruit')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (2, 'Cook dinner')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (3, 'Learn Flask')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (3, 'Learn SQLite')
                  )
      
      connection.commit()
      connection.close()
      

      Save and close the file.

      Here you connect to a file called database.db that will be created once you execute this program. You then open the schema.sql file and run it using the executescript() method that executes multiple SQL statements at once.

      Running schema.sql will create the lists and items tables. Next, using a Cursor object, you execute a few INSERT SQL statements to create three lists and five to-do items.

      You use the list_id column to link each item to a list via the list’s id value. For example, the Work list was the first insertion into the database, so it will have the ID 1. This is how you can link the Morning meeting to-do item to Work—the same rule applies to the other lists and items.

      Finally, you commit the changes and close the connection.

      Run the program:

      After execution, a new file called database.db will appear in your flask_todo directory.

      You’ve activated your environment, installed Flask, and created the SQLite database. Next, you’ll retrieve the lists and items from the database and display them in the application’s homepage.

      Step 2 — Displaying To-do Items

      In this step, you will connect the database you created in the previous step to a Flask application that displays the to-do lists and the items of each list. You will learn how to use SQLite joins to query data from two tables and how to group to-do items by their lists.

      First, you will create the application file. Open a file named app.py inside the flask_todo directory:

      And then add the following code to the file:

      flask_todo/app.py

      from itertools import groupby
      import sqlite3
      from flask import Flask, render_template, request, flash, redirect, url_for
      
      
      def get_db_connection():
          conn = sqlite3.connect('database.db')
          conn.row_factory = sqlite3.Row
          return conn
      
      
      app = Flask(__name__)
      app.config['SECRET_KEY'] = 'this should be a secret random string'
      
      
      @app.route('/')
      def index():
          conn = get_db_connection()
          todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l 
                                ON i.list_id = l.id ORDER BY l.title;').fetchall()
      
          lists = {}
      
          for k, g in groupby(todos, key=lambda t: t['title']):
              lists[k] = list(g)
      
          conn.close()
          return render_template('index.html', lists=lists)
      

      Save and close the file.

      The get_db_connection() function opens a connection to the database.db database file and then sets the row_factory attribute to sqlite3.Row. In this way you can have name-based access to columns; this means that the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn connection object you’ll be using to access the database.

      In the index() view function, you open a database connection and execute the following SQL query:

      SELECT i.content, l.title FROM items i JOIN lists l ON i.list_id = l.id ORDER BY l.title;
      

      You then retrieve its results by using the fetchall() method and save the data in a variable called todos.

      In this query, you use SELECT to get the content of the item and the title of the list it belongs to by joining both the items and lists tables (with the table aliases i for items and l for lists). With the join condition i.list_id = l.id after the ON keyword, you will get each row from the items table with every row from the lists table where the list_id column of the items table matches the id of the lists table. You then use ORDER BY to order the results by list titles.

      To understand this query better, open the Python REPL in your flask_todo directory:

      To understand the SQL query, examine the contents of the todos variable by running this small program:

      • from app import get_db_connection
      • conn = get_db_connection()
      • todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l
      • ON i.list_id = l.id ORDER BY l.title;').fetchall()
      • for todo in todos:
      • print(todo['title'], ':', todo['content'])

      You first import the get_db_connection from the app.py file then open a connection and execute the query (note that this is the same SQL query you have in your app.py file). In the for loop you print the title of the list and the content of each to-do item.

      The output will be as follows:

      Output

      Home : Buy fruit Home : Cook dinner Study : Learn Flask Study : Learn SQLite Work : Morning meeting

      Close the REPL using CTRL + D.

      Now that you understand how SQL joins work and what the query achieves, let’s return back to the index() view function in your app.py file. After declaring the todos variable, you group the results using the following code:

      lists = {}
      
      for k, g in groupby(todos, key=lambda t: t['title']):
          lists[k] = list(g)
      

      You first declare an empty dictionary called lists, then use a for loop to go through a grouping of the results in the todos variable by the list’s title. You use the groupby() function you imported from the itertools standard library. This function will go through each item in the todos variable and generate a group of results for each key in the for loop.

      k represents list titles (that is, Home, Study, Work), which are extracted using the function you pass to the key parameter of the groupby() function. In this case the function is lambda t: t['title'] that takes a to-do item and returns the title of the list (as you have done before with todo['title'] in the previous for loop). g represents the group that contains the to-do items of each list title. For example, in the first iteration, k will be 'Home', while g is an iterable that will contain the items 'Buy fruit' and 'Cook dinner'.

      This gives us a representation of the one-to-many relationship between lists and items, where each list title has several to-do items.

      When running the app.py file, and after the for loop finishes execution, lists will be as follows:

      Output

      {'Home': [<sqlite3.Row object at 0x7f9f58460950>, <sqlite3.Row object at 0x7f9f58460c30>], 'Study': [<sqlite3.Row object at 0x7f9f58460b70>, <sqlite3.Row object at 0x7f9f58460b50>], 'Work': [<sqlite3.Row object at 0x7f9f58460890>]}

      Each sqlite3.Row object will contain the data you retrieved from the items table using the SQL query in the index() function. To represent this data better, let’s make a program that goes through the lists dictionary and displays each list and its items.

      Open a file called list_example.py in your flask_todo directory:

      Then add the following code:

      flask_todo/list_example.py

      
      from itertools import groupby
      from app import get_db_connection
      
      conn = get_db_connection()
      todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l 
                              ON i.list_id = l.id ORDER BY l.title;').fetchall()
      
      lists = {}
      
      for k, g in groupby(todos, key=lambda t: t['title']):
          lists[k] = list(g)
      
      for list_, items in lists.items():
          print(list_)
          for item in items:
              print('    ', item['content'])
      

      Save and close the file.

      This is very similar to the content in your index() view function. The last for loop here illustrates how the lists dictionary is structured. You first go through the dictionary’s items, print the list title (which is in the list_ variable), then go through each group of to-do items that belong to the list and print the content value of the item.

      Run the list_example.py program:

      Here is the output of list_example.py:

      Output

      Home Buy fruit Cook dinner Study Learn Flask Learn SQLite Work Morning meeting

      Now that you understand each part of the index() function, let’s create a base template and create the index.html file you rendered using the line return render_template('index.html', lists=lists).

      In your flask_todo directory, create a templates directory and open a file called base.html inside it:

      • mkdir templates
      • nano templates/base.html

      Add the following code inside base.html, note that you’re using Bootstrap here. If you are not familiar with HTML templates in Flask, see Step 3 of How To Make a Web Application Using Flask in Python 3:

      flask_todo/templates/base.html

      <!doctype html>
      <html lang="en">
        <head>
          <!-- Required meta tags -->
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
      
          <!-- Bootstrap CSS -->
          <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
      
          <title>{% block title %} {% endblock %}</title>
        </head>
        <body>
          <nav class="navbar navbar-expand-md navbar-light bg-light">
              <a class="navbar-brand" href="https://www.digitalocean.com/{{ url_for("index')}}">FlaskTodo</a>
              <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
                  <span class="navbar-toggler-icon"></span>
              </button>
              <div class="collapse navbar-collapse" id="navbarNav">
                  <ul class="navbar-nav">
                  <li class="nav-item active">
                      <a class="nav-link" href="https://www.digitalocean.com/#">About</a>
                  </li>
                  </ul>
              </div>
          </nav>
          <div class="container">
              {% block content %} {% endblock %}
          </div>
      
          <!-- Optional JavaScript -->
          <!-- jQuery first, then Popper.js, then Bootstrap JS -->
          <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
          <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
          <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        </body>
      </html>
      

      Save and close the file.

      Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta> tags provide information for the web browser, the <link> tag links the Bootstrap CSS files, and the <script> tags are links to JavaScript code that allows some additional Bootstrap features. Check out the Bootstrap documentation for more information.

      Next, create the index.html file that will extend this base.html file:

      • nano templates/index.html

      Add the following code to index.html:

      flask_todo/templates/index.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Welcome to FlaskTodo {% endblock %}</h1>
          {% for list, items in lists.items() %}
              <div class="card" style="width: 18rem; margin-bottom: 50px;">
                  <div class="card-header">
                      <h3>{{ list }}</h3>
                  </div>
                  <ul class="list-group list-group-flush">
                      {% for item in items %}
                          <li class="list-group-item">{{ item['content'] }}</li>
                      {% endfor %}
                  </ul>
              </div>
          {% endfor %}
      {% endblock %}
      

      Here you use a for loop to go through each item of the lists dictionary, you display the list title as a card header inside an <h3> tag, and then use a list group to display each to-do item that belongs to the list in an <li> tag. This follows the same rules explained in the list_example.py program.

      You will now set the environment variables Flask needs and run the application using the following commands:

      • export FLASK_APP=app
      • export FLASK_ENV=development
      • flask run

      Once the development server is running, you can visit the URL http://127.0.0.1:5000/ in your browser. You will see a web page with the “Welcome to FlaskTodo” and your list items.

      Home Page

      You can now type CTRL + C to stop your development server.

      You’ve created a Flask application that displays the to-do lists and the items of each list. In the next step, you will add a new page for creating new to-do items.

      Step 3 — Adding New To-do Items

      In this step, you will make a new route for creating to-do items, you will insert data into database tables, and associate items with the lists they belong to.

      First, open the app.py file:

      Then, add a new /create route with a view function called create() at the end of the file:

      flask_todo/app.py

      ...
      @app.route('/create/', methods=('GET', 'POST'))
      def create():
          conn = get_db_connection()
          lists = conn.execute('SELECT title FROM lists;').fetchall()
      
          conn.close()
          return render_template('create.html', lists=lists)
      

      Save and close the file.

      Because you will use this route to insert new data to the database via a web form, you allow both GET and POST requests using methods=('GET', 'POST') in the app.route() decorator. In the create() view function, you open a database connection then get all the list titles available in the database, close the connection, and render a create.html template passing it the list titles.

      Next, open a new template file called create.html:

      • nano templates/create.html

      Add the following HTML code to create.html:

      flask_todo/templates/create.html

      {% extends 'base.html' %}
      
      {% block content %}
      <h1>{% block title %} Create a New Item {% endblock %}</h1>
      
      <form method="post">
          <div class="form-group">
              <label for="content">Content</label>
              <input type="text" name="content"
                     placeholder="Todo content" class="form-control"
                     value="{{ request.form['content'] }}"></input>
          </div>
      
          <div class="form-group">
              <label for="list">List</label>
              <select class="form-control" name="list">
                  {% for list in lists %}
                      {% if list['title'] == request.form['list'] %}
                          <option value="{{ request.form['list'] }}" selected>
                              {{ request.form['list'] }}
                          </option>
                      {% else %}
                          <option value="{{ list['title'] }}">
                              {{ list['title'] }}
                          </option>
                      {% endif %}
                  {% endfor %}
              </select>
          </div>
          <div class="form-group">
              <button type="submit" class="btn btn-primary">Submit</button>
          </div>
      </form>
      {% endblock %}
      

      Save and close the file.

      You use request.form to access the form data that is stored in case something goes wrong with your form submission (for example, if no to-do content was provided). In the <select> element, you loop through the lists you retrieved from the database in the create() function. If the list title is equal to what is stored in request.form then the selected option is that list title, otherwise, you display the list title in a normal non-selected <option> tag.

      Now, in the terminal, run your Flask application:

      Then visit http://127.0.0.1:5000/create in your browser, you will see a form for creating a new to-do item, note that the form doesn’t work yet because you have no code to handle POST requests that get sent by the browser when submitting the form.

      Type CTRL + C to stop your development server.

      Next, let’s add the code for handling POST requests to the create() function and make the form function properly, open app.py:

      Then edit the create() function to look like so:

      flask_todo/app.py

      ...
      @app.route('/create/', methods=('GET', 'POST'))
      def create():
          conn = get_db_connection()
      
          if request.method == 'POST':
              content = request.form['content']
              list_title = request.form['list']
      
              if not content:
                  flash('Content is required!')
                  return redirect(url_for('index'))
      
              list_id = conn.execute('SELECT id FROM lists WHERE title = (?);',
                                       (list_title,)).fetchone()['id']
              conn.execute('INSERT INTO items (content, list_id) VALUES (?, ?)',
                           (content, list_id))
              conn.commit()
              conn.close()
              return redirect(url_for('index'))
      
          lists = conn.execute('SELECT title FROM lists;').fetchall()
      
          conn.close()
          return render_template('create.html', lists=lists)
      

      Save and close the file.

      Inside the request.method == 'POST' condition you get the to-do item’s content and the list’s title from the form data. If no content was submitted, you send the user a message using the flash() function and redirect to the index page. If this condition was not triggered, then you execute a SELECT statement to get the list ID from the provided list title and save it in a variable called list_id. You then execute an INSERT INTO statement to insert the new to-do item into the items table. You use the list_id variable to link the item to the list it belongs to. Finally, you commit the transaction, close the connection, and redirect to the index page.

      As a last step, you will add a link to /create in the navigation bar and display flashed messages below it, to do this, open base.html:

      Edit the file by adding a new <li> navigation item that links to the create() view function. Then display the flashed messages using a for loop above the content block. These are available in the get_flashed_messages() Flask function:

      flask_todo/templates/base.html

      <nav class="navbar navbar-expand-md navbar-light bg-light">
          <a class="navbar-brand" href="https://www.digitalocean.com/{{ url_for("index')}}">FlaskTodo</a>
          <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
              <span class="navbar-toggler-icon"></span>
          </button>
          <div class="collapse navbar-collapse" id="navbarNav">
              <ul class="navbar-nav">
              <li class="nav-item active">
                  <a class="nav-link" href="https://www.digitalocean.com/{{ url_for("create') }}">New</a>
              </li>
      
              <li class="nav-item active">
                  <a class="nav-link" href="https://www.digitalocean.com/#">About</a>
              </li>
              </ul>
          </div>
      </nav>
      <div class="container">
          {% for message in get_flashed_messages() %}
              <div class="alert alert-danger">{{ message }}</div>
          {% endfor %}
          {% block content %} {% endblock %}
      </div>
      

      Save and close the file.

      Now, in the terminal, run your Flask application:

      A new link to /create will appear in the navigation bar. If you navigate to this page and try to add a new to-do item with no content, you’ll receive a flashed message saying Content is required!. If you fill in the content form, a new to-do item will appear on the index page.

      In this step, you have added the ability to create new to-do items and save them to the database.

      You can find the source code for this project in this repository.

      Conclusion

      You now have an application to manage to-do lists and items. Each list has several to-do items and each to-do item belongs to a single list in a one-to-many relationship. You learned how to use Flask and SQLite to manage multiple related database tables, how to use foreign keys and how to retrieve and display related data from two tables in a web application using SQLite joins.

      Furthermore, you grouped results using the groupby() function, inserted new data to the database, and associated database table rows with the tables they are related to. You can learn more about foreign keys and database relationships from the SQLite documentation.

      You can also read more of our Python Framework content. If you want to check out the sqlite3 Python module, read our tutorial on How To Use the sqlite3 Module in Python 3.



      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