Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-after-insert-trigger/ below:

MySQL After Insert Trigger - GeeksforGeeks

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.

• 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.

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'.

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 INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (1, 123, '2024-07-30', 250.00);

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

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 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 TABLE book_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action VARCHAR(50)
);

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 INTO books (title, author, published_date, genre)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', '1925-04-10', 'Fiction');

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

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4