Why UPDATE trigger with OLD and NEW in SQL? - Purpose & Use Cases

The Big Idea

What if your database could remember every change for you, perfectly and instantly?

The Scenario

Imagine you have a big notebook where you write down all changes made to your friends' contact details by hand. Every time a phone number changes, you try to remember the old one and write the new one next to it.

The Problem

This manual way is slow and confusing. You might forget the old number, mix up entries, or lose track of who changed what and when. It's hard to keep everything accurate and up-to-date.

The Solution

An UPDATE trigger with OLD and NEW values automatically watches for changes in your data. It remembers the old information and compares it with the new, so you can track updates easily and react to them without mistakes.

Before vs After

✗ Before

Manually check old value, then update and log changes in separate steps.

✓ After

CREATE TRIGGER trg_update BEFORE UPDATE ON contacts FOR EACH ROW BEGIN -- OLD and NEW let you compare and act on changes; END;

What It Enables

This lets your database automatically track and respond to data changes, keeping your information accurate and reliable without extra effort.

Real Life Example

When a customer updates their address, the trigger can save the old address for records and notify the shipping department about the change instantly.

Key Takeaways

Manual tracking of data changes is slow and error-prone.

UPDATE triggers use OLD and NEW to automatically detect and handle changes.

This keeps data accurate and helps automate important follow-up actions.