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

Using BEFORE INSERT Triggers in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to ensure that every new book added has a valid price and automatically set the date when the book was added.

🎯 Goal: Create a books table and a BEFORE INSERT trigger that checks if the price is positive and sets the added_date to the current date if not provided.

📋 What You'll Learn

Create a books table with columns id, title, author, price, and added_date

Create a BEFORE INSERT trigger named check_price_and_set_date

The trigger must prevent inserting a book with a non-positive price by setting it to 1.00

The trigger must set added_date to the current date if it is NULL during insert

💡 Why This Matters

🌍 Real World

BEFORE INSERT triggers help maintain data quality by automatically checking and adjusting data before it enters the database, useful in many business applications.

💼 Career

Database developers and administrators often write triggers to enforce business rules and data integrity without relying on application code.

1

Create the books table

Create a table called books with these columns: id as INT primary key auto-increment, title as VARCHAR(100), author as VARCHAR(100), price as DECIMAL(5,2), and added_date as DATE.

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Set up the trigger structure

Write the start of a BEFORE INSERT trigger named check_price_and_set_date on the books table. Use DELIMITER $$ before and DELIMITER ; after the trigger definition.

MySQL

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2),
  added_date DATE
);

-- Your code here to start the BEFORE INSERT trigger

Need a hint?

Remember to change the delimiter before and after the trigger to avoid errors.

3

Add logic to check price and set added_date

Inside the trigger, write an IF statement to check if NEW.price is less than or equal to 0. If yes, set NEW.price to 1.00. Then write another IF statement to check if NEW.added_date is NULL, and if so, set it to CURDATE().

MySQL

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2),
  added_date DATE
);

DELIMITER $$
CREATE TRIGGER check_price_and_set_date
BEFORE INSERT ON books
FOR EACH ROW
BEGIN
  -- Your code here to check price and set added_date

Need a hint?

Use IF statements and SET NEW.column = value inside the trigger.

4

Complete the trigger definition

Close the BEGIN block with END and the delimiter with $$. Then reset the delimiter to ;.

MySQL

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2),
  added_date DATE
);

DELIMITER $$
CREATE TRIGGER check_price_and_set_date
BEFORE INSERT ON books
FOR EACH ROW
BEGIN
  IF NEW.price <= 0 THEN
    SET NEW.price = 1.00;
  END IF;
  IF NEW.added_date IS NULL THEN
    SET NEW.added_date = CURDATE();
  END IF;
  -- Your code here to end the trigger

Need a hint?

Don't forget to close the trigger block and reset the delimiter.