MySQL After Insert Trigger
Last Updated :
23 Jul, 2025
An "AFTER INSERT" trigger in MySQL automatically executes specified actions after a new row is inserted into a table. It is used to perform tasks such as updating related tables, logging changes or performing calculations, ensuring immediate and consistent data processing.
In this article, We will learn about the MySQL After Insert Trigger in detail by understanding various examples and so on.
What is an AFTER INSERT Trigger
- An "AFTER INSERT" trigger in MySQL is a kind of trigger that, upon the insertion of any new row into any table in the database, self-executes with defined actions.
- Stated differently, once an insert operation is done, an "AFTER INSERT" trigger does other tasks, like updating relevant tables, logging the new entry or doing calculations.
Syntax for Creating an AFTER INSERT Trigger
The basic syntax for creating an "AFTER INSERT" trigger is as follows:
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
-- trigger logic here
END;
where,
- trigger_name: The name given to the trigger.
- Table name: refers to the name of the table to which the trigger is applied.
- FOR EACH ROW This specifies that—once for every row affected by the insert—a trigger is executed.
- BEGIN. END;: Contains the SQL statements that define the trigger’s logic.
Example of an AFTER INSERT Trigger
Suppose that you have an 'orders' table and want to create an "AFTER INSERT" trigger that logs the details of an order into a table called 'order_log'.
Trigger Creation
CREATE TRIGGER log_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_log (order_id, customer_id, order_date, total_amount)
VALUES (NEW.order_id, NEW.customer_id, NEW.order_date, NEW.total_amount);
END;
Insert a Row into the orders Table:
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (1, 123, '2024-07-30', 250.00);
Trigger Execution:
INSERT INTO order_log (order_id, customer_id, order_date, total_amount)
VALUES (1, 123, '2024-07-30', 250.00);
Explanation:
- The CREATE TRIGGER log_order_insert statement makes an "AFTER INSERT" trigger on the orders table. This implies that the trigger fires off all by itself after a new row has been inserted into the orders table.
- This triggers BEGIN … for each row inserted. This NEW keyword references data in the newly inserted row, so that every new order taken is recorded in the order_log table for record keeping or auditing purposes.
Output:
orders Table:
order_id | customer_id | order_date | total_amount |
---|
1 | 123 | 2024-07-30 | 250.00 |
order_log Table:
order_id | customer_id | order_date | total_amount |
---|
1 | 123 | 2024-07-30 | 250.00 |
---|
Example 2:
Suppose one has a database for a library system. There should be a table for holding data about the books and another for logging each new book added into the library.
Create the books Table
CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
author VARCHAR(255),
published_date DATE,
genre VARCHAR(100),
added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Create the book_log Table
CREATE TABLE book_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
book_id INT,
log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
action VARCHAR(50)
);
Create the AFTER INSERT Trigger
CREATE TRIGGER log_book_insert
AFTER INSERT ON books
FOR EACH ROW
BEGIN
INSERT INTO book_log (book_id, log_date, action)
VALUES (NEW.book_id, NOW(), 'Book Added');
END;
Insert a New Book to Test the Trigger
INSERT INTO books (title, author, published_date, genre)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', '1925-04-10', 'Fiction');
Check the Data in the books Table
SELECT * FROM books;
Output:
book_id | title | author | published_date | genre | added_date |
---|
1 | The Great Gatsby | F. Scott Fitzgerald | 1925-04-10 | Fiction | 2024-07-30 12:34:56 |
---|
Conclusion
MySQL's "AFTER INSERT" triggers are very useful in performing an action based on a new row being inserted into a table. They improve the working of your database by logging, updating related tables, and integrity checking of your data—all without manual intervention. Applying and understanding these triggers will help you automate operations, enforce business rules, and maintain consistency over your data.
Similar Reads
SQL Tutorial Structured Query Language (SQL) is the standard language used to interact with relational databases. Whether you want to create, delete, update or read data, SQL provides the structure and commands to perform these operations. SQL is widely supported across various database systems like MySQL, Oracl
8 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
Class Diagram | Unified Modeling Language (UML) A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a projectâlike developers and designersâunderstand how the system is organized and how its components interact
12 min read
Python Variables In Python, variables are used to store data that can be referenced and manipulated during program execution. A variable is essentially a name that is assigned to a value. Unlike many other programming languages, Python variables do not require explicit declaration of type. The type of the variable i
6 min read
Spring Boot Interview Questions and Answers Spring Boot is a Java-based framework used to develop stand-alone, production-ready applications with minimal configuration. Introduced by Pivotal in 2014, it simplifies the development of Spring applications by offering embedded servers, auto-configuration, and fast startup. Many top companies, inc
15+ min read
Backpropagation in Neural Network Back Propagation is also known as "Backward Propagation of Errors" is a method used to train neural network . Its goal is to reduce the difference between the modelâs predicted output and the actual output by adjusting the weights and biases in the network.It works iteratively to adjust weights and
9 min read
Polymorphism in Java Polymorphism in Java is one of the core concepts in object-oriented programming (OOP) that allows objects to behave differently based on their specific class type. The word polymorphism means having many forms, and it comes from the Greek words poly (many) and morph (forms), this means one entity ca
7 min read