How to Use MySQL Triggers to Insert Rows into Another Table?

Inserting Rows into another Table using MySQL Trigger

In relational database management systems like MySQL, triggers play a pivotal role in automating certain database actions in response to specific events, such as data insertion, deletion, or modification. In this context, let's delve into the topic of creating a trigger that inserts a row into another table upon row insertion in a given table.

Understanding the Issue

The goal is to create a MySQL trigger on a "comments" table that monitors each row insertion event. When a new comment row is inserted, the trigger should perform the following tasks:

1. Retrieve the ID of the newly inserted comment row.
2. Utilize the retrieved ID to perform an INSERT operation into an "activities" table, transferring relevant data from the comment row.

Trigger Structure

The basic structure of the trigger to implement these functionalities involves the following:

CREATE TRIGGER <trigger_name> AFTER INSERT ON <source_table>
FOR EACH ROW
BEGIN
    -- Retrieve the ID of the newly inserted row
    DECLARE last_insert_id INT;
    SET last_insert_id = LAST_INSERT_ID();

    -- Insert data into the "activities" table
    INSERT INTO <target_table> (column_list)
    VALUES (new.column1, new.column2, ..., last_insert_id);
END</target_table></source_table></trigger_name>

Trigger Implementation

To demonstrate the implementation, consider the following example:

-- Create the "comments" and "activities" tables
CREATE TABLE comments (
    comment_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL
) ENGINE=INNODB;

CREATE TABLE activities (
    activity_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    comment_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL
) ENGINE=INNODB;

-- Create the trigger to insert data into the "activities" table
CREATE TRIGGER comments_after_ins_trig AFTER INSERT ON comments
FOR EACH ROW
BEGIN
    -- Retrieve the ID of the newly inserted comment row
    DECLARE last_insert_id INT;
    SET last_insert_id = LAST_INSERT_ID();

    -- Insert data into the "activities" table
    INSERT INTO activities (comment_id, user_id)
    VALUES (new.comment_id, new.user_id);
END;

-- Insert some data into the "comments" table
INSERT INTO comments (user_id) VALUES (1), (2);

-- Query the "comments" and "activities" tables to observe the results
SELECT * FROM comments;
SELECT * FROM activities;

Upon executing the queries, you will notice that for each comment row inserted into the "comments" table, a corresponding row is automatically inserted into the "activities" table. This demonstrates the effectiveness of the trigger in achieving the desired result.

The above is the detailed content of How to Use MySQL Triggers to Insert Rows into Another Table?. For more information, please follow other related articles on the PHP Chinese website!