UPDATE trigger with OLD and NEW in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after updating a row with this trigger?

Consider a table employees with columns id, salary, and bonus. There is an UPDATE trigger that sets NEW.bonus = OLD.salary * 0.1 before updating the row. If the row with id = 1 has salary = 1000 and bonus = 0, what will be the bonus value after running UPDATE employees SET salary = 1200 WHERE id = 1;?

SQL

CREATE TRIGGER update_bonus BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  SET NEW.bonus = OLD.salary * 0.1;
END;

A bonus = 0

B bonus = 120

C bonus = 100

D bonus = NULL

💡 Hint

Remember that OLD refers to the row before update, NEW is the row after update but before saving.

The trigger uses OLD.salary (1000) to calculate bonus as 1000 * 0.1 = 100, so bonus becomes 100 regardless of the new salary.

🧠 Conceptual

intermediate

Which statement about OLD and NEW in UPDATE triggers is true?

In an UPDATE trigger, what do OLD and NEW represent?

A OLD is the row after update; NEW is the row before update

B OLD is the row before update; NEW is the row after update but before saving

C OLD and NEW both represent the row after update

D OLD and NEW both represent the row before update

💡 Hint

Think about what you want to compare or change during an update.

OLD holds the original row data before the update; NEW holds the new data that will be saved.

📝 Syntax

advanced

Which trigger code will cause a syntax error?

Identify the option that will cause a syntax error when creating an UPDATE trigger using OLD and NEW.

A CREATE TRIGGER trg BEFORE UPDATE ON sales FOR EACH ROW BEGIN SET NEW.total = OLD.amount + NEW.tax; END;

B CREATE TRIGGER trg BEFORE UPDATE ON sales FOR EACH ROW BEGIN SET NEW.total = OLD.amount + OLD.tax; END;

C CREATE TRIGGER trg BEFORE UPDATE ON sales FOR EACH ROW BEGIN SET NEW.total = NEW.amount + OLD.tax; END;

D CREATE TRIGGER trg BEFORE UPDATE ON sales FOR EACH ROW BEGIN SET OLD.total = NEW.amount + OLD.tax; END;

💡 Hint

Can you assign a value to OLD in a trigger?

OLD is read-only in triggers; assigning to OLD.total causes a syntax error.

❓ optimization

advanced

How to optimize an UPDATE trigger that recalculates a column only if a specific column changes?

You have an UPDATE trigger that recalculates the discount column whenever any column changes. How can you optimize it to recalculate only if price changes?

A Add an IF statement: IF NEW.price <> OLD.price THEN recalculate discount END IF;

B Always recalculate discount without any condition for safety.

C Use a BEFORE INSERT trigger instead of UPDATE trigger.

D Remove the trigger and calculate discount in application code.

💡 Hint

Check if the value changed before recalculating.

Using IF NEW.price <> OLD.price avoids unnecessary recalculations when price is unchanged.

🔧 Debug

expert

Why does this UPDATE trigger cause an infinite loop?

Given this trigger code, why does updating the quantity column cause an infinite loop? CREATE TRIGGER trg_update_quantity AFTER UPDATE ON inventory FOR EACH ROW BEGIN UPDATE inventory SET quantity = NEW.quantity WHERE id = NEW.id; END;

SQL

CREATE TRIGGER trg_update_quantity AFTER UPDATE ON inventory
FOR EACH ROW
BEGIN
  UPDATE inventory SET quantity = NEW.quantity WHERE id = NEW.id;
END;

A The trigger updates the same table causing it to fire repeatedly without stop

B The trigger uses AFTER UPDATE instead of BEFORE UPDATE

C The trigger tries to update OLD values which is not allowed

D The trigger does not have a WHERE clause to limit updates

💡 Hint

Think about what happens when a trigger updates the same table it listens to.

The trigger updates the table on every update, causing itself to fire again and again, creating an infinite loop.