How to Create a MySQL Trigger to Insert Data into Another Table After an INSERT?

Programming a MySQL Trigger to Insert Data into Another Table

MySQL triggers allow the execution of predefined actions when certain events occur in a database table. In this case, the goal is to create a trigger that inserts a row into another table when a row is inserted into a specific table.

How to Grab the ID of the Last Inserted Row

To capture the ID of the last inserted row, it is necessary to use the LAST_INSERT_ID() function within the trigger. This function returns the ID of the most recently inserted row.

Storing Data from Last Inserted Row for Use in INSERT Statement

To properly store the data from the last inserted comment row, it can be stored in local variables within the trigger. For example, if the user ID column is named user_id and the comment text column is named comment_text, you could use the following syntax:

DECLARE user_id INTEGER;
DECLARE comment_text VARCHAR(255);

SET user_id = NEW.user_id;
SET comment_text = NEW.comment_text;

Using Stored Procedures

While triggers are useful for performing actions automatically in response to certain events, they are limited in functionality and may not be suitable for complex operations. In such cases, it is recommended to use a combination of triggers and stored procedures.

Trigger Structure

The basic structure of the trigger would resemble the following:

DELIMITER $$

CREATE TRIGGER comments_after_ins_trig AFTER INSERT ON comments
FOR EACH ROW
BEGIN
  -- Grab the ID of the last inserted row
  SET @last_inserted_id = LAST_INSERT_ID();

  -- Store data from the last inserted row into local variables
  SET @user_id = NEW.user_id;
  SET @comment_text = NEW.comment_text;

  -- Perform the INSERT into the activities table
  INSERT INTO activities (user_id, comment_text) VALUES (@user_id, @comment_text);
END;
$$

DELIMITER ;

The above is the detailed content of How to Create a MySQL Trigger to Insert Data into Another Table After an INSERT?. For more information, please follow other related articles on the PHP Chinese website!