AFTER INSERT triggers in MySQL

Introduction

AFTER INSERT triggers run automatically after new data is added to a table. They help you do extra tasks right after inserting data.

You want to log every new record added to a table.

You need to update related tables after adding new data.

You want to send notifications or alerts after data insertion.

You want to enforce additional rules or calculations after insert.

You want to keep an audit trail of changes in your database.

Syntax

MySQL

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- statements to execute
END;

The trigger runs once for each row inserted.

Use NEW.column_name to access the inserted row's data.

Examples

This trigger adds a log entry every time a new user is added.

MySQL

CREATE TRIGGER log_new_user
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_log(user_id, action) VALUES (NEW.id, 'inserted');
END;

This trigger updates a stats table to count total orders after each new order.

MySQL

CREATE TRIGGER update_stats_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE stats SET total_orders = total_orders + 1;
END;

Sample Program

This example creates a products table and a product_log table. The trigger adds a log entry every time a new product is inserted.

MySQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2)
);

CREATE TABLE product_log (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  action VARCHAR(20),
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER after_product_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
  INSERT INTO product_log(product_id, action) VALUES (NEW.id, 'inserted');
END$$
DELIMITER ;

INSERT INTO products(name, price) VALUES ('Pen', 1.20);

SELECT * FROM product_log;

Important Notes

Remember to change the delimiter when creating triggers in MySQL to avoid errors.

AFTER INSERT triggers cannot modify the row being inserted, only act after insertion.

Summary

AFTER INSERT triggers run after new rows are added.

They help automate tasks like logging or updating related data.

Use NEW to access inserted row data inside the trigger.