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.

Practice

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

easy

A. To select data from another table before insertion

B. To delete rows after they are inserted

C. To modify or validate data before it is inserted into a table

D. To create a new table before inserting data

Solution

1. Step 1: Understand the trigger timing

   A BEFORE INSERT trigger runs before the new row is saved to the table.

2. Step 2: Identify the trigger's purpose

   It allows you to check or change the data before it is inserted, ensuring data integrity or applying rules.

3. Final Answer:

   To modify or validate data before it is inserted into a table -> Option C

4. Quick Check:

   BEFORE INSERT triggers run before insert = A [OK]

Hint: BEFORE INSERT triggers act before saving data [OK]

Common Mistakes:

• Confusing BEFORE INSERT with AFTER INSERT triggers
• Thinking triggers create or delete tables
• Assuming triggers run after data is saved

2. Which of the following is the correct syntax to create a BEFORE INSERT trigger on a table named users?

easy

A. CREATE TRIGGER before_insert_users AFTER INSERT ON users FOR EACH ROW SET NEW.name = UPPER(NEW.name);

B. CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH ROW SET NEW.name = UPPER(NEW.name);

C. CREATE TRIGGER before_insert_users BEFORE UPDATE ON users FOR EACH ROW SET NEW.name = UPPER(NEW.name);

D. CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH STATEMENT SET NEW.name = UPPER(NEW.name);

Solution

1. Step 1: Identify correct trigger timing and event

   The question asks for a BEFORE INSERT trigger, so timing is BEFORE and event is INSERT.

2. Step 2: Check syntax details

   Triggers must be defined FOR EACH ROW to access NEW values. CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH ROW SET NEW.name = UPPER(NEW.name); matches this exactly.

3. Final Answer:

   CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH ROW SET NEW.name = UPPER(NEW.name); -> Option B

4. Quick Check:

   BEFORE INSERT + FOR EACH ROW = D [OK]

Hint: BEFORE INSERT triggers use FOR EACH ROW, not STATEMENT [OK]

Common Mistakes:

• Using AFTER instead of BEFORE
• Using BEFORE UPDATE instead of BEFORE INSERT
• Using FOR EACH STATEMENT instead of FOR EACH ROW

3. Given this trigger and insert statement:

CREATE TRIGGER check_age BEFORE INSERT ON employees FOR EACH ROW BEGIN IF NEW.age < 18 THEN SET NEW.age = 18; END IF; END;

What will be the age value after running:

INSERT INTO employees (name, age) VALUES ('Alice', 16);

medium

A. 18

B. NULL

C. 16

D. Error: age cannot be less than 18

Solution

1. Step 1: Understand trigger logic

   The trigger checks if NEW.age is less than 18, and if so, sets NEW.age to 18 before insert.

2. Step 2: Apply trigger to inserted data

   The insert tries to add age 16, which is less than 18, so trigger changes it to 18.

3. Final Answer:

   18 -> Option A

4. Quick Check:

   Trigger sets age to 18 if less than 18 = 18 [OK]

Hint: BEFORE INSERT can modify NEW values before saving [OK]

Common Mistakes:

• Assuming original value 16 is saved
• Expecting an error instead of modification
• Confusing NEW with OLD values

4. Consider this trigger code:

CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Quantity must be positive'; END IF; END;

What error will occur if you run:

INSERT INTO orders (product_id, quantity) VALUES (101, 0);

medium

A. Runtime error with message 'Quantity must be positive'

B. No error, row inserted with quantity 0

C. Syntax error in trigger definition

D. Trigger ignored, default quantity used

Solution

1. Step 1: Analyze trigger condition

   The trigger checks if NEW.quantity is less than 1, which is true for 0.

2. Step 2: Understand SIGNAL statement effect

   The SIGNAL statement raises a runtime error with the given message, stopping the insert.

3. Final Answer:

   Runtime error with message 'Quantity must be positive' -> Option A

4. Quick Check:

   SIGNAL raises error if condition met = B [OK]

Hint: SIGNAL in BEFORE INSERT stops invalid inserts [OK]

Common Mistakes:

• Thinking insert succeeds with invalid data
• Confusing SIGNAL with warning
• Assuming syntax error instead of runtime error

5. You want to ensure that every new row inserted into the products table has a unique SKU code in uppercase. Which BEFORE INSERT trigger code correctly enforces this?

hard

A. CREATE TRIGGER sku_uppercase BEFORE UPDATE ON products FOR EACH ROW BEGIN SET NEW.sku = UPPER(NEW.sku); END;

B. CREATE TRIGGER sku_uppercase AFTER INSERT ON products FOR EACH ROW BEGIN SET NEW.sku = UPPER(NEW.sku); END;

C. CREATE TRIGGER sku_uppercase BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.sku = LOWER(NEW.sku); END;

D. CREATE TRIGGER sku_uppercase BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.sku = UPPER(NEW.sku); IF EXISTS (SELECT 1 FROM products WHERE sku = NEW.sku) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate SKU'; END IF; END;

Solution

1. Step 1: Ensure SKU is uppercase before insert

   CREATE TRIGGER sku_uppercase BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.sku = UPPER(NEW.sku); IF EXISTS (SELECT 1 FROM products WHERE sku = NEW.sku) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate SKU'; END IF; END; uses SET NEW.sku = UPPER(NEW.sku) in a BEFORE INSERT trigger, correctly modifying the new row.

2. Step 2: Check for duplicate SKU before insert

   CREATE TRIGGER sku_uppercase BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.sku = UPPER(NEW.sku); IF EXISTS (SELECT 1 FROM products WHERE sku = NEW.sku) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate SKU'; END IF; END; checks if the SKU already exists and raises an error to prevent duplicates.

3. Step 3: Verify other options

   CREATE TRIGGER sku_uppercase AFTER INSERT ON products FOR EACH ROW BEGIN SET NEW.sku = UPPER(NEW.sku); END; uses AFTER INSERT, too late to modify NEW. CREATE TRIGGER sku_uppercase BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.sku = LOWER(NEW.sku); END; uses LOWER instead of UPPER. CREATE TRIGGER sku_uppercase BEFORE UPDATE ON products FOR EACH ROW BEGIN SET NEW.sku = UPPER(NEW.sku); END; triggers on UPDATE, not INSERT.

4. Final Answer:

   CREATE TRIGGER sku_uppercase BEFORE INSERT ON products FOR EACH ROW BEGIN SET NEW.sku = UPPER(NEW.sku); IF EXISTS (SELECT 1 FROM products WHERE sku = NEW.sku) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate SKU'; END IF; END; -> Option D

5. Quick Check:

   Modify NEW and check duplicates BEFORE INSERT = A [OK]

Hint: Use BEFORE INSERT to modify and validate NEW row [OK]

Common Mistakes:

• Using AFTER INSERT to modify NEW values
• Not checking duplicates before insert
• Modifying SKU to lowercase instead of uppercase