MySQL Trigger to Insert Row into Another Table

Triggers in MySQL enable us to describe automatic operations that would be conducted as a result of specific actions happening within tables, such as INSERT, UPDATE, or DELETE. Triggers are very useful to automate repetitive database operations and maintain data integrity.

Inserting a Row into Another Table with a Trigger

Inserting a row into another table using trigger can be achieved by using the CREATE TRIGGER command followed by the INSERT INTO statement in the trigger's code. This will be capable of adding a row to the second table automatically whenever an action on the first table is performed.

Let us understand this with an Example where a record is automatically added to table2 when we insert a new record into table 1.

Example

Let us first create two tables called Table1 and Table2 with the same schema and columns(id and name) using CREATE command.

CREATE TABLE Table1 (
   id INT NOT NULL,
   name VARCHAR(100),
   PRIMARY KEY (id)
);

Let us now create a second table.

CREATE TABLE Table2 (
   id INT NOT NULL,
   name VARCHAR(100),
   PRIMARY KEY (id)
);

Now, we will create trigger that automatically inserts a new row in Table2 for every new insertion in Table1.

DELIMITER #
CREATE TRIGGER Table1Trigger 
AFTER INSERT ON Table1
FOR EACH ROW
BEGIN
    INSERT INTO Table2(id, name) VALUES (NEW.id, NEW.name);
END #
DELIMITER ;

Let us understand the main statements in the above query to create a trigger.

• DELIMITER # : Changes the default delimiter(;) in order to allow creating a multi-statement trigger.
• AFTER Trigger: The After Insert Trigger is executed right after a value is inserted into a database table.
• INSERT INTO: This statement allows you to insert the records into table2 after we insert rows into first table.

Let us use the 'SHOW TRIGGERS' statement to check whether the trigger is created successfully or not. 

To check the details of a specific trigger in a database that contains multiple triggers, you can use the following query -

SHOW TRIGGERS LIKE 'Table1'\G

Following is the output of the above query ?

*************************** 1. row ***************************
             Trigger: Table1Trigger
               Event: INSERT
               Table: table1
           Statement: BEGIN
    INSERT INTO Table2(id, name) VALUES (NEW.id, NEW.name);
END
              Timing: AFTER
             Created: 2024-11-25 11:44:05.07
            sql_mode:
             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Inserting the values into Table1 activates the trigger and inserts the records into Table2.

To insert records in Table1 we use INSERT command.

INSERT INTO Table1 (id, name) 
VALUES 
    (1, 'John'),
    (2, 'Smith'),
    (3, 'Carol');

Let us fetch the data to check if the records are inserted in both the tables, using the SELECT command.

SELECT * FROM Table1;

Here is the output that shows records inserted successfully in Table1.

To check for the second table.

SELECT * FROM Table2;

The following is the output that shows the record inserted successfully in Table2.

This demonstrates that the trigger is working properly since it automatically inserts identical records into Table2 as they are added to Table 1.