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.

Practice

1. What do the OLD and NEW keywords represent in an UPDATE trigger?

easy

A. OLD and NEW both represent the data before update

B. OLD is the data after update; NEW is the data before update

C. OLD and NEW both represent the data after update

D. OLD is the data before update; NEW is the data after update

Solution

1. Step 1: Understand trigger timing

   In an UPDATE trigger, OLD holds the row data before the update happens.

2. Step 2: Understand data after update

   NEW holds the row data after the update is applied.

3. Final Answer:

   OLD is the data before update; NEW is the data after update -> Option D

4. Quick Check:

   OLD = before, NEW = after [OK]

Hint: OLD = before update, NEW = after update [OK]

Common Mistakes:

• Confusing OLD and NEW roles
• Thinking both OLD and NEW hold same data
• Assuming OLD is after update

2. Which of the following is the correct syntax to access the new value of a column named salary inside an UPDATE trigger?

easy

A. NEW.salary

B. OLD.salary

C. SELECT salary FROM NEW;

D. UPDATE salary SET NEW;

Solution

1. Step 1: Identify how to reference NEW values in triggers

   Inside an UPDATE trigger, you access new column values using NEW.column_name.

2. Step 2: Match syntax for column salary

   So, the correct way is NEW.salary.

3. Final Answer:

   NEW.salary -> Option A

4. Quick Check:

   Use NEW.column_name to get updated value [OK]

Hint: Use NEW.column_name to get updated column value [OK]

Common Mistakes:

• Using OLD.salary to get new value
• Trying to SELECT from NEW as a table
• Incorrect UPDATE syntax inside trigger

3. Given this trigger code snippet inside an UPDATE trigger:

IF NEW.status = 'active' AND OLD.status = 'inactive' THEN
  INSERT INTO log_table(action) VALUES('Activated');
END IF;

What happens when a row's status changes from 'inactive' to 'active'?

medium

A. No action is taken because OLD and NEW are the same

B. A new row with action 'Activated' is inserted into log_table

C. An error occurs because you cannot compare OLD and NEW

D. The row update is canceled automatically

Solution

1. Step 1: Understand the condition in the trigger

   The condition checks if the new status is 'active' and the old status was 'inactive'.

2. Step 2: Effect of condition being true

   If true, it inserts a row into log_table with action 'Activated'.

3. Final Answer:

   A new row with action 'Activated' is inserted into log_table -> Option B

4. Quick Check:

   Condition true triggers insert [OK]

Hint: Trigger runs code when OLD and NEW differ as condition says [OK]

Common Mistakes:

• Thinking OLD and NEW cannot be compared
• Assuming no insert happens on status change
• Believing update cancels automatically

4. You wrote this trigger code:

CREATE TRIGGER trg_update BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SET NEW.salary = OLD.salary;
  END IF;
END;

But when you try to update salary to a lower value, it still changes. What is the problem?

medium

A. The trigger syntax is missing a delimiter or terminator

B. You cannot assign values to NEW in a BEFORE UPDATE trigger

C. The trigger should be AFTER UPDATE, not BEFORE UPDATE

D. The trigger logic is correct; the issue is elsewhere

Solution

1. Step 1: Check trigger syntax completeness

   In many SQL systems, triggers require a delimiter change to define the body properly.

2. Step 2: Missing delimiter causes trigger body to be incomplete

   Without proper delimiters, the trigger may not compile or behave as expected.

3. Final Answer:

   The trigger syntax is missing a delimiter or terminator -> Option A

4. Quick Check:

   Triggers need delimiters to parse body correctly [OK]

Hint: Check delimiters when trigger body has multiple statements [OK]

Common Mistakes:

• Assuming AFTER triggers can modify NEW
• Thinking NEW cannot be assigned in BEFORE triggers
• Ignoring delimiter requirements in SQL triggers

5. You want to create an UPDATE trigger that prevents lowering a product's price. If the new price is less than the old price, the trigger should reset the price to the old value and log the attempt. Which trigger code correctly implements this?

hard

A. CREATE TRIGGER trg_price_check BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF OLD.price < NEW.price THEN
SET NEW.price = OLD.price;
INSERT INTO price_log(product_id, old_price, attempted_price) VALUES (OLD.id, OLD.price, NEW.price);
END IF;
END;

B. CREATE TRIGGER trg_price_check AFTER UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price < OLD.price THEN
UPDATE products SET price = OLD.price WHERE id = OLD.id;
INSERT INTO price_log(product_id, old_price, attempted_price) VALUES (OLD.id, OLD.price, NEW.price);
END IF;
END;

C. CREATE TRIGGER trg_price_check BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price < OLD.price THEN
INSERT INTO price_log(product_id, old_price, attempted_price) VALUES (OLD.id, OLD.price, NEW.price);
SET NEW.price = OLD.price;
END IF;
END;

D. CREATE TRIGGER trg_price_check BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price > OLD.price THEN
SET NEW.price = OLD.price;
INSERT INTO price_log(product_id, old_price, attempted_price) VALUES (OLD.id, OLD.price, NEW.price);
END IF;
END;

Solution

1. Step 1: Identify correct trigger timing and condition

   Use a BEFORE UPDATE trigger to modify NEW values before update. Condition should check if NEW.price is less than OLD.price.

2. Step 2: Correctly reset price and log attempt

   If condition true, set NEW.price = OLD.price to prevent lowering, then insert a log record with OLD and attempted NEW price.

3. Final Answer:

   BEFORE UPDATE trigger that prevents lowering price and logs the attempt -> Option C

4. Quick Check:

   BEFORE UPDATE + NEW.price < OLD.price + reset + log [OK]

Hint: Use BEFORE UPDATE to modify NEW and log changes [OK]

Common Mistakes:

• Using AFTER UPDATE and trying to modify NEW
• Reversing price comparison logic
• Not logging the attempted price change