How to Insert Data into Another Table Using MySQL Triggers?

Programming MySQL Triggers: Inserting into Another Table

MySQL triggers offer a powerful mechanism to automate tasks upon specific database actions. This can be particularly useful for maintaining data consistency and performing complex operations.

Inserting into Another Table

To insert a row into another table when a row is inserted into a particular table, you can define an after insert trigger. Here's how to do it in MySQL:

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- Insert data into another table
  INSERT INTO other_table (column1, column2, ...)
  VALUES (NEW.column1, NEW.column2, ...);
END;

Answering Your Questions

• LAST_INSERT_ID(): Yes, LAST_INSERT_ID() is typically used to retrieve the ID of the last inserted row.
• Storing Last Inserted Row Data: You can access the data from the last inserted comment row using the NEW pseudo-table. It contains values for the columns of the inserted row.
• Stored Procedures: Stored procedures can be used in conjunction with triggers, but they are not necessary for this specific task.
• Trigger Structure: The basic structure of an after insert trigger is shown above. You can specify a unique name for the trigger, the table it applies to, and the actions to perform for each inserted row.

Example

Consider the following example:

CREATE TABLE comments (
  comment_id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  PRIMARY KEY (comment_id)
);

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

CREATE TRIGGER comments_after_insert
AFTER INSERT ON comments
FOR EACH ROW
BEGIN
  -- Insert into the activities table
  INSERT INTO activities (comment_id, user_id)
  VALUES (NEW.comment_id, NEW.user_id);
END;

This trigger will automatically insert a row into the activities table whenever a row is inserted into the comments table, ensuring that actions are logged as desired.

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