INSERT trigger in SQL - Mini Project: Build & Apply

Create an INSERT Trigger to Log New Orders

📖 Scenario: You manage an online store database. You want to keep track of every new order placed by customers by automatically recording details into a separate log table.

🎯 Goal: Build an INSERT trigger on the orders table that automatically inserts a record into the order_log table whenever a new order is added.

📋 What You'll Learn

Create a table called orders with columns order_id (integer), customer_name (varchar), and order_total (decimal).

Create a table called order_log with columns log_id (integer, auto-increment), order_id (integer), and log_message (varchar).

Create an INSERT trigger named log_new_order on the orders table.

The trigger should insert a log entry into order_log with the order_id and a message like 'New order added'.

💡 Why This Matters

🌍 Real World

Automatically logging changes in a database helps keep track of important events without manual effort.

💼 Career

Database triggers are commonly used in real jobs to enforce business rules and audit data changes.

1

Create the orders table

Create a table called orders with columns order_id as INTEGER, customer_name as VARCHAR(100), and order_total as DECIMAL(10,2).

SQL

-- Create the orders table
-- Your code here

Need a hint?

Use CREATE TABLE orders and define the columns with their types exactly as specified.

2

Create the order_log table

Create a table called order_log with columns log_id as INTEGER AUTO_INCREMENT PRIMARY KEY, order_id as INTEGER, and log_message as VARCHAR(255).

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_name VARCHAR(100),
    order_total DECIMAL(10,2)
);
-- Create the order_log table
-- Your code here

Need a hint?

Remember to set log_id as the primary key and auto-increment it.

3

Create the INSERT trigger log_new_order

Create an INSERT trigger named log_new_order on the orders table that runs AFTER insert.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_name VARCHAR(100),
    order_total DECIMAL(10,2)
);

CREATE TABLE order_log (
    log_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    order_id INTEGER,
    log_message VARCHAR(255)
);
-- Create the INSERT trigger log_new_order
-- Your code here

Need a hint?

Use NEW.order_id to get the inserted order's ID inside the trigger.

4

Complete the trigger with proper delimiter handling

Add the delimiter commands before and after the trigger creation to allow the trigger body to be parsed correctly.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_name VARCHAR(100),
    order_total DECIMAL(10,2)
);

CREATE TABLE order_log (
    log_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    order_id INTEGER,
    log_message VARCHAR(255)
);

-- Add delimiter commands and create the trigger
-- Your code here

Need a hint?

Use DELIMITER $$ before and DELIMITER ; after the trigger to handle the semicolons inside the trigger body.