AFTER INSERT triggers in MySQL - Mini Project: Build & Apply

Create an AFTER INSERT Trigger in MySQL

📖 Scenario: You manage a small online store database. You want to keep track of every new order inserted into the orders table by automatically recording a log entry in a separate order_logs table.

🎯 Goal: Build an AFTER INSERT trigger in MySQL that automatically inserts a log record into order_logs whenever a new order is added to orders.

📋 What You'll Learn

Create a table called orders with columns order_id (INT, primary key) and customer_name (VARCHAR(100))

Create a table called order_logs with columns log_id (INT, primary key, auto-increment), order_id (INT), and log_message (VARCHAR(255))

Create an AFTER INSERT trigger on orders that inserts a log message into order_logs with the new order_id and a message like 'New order inserted'

Use exact table and column names as specified

💡 Why This Matters

🌍 Real World

AFTER INSERT triggers are used in real databases to automate logging, auditing, or cascading actions when new data is added.

💼 Career

Database developers and administrators use triggers to enforce business rules and maintain data integrity automatically.

1

Create the orders table

Write a SQL statement to create a table called orders with columns order_id as INT primary key and customer_name as VARCHAR(100).

MySQL

-- Your code here to create the orders table

Need a hint?

Use CREATE TABLE orders and define order_id as primary key.

2

Create the order_logs table

Write a SQL statement to create a table called order_logs with columns log_id as INT primary key auto-increment, order_id as INT, and log_message as VARCHAR(255).

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100)
);
-- Your code here to create the order_logs table

Need a hint?

Remember to add AUTO_INCREMENT to log_id for automatic numbering.

3

Create the AFTER INSERT trigger

Write a SQL statement to create an AFTER INSERT trigger named after_order_insert on the orders table. The trigger should insert a new row into order_logs with the inserted order_id and the message 'New order inserted'.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100)
);

CREATE TABLE order_logs (
  log_id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT,
  log_message VARCHAR(255)
);
-- Your code here to create the AFTER INSERT trigger

Need a hint?

Use DELIMITER $$ before and DELIMITER ; after the trigger definition.

4

Test the trigger by inserting a new order

Write a SQL statement to insert a new row into orders with order_id 101 and customer_name 'Alice'. This will activate the trigger to insert a log entry.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100)
);

CREATE TABLE order_logs (
  log_id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT,
  log_message VARCHAR(255)
);

DELIMITER $$
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_logs (order_id, log_message) VALUES (NEW.order_id, 'New order inserted');
END$$
DELIMITER ;
-- Your code here to insert a new order

Need a hint?

Use INSERT INTO orders (order_id, customer_name) VALUES (101, 'Alice');