MySQL-Triggers für Automatisierung

Introduction

In this lab, you will learn how to automate tasks in MySQL using triggers. The lab focuses on creating a trigger that logs insert operations on a products table into a separate product_logs table.

The lab guides you through connecting to the MySQL server, creating the mydatabase database, and defining the products and product_logs tables. You will then create a trigger named products_after_insert that automatically inserts a record into the product_logs table whenever a new product is added to the products table. Subsequent steps involve testing the trigger, updating its logic, and finally, dropping the trigger.

Create a Trigger to Log Inserts

In this step, you will learn how to create a trigger in MySQL that logs insert operations into a separate table. Triggers are special stored programs that automatically execute in response to certain events on a table, such as INSERT, UPDATE, or DELETE. This is useful for auditing changes, enforcing business rules, or performing other actions based on data modifications.

First, let's connect to the MySQL server. Open a terminal and execute the following command:

mysql -u root

You will be prompted for the root password. Enter the password and press Enter.

Next, create a database named mydatabase if it doesn't already exist:

CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

Now, let's create a table named products:

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

We also need a table to log the insert operations. Let's create a table named product_logs:

CREATE TABLE product_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    product_name VARCHAR(255),
    product_price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now, let's create the trigger. The trigger will be executed after each insert operation on the products table. The trigger will insert a record into the product_logs table with the details of the inserted product.

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, product_name, product_price)
    VALUES (NEW.id, NEW.name, NEW.price);
END;

Let's break down the trigger definition:

• CREATE TRIGGER products_after_insert: This statement creates a trigger named products_after_insert.
• AFTER INSERT ON products: This specifies that the trigger will be executed after each insert operation on the products table.
• FOR EACH ROW: This indicates that the trigger will be executed for each row that is inserted into the products table.
• BEGIN ... END: This block contains the SQL statements that will be executed when the trigger is activated.
• NEW.id, NEW.name, NEW.price: These refer to the values of the id, name, and price columns of the newly inserted row.

Now, let's insert some data into the products table to test the trigger:

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

Finally, let's check the product_logs table to see if the trigger has inserted the log records:

SELECT * FROM product_logs;

You should see two records in the product_logs table, corresponding to the two insert operations we performed on the products table.

+--------+------------+--------------+---------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          |
+--------+------------+--------------+---------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 |
+--------+------------+--------------+---------------+---------------------+
2 rows in set (0.00 sec)

Insert Data to Test the Trigger

In this step, you will insert data into the products table, which will trigger the products_after_insert trigger you created in the previous step. This will allow you to verify that the trigger is working correctly and that it is inserting the expected data into the product_logs table.

First, ensure you are connected to the MySQL server and using the mydatabase database. If you are not already connected, open a terminal and execute the following commands:

mysql -u root

Enter the password when prompted.

USE mydatabase;

Now, let's insert some data into the products table. We'll insert three new products with different names and prices:

INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);
INSERT INTO products (name, price) VALUES ('Monitor', 300.00);
INSERT INTO products (name, price) VALUES ('Headphones', 100.00);

These INSERT statements will add three new rows to the products table. Because of the trigger we created, each of these inserts should also result in a new row being added to the product_logs table.

To verify that the trigger is working correctly, let's query the product_logs table and check if the new records have been inserted:

SELECT * FROM product_logs;

You should see the records corresponding to the newly inserted products in the product_logs table. The output should look similar to this (the log_id and created_at values will be different):

+--------+------------+--------------+---------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          |
+--------+------------+--------------+---------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 |
|      3 |          3 | Keyboard     |         75.00 | 2024-10-27 12:05:00 |
|      4 |          4 | Monitor      |        300.00 | 2024-10-27 12:05:00 |
|      5 |          5 | Headphones   |        100.00 | 2024-10-27 12:05:00 |
+--------+------------+--------------+---------------+---------------------+
5 rows in set (0.00 sec)

If you see the new records in the product_logs table, it means the trigger is working correctly. The product_id should match the id of the corresponding product in the products table, and the product_name and product_price should also match the values you inserted.

Update the Trigger Logic

In this step, you will modify the existing trigger products_after_insert to include additional information in the product_logs table. Specifically, you will add the current timestamp to a new column named operation_time in the product_logs table.

First, you need to add the operation_time column to the product_logs table. Connect to the MySQL server and use the mydatabase database:

mysql -u root

Enter the password when prompted.

USE mydatabase;

Now, add the operation_time column to the product_logs table:

ALTER TABLE product_logs ADD COLUMN operation_time TIMESTAMP;

Next, you need to drop the existing trigger products_after_insert before you can recreate it with the updated logic:

DROP TRIGGER IF EXISTS products_after_insert;

Now, recreate the trigger with the updated logic to include the operation_time. We will set the operation_time to the current timestamp when the trigger is executed:

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, product_name, product_price, operation_time)
    VALUES (NEW.id, NEW.name, NEW.price, CURRENT_TIMESTAMP);
END;

In this updated trigger:

• We added operation_time to the product_logs table's column list in the INSERT statement.
• We set the value of operation_time to CURRENT_TIMESTAMP, which will insert the current date and time when the trigger is executed.

Now, let's insert some new data into the products table to test the updated trigger:

INSERT INTO products (name, price) VALUES ('Printer', 200.00);

Finally, let's check the product_logs table to see if the trigger has inserted the log record with the operation_time:

SELECT * FROM product_logs;

You should see a new record in the product_logs table with the operation_time column populated with the current timestamp. The output should look similar to this (the log_id and created_at and operation_time values will be different):

+--------+------------+--------------+---------------+---------------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          | operation_time      |
+--------+------------+--------------+---------------+---------------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 | NULL                |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 | NULL                |
|      3 |          3 | Keyboard     |         75.00 | 2024-10-27 12:05:00 | NULL                |
|      4 |          4 | Monitor      |        300.00 | 2024-10-27 12:05:00 | NULL                |
|      5 |          5 | Headphones   |        100.00 | 2024-10-27 12:05:00 | NULL                |
|      6 |          6 | Printer      |        200.00 | 2024-10-27 12:10:00 | 2024-10-27 12:10:00 |
+--------+------------+--------------+---------------+---------------------+---------------------+
6 rows in set (0.00 sec)

Note that the operation_time for the new entry is populated, while the old entries are NULL because they were inserted before the column was added and the trigger was updated.

Drop the Trigger

In this step, you will drop the trigger products_after_insert that you created and updated in the previous steps. Dropping a trigger removes it from the database, so it will no longer be executed when the associated event occurs.

First, ensure you are connected to the MySQL server and using the mydatabase database. If you are not already connected, open a terminal and execute the following commands:

mysql -u root

Enter the password when prompted.

USE mydatabase;

To drop the trigger, use the following SQL command:

DROP TRIGGER IF EXISTS products_after_insert;

The DROP TRIGGER statement removes the specified trigger from the database. The IF EXISTS clause ensures that the command will not return an error if the trigger does not exist.

To verify that the trigger has been dropped, you can use the SHOW TRIGGERS command:

SHOW TRIGGERS LIKE 'products_after_insert';

If the trigger has been successfully dropped, the SHOW TRIGGERS command will not return any results.

Let's insert one more record into the products table to confirm that the trigger is indeed dropped and no longer logging new inserts:

INSERT INTO products (name, price) VALUES ('Webcam', 50.00);

Now, let's check the product_logs table. If the trigger has been successfully dropped, the new insert should not be logged.

SELECT * FROM product_logs;

You should not see a new record for 'Webcam' in the product_logs table. This confirms that the trigger has been successfully dropped.

Summary

In this lab, you learned how to create a MySQL trigger that automatically logs insert operations into a separate table. This involved connecting to the MySQL server, creating a database and two tables: products and product_logs. The products table stores product information, while product_logs records details of each inserted product.

The core of this step was defining a trigger named products_after_insert that executes after each insert on the products table. This trigger inserts a new record into the product_logs table, capturing the id, name, and price of the newly inserted product using the NEW keyword.