Why triggers automate responses in MySQL - See It in Action

Why triggers automate responses

📖 Scenario: You manage a small online store database. You want to automatically record the time when a new order is added, without manually typing it every time.

🎯 Goal: Build a MySQL trigger that automatically sets the order_date to the current date and time when a new order is inserted.

📋 What You'll Learn

Create a table called orders with columns order_id (integer primary key), customer_name (text), and order_date (datetime).

Create a trigger called set_order_date that runs before inserting a new row into orders.

The trigger should set the order_date column to the current timestamp automatically.

Insert a sample order without specifying order_date to test the trigger.

💡 Why This Matters

🌍 Real World

Triggers help automate routine tasks in databases, like setting timestamps or enforcing rules, saving time and reducing errors.

💼 Career

Database developers and administrators use triggers to maintain data integrity and automate workflows without extra application code.

1

Create the orders table

Write a SQL statement to create a table called orders with columns: order_id as an integer primary key, customer_name as VARCHAR(100), and order_date as DATETIME.

MySQL

-- Your code here to create the orders table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Start creating the trigger

Write the beginning of a trigger named set_order_date that activates BEFORE INSERT on the orders table.

MySQL

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

-- Your code here to start the trigger definition

Need a hint?

Use CREATE TRIGGER with the name and timing before insert on the correct table.

3

Complete the trigger to set order_date

Complete the trigger by adding FOR EACH ROW and a BEGIN ... END block that sets NEW.order_date = NOW().

MySQL

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

CREATE TRIGGER set_order_date
BEFORE INSERT ON orders
-- Your code here to complete the trigger

Need a hint?

Use FOR EACH ROW and inside BEGIN ... END set the new row's order_date to the current timestamp with NOW().

4

Insert a sample order to test the trigger

Write an INSERT statement to add a new order with order_id 1 and customer_name 'Alice' without specifying order_date.

MySQL

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

CREATE TRIGGER set_order_date
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.order_date = NOW();
END;

-- Your code here to insert a sample order

Need a hint?

Insert a new row with order_id 1 and customer_name 'Alice' without order_date.