MySQL Triggers for Automation

Introduction

In this lab, you will learn how to use MySQL triggers to automate database actions. Triggers are stored programs that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE operations.

You will create a products table to store item information and a product_logs table to audit changes. You will then implement a trigger that automatically adds a log entry to product_logs every time a new product is inserted into the products table. The lab will also guide you through testing, updating, and removing the trigger.

Set Up the Database and Tables

Before creating a trigger, you need a database and the necessary tables. In this step, you will create a database named mydatabase and two tables: products to store product data and product_logs to store audit logs.

First, open the terminal from your desktop.

Connect to the MySQL server as the root user. Since this is a lab environment, you can use sudo to connect without a password.

sudo mysql -u root

Once connected, you will see the MySQL prompt (mysql>).

Now, create the mydatabase database and switch to it. The IF NOT EXISTS clause prevents an error if the database already exists.

CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

Next, create the products table. This table will store the ID, name, and price of each product.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Now, create the product_logs table. This table will store a log of every new product added.

CREATE TABLE product_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    action_description VARCHAR(255),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

You can verify that both tables were created successfully by running:

SHOW TABLES;

You should see the products and product_logs tables listed in the output.

+--------------------+
| Tables_in_mydatabase |
+--------------------+
| product_logs       |
| products           |
+--------------------+
2 rows in set (0.00 sec)

Now that the database and tables are ready, you can continue to the next step.

Create and Test a Trigger

With the tables in place, you can now create a trigger. This trigger will activate after a new row is inserted into the products table and will add a corresponding entry to the product_logs table.

Switch to your database.

USE mydatabase;

Now, create the trigger. The DELIMITER command is used to change the standard delimiter (;) to // so that the semicolon inside the trigger body is not interpreted as the end of the CREATE TRIGGER statement.

DELIMITER //

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, action_description)
    VALUES (NEW.id, CONCAT('New product added: ', NEW.name));
END//

DELIMITER ;

Let's break down this statement:

• CREATE TRIGGER products_after_insert: Defines a new trigger with the name products_after_insert.
• AFTER INSERT ON products: Specifies that the trigger will fire after an INSERT event on the products table.
• FOR EACH ROW: Indicates that the trigger's logic will execute for every row that is inserted.
• BEGIN...END: Encloses the trigger's SQL statements.
• NEW.id, NEW.name: The NEW keyword refers to the row that was just inserted. NEW.id is the id of the new product, and NEW.name is its name.

Now, let's test the trigger by inserting a new product.

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);

This INSERT operation should have activated the trigger. To confirm this, check the contents of the product_logs table.

SELECT * FROM product_logs;

You should see one record in the product_logs table, which was automatically created by the trigger. The output will look similar to this (the log_id and log_time will vary):

+--------+------------+-----------------------------+---------------------+
| log_id | product_id | action_description          | log_time            |
+--------+------------+-----------------------------+---------------------+
|      1 |          1 | New product added: Laptop   | 2024-05-27 10:30:00 |
+--------+------------+-----------------------------+---------------------+
1 row in set (0.00 sec)

This confirms your trigger is working correctly.

Update the Trigger Logic

Your requirements may change over time. For instance, you might want to log more details. In this step, you will update the trigger to also log the price of the new product.

In MySQL, you cannot directly modify a trigger. You must first drop the existing trigger and then create a new one with the updated logic.

Switch to your database.

USE mydatabase;

First, drop the existing trigger. The IF EXISTS clause prevents an error if the trigger has already been removed.

DROP TRIGGER IF EXISTS products_after_insert;

Now, recreate the trigger with the updated logic. This new version will include the product's price in the action_description.

DELIMITER //

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, action_description)
    VALUES (NEW.id, CONCAT('New product added: ', NEW.name, ' with price ', NEW.price));
END//

DELIMITER ;

To test the updated trigger, insert another product.

INSERT INTO products (name, price) VALUES ('Mouse', 25.00);

Now, query the product_logs table to see all the log entries.

SELECT * FROM product_logs;

The output should now show two log entries. The second entry, for the 'Mouse', should include the price in its description, confirming that your updated trigger is working.

+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description                           | log_time            |
+--------+------------+----------------------------------------------+---------------------+
|      1 |          1 | New product added: Laptop                    | 2024-05-27 10:30:00 |
|      2 |          2 | New product added: Mouse with price 25.00    | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

You have successfully updated the trigger.

Drop the Trigger

If a trigger is no longer needed, you should remove it to avoid unnecessary processing and potential maintenance issues. In this final step, you will drop the products_after_insert trigger.

Switch to your database.

USE mydatabase;

To drop the trigger, use the DROP TRIGGER statement.

DROP TRIGGER IF EXISTS products_after_insert;

You can verify that the trigger has been removed by running the SHOW TRIGGERS command.

SHOW TRIGGERS;

This command should produce an empty set, confirming the trigger is gone.

Empty set (0.00 sec)

To be absolutely sure, insert one more product.

INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);

Now, check the product_logs table.

SELECT * FROM product_logs;

The table should still contain only the first two log entries. The 'Keyboard' product should not have a log entry, because the trigger was dropped before the insert.

+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description                           | log_time            |
+--------+------------+----------------------------------------------+---------------------+
|      1 |          1 | New product added: Laptop                    | 2024-05-27 10:30:00 |
|      2 |          2 | New product added: Mouse with price 25.00    | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

This confirms that the trigger has been successfully removed. You can now exit the MySQL shell.

exit;

Summary

In this lab, you have learned the fundamentals of using MySQL triggers for automation. You successfully set up a database environment, created tables, and implemented a trigger to log data insertion events. You also practiced the complete lifecycle of a trigger, including creating, testing, updating, and dropping it.

These skills are essential for tasks such as creating audit trails, enforcing complex business rules, and maintaining data integrity automatically within your database.