MySQL Triggers for Auto-Inserting Values

Understanding MySQL Triggers and Their Purpose

MySQL triggers are a powerful feature that allows developers to automate specific actions based on database events, such as insertions, updates, or deletions. In this article, we will explore how to create a trigger in MySQL to auto-insert values into a table when certain conditions are met.

Background Information: The Additional Table

Let’s start with the additional table, which has the following structure:

CREATE TABLE `additional` ( 
    `trip_num` VARCHAR(8) NOT NULL,  
    `add_id` VARCHAR(3) NOT NULL,  
    `add_type` VARCHAR(10) NOT NULL,  
    `value` VARCHAR(5) NOT NULL );

This table stores information about various fields related to trips. The add_id, add_type, and value columns are used to store the ID of the type of field added, the actual value of that field, and other relevant details.

The Problem: Manually Inserting Values

The user has already manually inserted values into this table:

INSERT INTO `additional` (`trip_num`, `add_id`, `add_type`, `value`) 
VALUES 
('TRP0001', 'UM1', 'type 1', '50'), 
('TRP0001', 'UM2', 'type 1', '27'), 
('TRP0001', 'UB1', 'type 2', '250'), 
('TRP0002', 'UM1', 'type 1', '32'), 
('TRP0002', 'UB1', 'type 2', '300'), 
('TRP0002', 'UB2', 'type 2', '120'), 
('TRP0002', 'UL1', 'type 3', '120');

However, this manual process is prone to errors and can be time-consuming. We want to automate the insertion of values into the additional table based on specific conditions.

The Solution: Using a MySQL Trigger

To achieve this, we will create a trigger in MySQL that inserts new values into the additional table whenever an insert operation occurs on the table.

Here’s how you can do it:

CREATE TRIGGER tr_bi
BEFORE INSERT 
ON additional
FOR EACH ROW
BEGIN
DECLARE counter INT;
SELECT 1 + COUNT(*) 
    INTO counter 
    FROM additional 
    WHERE trip_num = NEW.trip_num
      AND add_type = NEW.add_type;
SET NEW.add_id = CONCAT( CASE NEW.add_type WHEN 'type 1' THEN 'UM'
                                           WHEN 'type 2' THEN 'UB'
                                           WHEN 'type 3' THEN 'UL'
                                                         ELSE '??' END,
                         counter );
END;

This trigger is named tr_bi and it’s activated whenever an insert operation occurs on the additional table (ON additional). The BEFORE INSERT clause means that this trigger will be executed before any inserts are made.

How the Trigger Works

Here’s a step-by-step explanation of how the trigger works:

1. Trigger Declaration: We declare a variable counter to keep track of the number of existing rows in the table with matching values.
2. Counting Existing Rows: We use a SELECT statement within the trigger to count the number of existing rows that match both the trip_num and add_type columns of the new row being inserted (NEW.trip_num and NEW.add_type).
3. Incrementing Counter: The result is stored in the counter variable, which is then incremented by 1.
4. Auto-Inserting Value: Based on the value of counter, we concatenate a prefix to it using the CASE statement. If the add_type column has a specific value (e.g., ’type 1’, ’type 2’, etc.), we add a corresponding prefix (‘UM’ for ’type 1’, ‘UB’ for ’type 2’, and ‘UL’ for ’type 3’). Otherwise, we append a question mark to the end.

Conclusion

In this article, we explored how MySQL triggers can be used to automate specific actions based on database events. We created a trigger in MySQL that inserts new values into the additional table whenever an insert operation occurs on the table. This solution reduces manual errors and saves time by automating the insertion process.

Last modified on 2025-02-02