UPDATE trigger with OLD and NEW in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the OLD and NEW keywords in an UPDATE trigger?

OLD refers to the row data before the update, and NEW refers to the row data after the update. They help compare or use values during the update operation.

intermediate

In an UPDATE trigger, can you modify the NEW values? Why or why not?

Yes, you can modify NEW values in a BEFORE UPDATE trigger to change what will be saved. In AFTER UPDATE triggers, NEW is read-only.

intermediate

Write a simple SQL UPDATE trigger that logs old and new values of a column named 'price'.

CREATE TRIGGER log_price_update BEFORE UPDATE ON products FOR EACH ROW BEGIN INSERT INTO price_log (old_price, new_price) VALUES (OLD.price, NEW.price); END;

beginner

What happens if you try to access OLD in an INSERT trigger?

OLD is not available in INSERT triggers because there is no previous row before insertion.

beginner

Why are UPDATE triggers useful in real-life applications?

They help track changes, enforce rules, or update related data automatically when a row changes, like auditing price changes or updating timestamps.

In an UPDATE trigger, what does NEW represent?

A The row data before the update

B The table schema

C The row data after the update

D The database user

NEW holds the new values that will be saved after the update.

Can you use OLD in an INSERT trigger?

A No, OLD is not available in INSERT triggers

B Only in BEFORE INSERT triggers

C Only if the table has a primary key

D Yes, always

OLD is not available in INSERT triggers because there is no previous row.

Which trigger timing allows you to modify NEW values before they are saved?

A BEFORE UPDATE

B AFTER DELETE

C AFTER UPDATE

D BEFORE DELETE

BEFORE UPDATE triggers let you change NEW values before saving.

What is a common use of UPDATE triggers with OLD and NEW?

A Creating new tables

B Logging changes to data

C Deleting rows automatically

D Changing database users

UPDATE triggers often log old and new values to track changes.

If you want to prevent a price from being set below zero during an update, where would you check NEW.price?

A In a BEFORE DELETE trigger

B In an AFTER UPDATE trigger

C In an AFTER INSERT trigger

D In a BEFORE UPDATE trigger

BEFORE UPDATE triggers let you validate or change NEW values before saving.

Explain how OLD and NEW work in an UPDATE trigger and give a simple example of their use.

Think about what data you have before and after the update.

Describe a real-life scenario where an UPDATE trigger using OLD and NEW would be helpful.

Imagine you want to keep a history of changes automatically.