Trigger best practices and limitations in MySQL - Mini Project: Build & Apply

Trigger Best Practices and Limitations in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to keep track of changes made to the books table by automatically recording updates in a separate books_audit table. This helps you monitor changes and maintain data integrity.

🎯 Goal: Build a MySQL trigger that logs updates on the books table into the books_audit table, following best practices and understanding limitations of triggers.

📋 What You'll Learn

Create a books table with columns book_id (INT, primary key), title (VARCHAR(100)), and price (DECIMAL(5,2))

Create a books_audit table with columns audit_id (INT, primary key, auto-increment), book_id (INT), old_price (DECIMAL(5,2)), new_price (DECIMAL(5,2)), and changed_at (TIMESTAMP)

Create a BEFORE UPDATE trigger on the books table that inserts a record into books_audit only if the price changes

Use proper trigger syntax and avoid common pitfalls such as recursive triggers or complex logic inside the trigger

💡 Why This Matters

🌍 Real World

Triggers help automate tasks like auditing changes, enforcing business rules, and maintaining data integrity without manual intervention.

💼 Career

Database developers and administrators use triggers to ensure data consistency and to track changes for compliance and debugging.

1

Create the books and books_audit tables

Create a table called books with columns book_id as INT primary key, title as VARCHAR(100), and price as DECIMAL(5,2). Also create a table called books_audit with columns audit_id as INT primary key auto-increment, book_id as INT, old_price as DECIMAL(5,2), new_price as DECIMAL(5,2), and changed_at as TIMESTAMP.

MySQL

-- Create the books table
-- Create the books_audit table

Hint

Use CREATE TABLE statements with the exact column names and types as specified.

2

Set up a helper variable to check price changes

Declare a variable inside the trigger to check if the price has changed by comparing OLD.price and NEW.price. This will help decide if an audit record should be inserted.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

CREATE TABLE books_audit (
  audit_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  old_price DECIMAL(5,2),
  new_price DECIMAL(5,2),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Declare a variable inside the trigger to check price change
-- Your code here

Hint

Use DECLARE to create a boolean variable and SET to assign the comparison result.

3

Insert audit record only if price changed

Inside the trigger, write an IF statement that inserts a new row into books_audit with book_id, old_price, and new_price only if the price_changed variable is true.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

CREATE TABLE books_audit (
  audit_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  old_price DECIMAL(5,2),
  new_price DECIMAL(5,2),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER before_books_update
BEFORE UPDATE ON books
FOR EACH ROW
BEGIN
  DECLARE price_changed BOOLEAN;
  SET price_changed = (OLD.price  NEW.price);
  -- Insert audit record if price_changed is true
  -- Your code here
END$$
DELIMITER ;

Hint

Use an IF block to conditionally insert into books_audit.

4

Complete the trigger with proper delimiter usage

Ensure the trigger ends with END and the delimiter is reset to ; after the trigger definition to avoid syntax errors.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

CREATE TABLE books_audit (
  audit_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  old_price DECIMAL(5,2),
  new_price DECIMAL(5,2),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER before_books_update
BEFORE UPDATE ON books
FOR EACH ROW
BEGIN
  DECLARE price_changed BOOLEAN;
  SET price_changed = (OLD.price  NEW.price);
  IF price_changed THEN
    INSERT INTO books_audit (book_id, old_price, new_price) VALUES (OLD.book_id, OLD.price, NEW.price);
  END IF;
  -- End the trigger and reset delimiter
  -- Your code here
END$$
DELIMITER ;

Hint

Remember to close the trigger block with END and reset the delimiter to ;.

Practice

1. Which of the following is a best practice when creating MySQL triggers?

easy

A. Keep trigger logic simple to avoid performance issues.

B. Use triggers to replace all application logic.

C. Allow triggers to call themselves recursively without limits.

D. Write triggers that perform complex calculations on large datasets.

Solution

1. Step 1: Understand trigger performance impact

   Triggers run automatically on data changes, so complex logic can slow down database operations.

2. Step 2: Identify best practice for trigger design

   Keeping trigger logic simple helps maintain good performance and avoids unexpected delays.

3. Final Answer:

   Keep trigger logic simple to avoid performance issues. -> Option A

4. Quick Check:

   Simple trigger logic = Better performance [OK]

Hint: Keep triggers simple to avoid slowdowns [OK]

Common Mistakes:

• Trying to put all business logic inside triggers
• Allowing infinite recursion in triggers
• Using triggers for heavy data processing

2. Which syntax correctly defines a BEFORE INSERT trigger in MySQL?

easy

A. CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;

B. CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END;

C. CREATE TRIGGER trg BEFORE UPDATE ON table_name FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;

Solution

1. Step 1: Identify trigger timing and event

   BEFORE INSERT triggers run before a new row is inserted.

2. Step 2: Match syntax to BEFORE INSERT event

   CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; uses BEFORE INSERT, correct timing and event for this trigger.

3. Final Answer:

   CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; -> Option A

4. Quick Check:

   BEFORE INSERT syntax = CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; [OK]

Hint: BEFORE triggers run before data changes [OK]

Common Mistakes:

• Confusing BEFORE and AFTER keywords
• Using wrong event like UPDATE or DELETE
• Missing FOR EACH ROW clause

3. Given this trigger, what happens when a new row is inserted?

CREATE TRIGGER trg BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.salary = NEW.salary * 1.1; END;

medium

A. The trigger causes a syntax error.

B. The salary is increased by 10% before insertion.

C. The salary remains unchanged.

D. The insertion fails due to recursion.

Solution

1. Step 1: Analyze trigger action on BEFORE INSERT

   The trigger modifies the NEW.salary value by multiplying it by 1.1 before the row is inserted.

2. Step 2: Understand effect on inserted data

   The inserted row will have salary increased by 10% automatically.

3. Final Answer:

   The salary is increased by 10% before insertion. -> Option B

4. Quick Check:

   BEFORE INSERT modifies NEW.salary = increased salary [OK]

Hint: BEFORE triggers can modify NEW row data [OK]

Common Mistakes:

• Thinking triggers run after insertion
• Assuming salary stays the same
• Confusing NEW and OLD keywords

4. What is wrong with this trigger definition?

CREATE TRIGGER trg AFTER UPDATE ON employees FOR EACH ROW BEGIN UPDATE employees SET salary = salary * 1.05 WHERE id = NEW.id; END;

medium

A. It will not update any rows because NEW.id is invalid.

B. Syntax error due to missing DELIMITER statement.

C. Triggers cannot use UPDATE statements inside their body.

D. It causes infinite recursion by updating the same table inside the trigger.

Solution

1. Step 1: Identify trigger action on AFTER UPDATE

   The trigger updates the same table employees inside an AFTER UPDATE trigger.

2. Step 2: Understand recursion risk

   Updating the same table inside its own UPDATE trigger causes the trigger to call itself repeatedly, causing infinite recursion.

3. Final Answer:

   It causes infinite recursion by updating the same table inside the trigger. -> Option D

4. Quick Check:

   Trigger updating same table = recursion risk [OK]

Hint: Avoid updating same table inside its trigger [OK]

Common Mistakes:

• Ignoring recursion risk
• Assuming syntax error without DELIMITER
• Thinking triggers can't run UPDATE statements

5. You want to log every DELETE action on the orders table without slowing down the delete operation. Which trigger design is best?

hard

A. Use a BEFORE DELETE trigger that updates the orders table with a log message.

B. Use an AFTER DELETE trigger that updates the orders table itself.

C. Use an AFTER DELETE trigger that inserts a log record into a separate table.

D. Use a BEFORE DELETE trigger that performs complex calculations before deleting.

Solution

1. Step 1: Choose trigger timing for logging

   AFTER DELETE triggers run after the row is deleted, so they don't block the delete operation.

2. Step 2: Select action that avoids slowing delete

   Inserting a log record into a separate table is efficient and safe, avoiding recursion or delays.

3. Final Answer:

   Use an AFTER DELETE trigger that inserts a log record into a separate table. -> Option C

4. Quick Check:

   AFTER DELETE + separate log table = best practice [OK]

Hint: Log deletes AFTER action to avoid delays [OK]

Common Mistakes:

• Updating same table inside trigger causing recursion
• Using BEFORE DELETE for logging causing delays
• Doing heavy calculations inside triggers