BEFORE trigger execution in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of BEFORE INSERT trigger modifying data

Consider a table users with columns id (int) and username (varchar). A BEFORE INSERT trigger changes the username to uppercase before insertion.
What will be the content of the table after running INSERT INTO users (id, username) VALUES (1, 'alice');?

SQL

CREATE TABLE users (id INT, username VARCHAR(20));
DELIMITER $$
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.username = UPPER(NEW.username);
END$$
DELIMITER ;

INSERT INTO users (id, username) VALUES (1, 'alice');

SELECT * FROM users;

A [{"id": 1, "username": "alice"}]

B SyntaxError

C [{"id": 1, "username": "ALICE"}]

D []

💡 Hint

BEFORE triggers can modify the data before it is saved.

The BEFORE INSERT trigger changes the username to uppercase before the row is inserted, so the stored username is 'ALICE'.

❓ query_result

intermediate

Effect of BEFORE UPDATE trigger on data

Given a table products with columns id and price, a BEFORE UPDATE trigger doubles the price if the new price is less than 100.
What will be the price after executing UPDATE products SET price = 50 WHERE id = 1; if the original price was 80?

SQL

CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 80);
DELIMITER $$
CREATE TRIGGER before_update_products
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price < 100 THEN
    SET NEW.price = NEW.price * 2;
  END IF;
END$$
DELIMITER ;

UPDATE products SET price = 50 WHERE id = 1;
SELECT price FROM products WHERE id = 1;

A [{"price": 100}]

B [{"price": 50}]

C [{"price": 80}]

D RuntimeError

💡 Hint

BEFORE UPDATE triggers can change the new values before saving.

The trigger doubles the new price if it is less than 100, so 50 becomes 100 before update.

📝 Syntax

advanced

Identify the syntax error in BEFORE INSERT trigger

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

SQL

CREATE TRIGGER before_insert_test
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
  SET NEW.value = NEW.value + 1;
END;

A Trigger name is invalid

B FOR EACH ROW keyword is missing

C NEW keyword cannot be used in BEFORE triggers

D Missing DELIMITER change before and after trigger definition

💡 Hint

In MySQL, triggers require changing the delimiter to define the body.

Without changing the delimiter, the parser ends the statement early causing syntax error.

🧠 Conceptual

advanced

When does a BEFORE trigger execute?

Choose the correct statement about when a BEFORE trigger executes in relation to the triggering SQL statement.

A BEFORE triggers execute before each row is inserted, updated, or deleted

B BEFORE triggers execute before the triggering statement starts

C BEFORE triggers execute after the data is written to the table

D BEFORE triggers execute only after the transaction commits

💡 Hint

Think about when you want to modify data before it is saved.

BEFORE triggers run before each affected row is changed, allowing modification or validation of data.

🔧 Debug

expert

Why does this BEFORE INSERT trigger cause an infinite loop?

A BEFORE INSERT trigger on table orders tries to insert a row into the same table inside its body.
What is the main reason this causes an infinite loop?

SQL

CREATE TRIGGER before_insert_orders
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders (id, amount) VALUES (NEW.id + 1, NEW.amount);
END;

A The trigger syntax is invalid and causes a syntax error

B The trigger recursively calls itself because the INSERT inside the trigger fires the same trigger again

C The NEW keyword cannot be used inside INSERT statements

D Triggers cannot perform INSERT operations on any table

💡 Hint

Think about what happens when the trigger's INSERT runs.

The trigger's INSERT causes the trigger to fire again, creating an infinite recursion.