BEFORE INSERT triggers in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of BEFORE INSERT trigger modifying data

Consider a table users with columns id (auto-increment), username, and created_at. A BEFORE INSERT trigger sets created_at to the current date if not provided.

What will be the created_at value after running this insert?

MySQL

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), created_at DATE);
DELIMITER $$
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = CURDATE();
  END IF;
END$$
DELIMITER ;

INSERT INTO users (username) VALUES ('alice');

SELECT created_at FROM users WHERE username = 'alice';

A An error occurs because created_at is not nullable

B NULL because no value was provided explicitly

C The current date on the day of insertion (e.g., '2024-06-15')

D The default date '0000-00-00'

💡 Hint

Think about what the trigger does before the row is inserted.

The BEFORE INSERT trigger checks if created_at is NULL and sets it to the current date. Since no value was provided, it sets created_at to today's date.

🧠 Conceptual

intermediate

Purpose of BEFORE INSERT triggers

What is the main purpose of a BEFORE INSERT trigger in a database?

A To automatically delete rows after insertion

B To modify or validate data before it is inserted into the table

C To create indexes on the table

D To backup the database before insertion

💡 Hint

Think about when the trigger runs in relation to the insert operation.

BEFORE INSERT triggers run before data is inserted, allowing modification or validation of the data before it is saved.

📝 Syntax

advanced

Identify the syntax error in BEFORE INSERT trigger

Which option contains a syntax error in the BEFORE INSERT trigger definition?

MySQL

CREATE TABLE products (id INT PRIMARY KEY, price DECIMAL(10,2));

A CREATE TRIGGER trg_before_insert_products BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.price = NEW.price - 2; END;

B CREATE TRIGGER trg_before_insert_products BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.price = NEW.price + 5; END;

C CREATE TRIGGER trg_before_insert_products BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.price = NEW.price * 1.1; END;

D CREATE TRIGGER trg_before_insert_products BEFORE INSERT ON products FOR EACH ROW BEGIN SET price = price * 1.1; END;

💡 Hint

Remember how to refer to the new row's columns inside triggers.

Inside triggers, you must use NEW.column_name to refer to the new row's values. Option D uses price without NEW., causing a syntax error.

🔧 Debug

advanced

Why does this BEFORE INSERT trigger cause an error?

Given the following trigger, why does inserting a row cause an error?

MySQL

CREATE TABLE orders (id INT PRIMARY KEY, quantity INT);
DELIMITER $$
CREATE TRIGGER trg_before_insert_orders
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.quantity < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Quantity cannot be negative';
  END IF;
END$$
DELIMITER ;

INSERT INTO orders (id, quantity) VALUES (1, -5);

A The trigger raises a custom error because quantity is negative

B The trigger syntax is invalid and causes a syntax error

C The insert succeeds and the trigger does nothing

D The trigger causes a deadlock error

💡 Hint

Look at the SIGNAL statement inside the trigger.

The trigger checks if quantity is negative and raises a custom error using SIGNAL, preventing the insert.

❓ optimization

expert

Optimizing BEFORE INSERT trigger for bulk inserts

You have a BEFORE INSERT trigger that sets a timestamp column to the current time if NULL. You notice bulk inserts are slow. Which optimization can improve performance?

A Use a DEFAULT value for the timestamp column instead of a trigger

B Add an index on the timestamp column to speed up inserts

C Change the trigger to AFTER INSERT to reduce overhead

D Remove the trigger and set the timestamp in the application before insert

💡 Hint

Think about how to avoid running code for every row during insert.

Using a DEFAULT value for the timestamp column lets the database set it automatically without running trigger code, improving bulk insert speed.