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

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

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

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

Hint

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

Practice

1. What is the main purpose of a trigger in MySQL?

easy

A. To backup the database automatically

B. To create new tables in the database

C. To manually update data by user command

D. To automatically run code when data changes

Solution

1. Step 1: Understand what triggers do

   Triggers run automatically when certain data changes happen in the database, like insert, update, or delete.

2. Step 2: Compare options with trigger purpose

   Only To automatically run code when data changes describes automatic code running on data changes, which matches the trigger's role.

3. Final Answer:

   To automatically run code when data changes -> Option D

4. Quick Check:

   Trigger = automatic response to data change [OK]

Hint: Triggers run code automatically on data changes [OK]

Common Mistakes:

• Thinking triggers create tables
• Confusing triggers with manual commands
• Assuming triggers backup data

2. Which of the following is the correct way to start creating a trigger in MySQL?

easy

A. CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW

B. MAKE TRIGGER trigger_name AFTER DELETE ON table_name

C. START TRIGGER trigger_name ON table_name

D. BEGIN TRIGGER trigger_name BEFORE UPDATE

Solution

1. Step 1: Recall MySQL trigger syntax

   The correct syntax starts with CREATE TRIGGER, followed by trigger name, timing (BEFORE/AFTER), event (INSERT/UPDATE/DELETE), and table name.

2. Step 2: Check each option's syntax

   Only CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW matches the correct syntax format exactly.

3. Final Answer:

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW -> Option A

4. Quick Check:

   Correct syntax starts with CREATE TRIGGER [OK]

Hint: Trigger syntax starts with CREATE TRIGGER [OK]

Common Mistakes:

• Using MAKE or START instead of CREATE
• Missing FOR EACH ROW clause
• Wrong order of keywords

3. Given this trigger code:

CREATE TRIGGER update_stock AFTER INSERT ON sales FOR EACH ROW BEGIN UPDATE products SET stock = stock - NEW.quantity WHERE product_id = NEW.product_id; END;

What happens when a new sale is inserted?

medium

A. The stock of the sold product decreases by the sold quantity

B. The stock of all products increases

C. The sales table is deleted

D. Nothing happens automatically

Solution

1. Step 1: Analyze the trigger event and action

   The trigger runs AFTER INSERT on sales table, so after a sale is added, it updates products stock.

2. Step 2: Understand the update statement

   The update reduces stock by NEW.quantity for the product matching NEW.product_id, meaning stock decreases for sold product.

3. Final Answer:

   The stock of the sold product decreases by the sold quantity -> Option A

4. Quick Check:

   AFTER INSERT trigger updates stock correctly [OK]

Hint: AFTER INSERT triggers run after new rows added [OK]

Common Mistakes:

• Thinking stock increases instead of decreases
• Assuming trigger deletes tables
• Believing trigger does nothing automatically

4. This trigger code causes an error:

CREATE TRIGGER wrong_trigger BEFORE UPDATE ON orders BEGIN SET NEW.status = 'processed'; END;

What is the main problem?

medium

A. Cannot assign values to NEW in triggers

B. Missing FOR EACH ROW clause after table name

C. Cannot use BEFORE UPDATE triggers in MySQL

D. Trigger name cannot have underscores

Solution

1. Step 1: Check trigger syntax requirements

   MySQL requires FOR EACH ROW clause after the table name in triggers that act on rows.

2. Step 2: Identify missing part in code

   The code misses FOR EACH ROW after ON orders, causing syntax error.

3. Final Answer:

   Missing FOR EACH ROW clause after table name -> Option B

4. Quick Check:

   FOR EACH ROW is mandatory in row-level triggers [OK]

Hint: Always include FOR EACH ROW in row triggers [OK]

Common Mistakes:

• Thinking BEFORE UPDATE triggers are not allowed
• Believing NEW cannot be assigned
• Assuming trigger names cannot have underscores

5. You want to ensure that when a user's email is updated, a log entry is automatically created in a separate table. Which trigger setup best automates this?

hard

A. CREATE TRIGGER log_email_change AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO email_log(user_id, old_email, new_email) VALUES (NEW.id, OLD.email, NEW.email); END;

B. CREATE TRIGGER log_email_change BEFORE INSERT ON users FOR EACH ROW BEGIN INSERT INTO email_log(user_id, old_email, new_email) VALUES (NEW.id, OLD.email, NEW.email); END;

C. CREATE TRIGGER log_email_change AFTER UPDATE ON users FOR EACH ROW BEGIN INSERT INTO email_log(user_id, old_email, new_email) VALUES (NEW.id, OLD.email, NEW.email); END;

D. CREATE TRIGGER log_email_change BEFORE UPDATE ON users BEGIN INSERT INTO email_log(user_id, old_email, new_email) VALUES (NEW.id, OLD.email, NEW.email); END;

Solution

1. Step 1: Identify correct trigger timing and event

   We want to log after the email changes, so AFTER UPDATE is correct to capture new and old values.

2. Step 2: Check trigger syntax and usage of OLD and NEW

   CREATE TRIGGER log_email_change AFTER UPDATE ON users FOR EACH ROW BEGIN INSERT INTO email_log(user_id, old_email, new_email) VALUES (NEW.id, OLD.email, NEW.email); END; uses FOR EACH ROW and correctly inserts old and new email values into log table after update.

3. Final Answer:

   CREATE TRIGGER log_email_change AFTER UPDATE ON users FOR EACH ROW BEGIN INSERT INTO email_log(user_id, old_email, new_email) VALUES (NEW.id, OLD.email, NEW.email); END; -> Option C

4. Quick Check:

   AFTER UPDATE with OLD and NEW logs changes [OK]

Hint: Use AFTER UPDATE with OLD and NEW for logging changes [OK]

Common Mistakes:

• Using BEFORE INSERT instead of AFTER UPDATE
• Missing FOR EACH ROW clause
• Using AFTER DELETE when updating data