MySQL After Insert Trigger

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_log Table:

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:

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.