UPDATE trigger with OLD and NEW in SQL - Deep Dive

Overview - UPDATE trigger with OLD and NEW

What is it?

An UPDATE trigger is a special kind of database procedure that runs automatically when a row in a table is changed. It uses OLD and NEW references to access the data before and after the update. OLD holds the original row values, and NEW holds the new values being saved. This helps you check or change data during updates.

Why it matters

Without UPDATE triggers using OLD and NEW, you would have to manually check and fix data inconsistencies after changes. This can lead to errors and slow systems. Triggers automate data validation and auditing, making databases safer and more reliable. They help keep data clean and enforce rules without extra application code.

Where it fits

Before learning UPDATE triggers, you should understand basic SQL commands like SELECT, INSERT, UPDATE, and DELETE. After this, you can explore DELETE triggers and advanced trigger features like INSTEAD OF triggers or trigger chaining. This fits into the broader topic of database automation and integrity.

Mental Model

Core Idea

An UPDATE trigger uses OLD and NEW to compare and control data changes automatically when a row is updated.

Think of it like...

Imagine you are editing a document. OLD is the original text before you start editing, and NEW is the text after your changes. The trigger is like a smart assistant who watches your edits and can stop or fix mistakes before you save.

┌───────────────┐       UPDATE       ┌───────────────┐
│   OLD values  │ ───────────────▶ │   NEW values  │
└───────────────┘                  └───────────────┘
         ▲                                │
         │                                ▼
    Trigger fires automatically and can:
    - Compare OLD and NEW
    - Modify NEW
    - Reject update
    - Log changes

Build-Up - 7 Steps

1

Foundation

What is a database trigger?

Concept: Triggers are automatic actions in a database that run when data changes.

A trigger is like a rule that the database follows. For example, when you add, change, or delete data, the trigger can run code to check or change things automatically. This helps keep data correct without needing to write extra code every time.

Result

You understand that triggers automate responses to data changes.

Understanding triggers as automatic rules helps you see how databases enforce data rules without manual checks.

2

Foundation

Basics of UPDATE statements

Concept: UPDATE changes existing data in a table.

The UPDATE command changes one or more rows in a table. For example, UPDATE employees SET salary = 5000 WHERE id = 1 changes the salary of the employee with id 1. This is the event that can activate an UPDATE trigger.

Result

You know how data changes happen that triggers respond to.

Knowing how UPDATE works is essential because triggers react to these changes.

3

Intermediate

OLD and NEW in UPDATE triggers

🤔Before reading on: do you think OLD and NEW hold the same data or different data during an UPDATE? Commit to your answer.

Concept: OLD holds the data before update; NEW holds the data after update.

When an UPDATE trigger runs, OLD contains the row's original values before the change. NEW contains the values that will be saved after the update. You can use these to compare what changed or to modify the new data before saving.

Result

You can access both old and new versions of data during an update.

Knowing OLD and NEW lets you control or audit exactly what changes in each update.

4

Intermediate

Writing a simple UPDATE trigger

🤔Before reading on: do you think you can change NEW values inside a trigger to modify the update? Commit to your answer.

Concept: You can write triggers that check or change NEW values before the update finishes.

Example in SQL: CREATE TRIGGER check_salary BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF NEW.salary < 0 THEN SET NEW.salary = 0; END IF; END; This trigger sets salary to 0 if someone tries to update it to a negative number.

Result

The database prevents negative salaries by changing the NEW value before saving.

Modifying NEW inside triggers enforces data rules automatically.

5

Intermediate

Using OLD and NEW for auditing changes

Concept: Triggers can log what changed by comparing OLD and NEW.

You can create a trigger that writes a record to an audit table whenever a row changes. It can store OLD and NEW values to track history. Example: CREATE TRIGGER audit_update AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO audit_log(emp_id, old_salary, new_salary, changed_at) VALUES (OLD.id, OLD.salary, NEW.salary, NOW()); END;

Result

Every update is recorded with old and new values for review.

Using OLD and NEW for auditing helps track data changes over time for accountability.

6

Advanced

BEFORE vs AFTER UPDATE triggers

🤔Before reading on: do you think BEFORE triggers can modify NEW values, or only AFTER triggers can? Commit to your answer.

Concept: BEFORE triggers can change NEW values; AFTER triggers cannot but can react after update.

BEFORE UPDATE triggers run before the data is saved, so they can modify NEW values to change what gets stored. AFTER UPDATE triggers run after the data is saved, so they can only read OLD and NEW but not change the data. This difference affects what you can do in each trigger type.

Result

You understand when and how you can modify data during updates.

Knowing the timing of triggers is key to choosing the right place to enforce rules or log changes.

7

Expert

Trigger side effects and recursion risks

🤔Before reading on: do you think an UPDATE trigger that changes NEW values can cause itself to run again? Commit to your answer.

Concept: Triggers that modify data can cause recursive calls if not carefully controlled.

If a trigger updates the same table it listens to, it can cause itself to run repeatedly, leading to infinite loops or stack overflow errors. Databases often limit recursion depth or require careful logic to avoid this. For example, using flags or conditions inside triggers to prevent repeated updates.

Result

You learn to avoid dangerous trigger recursion and infinite loops.

Understanding trigger recursion risks prevents serious bugs and performance problems in production.

Under the Hood

When an UPDATE command runs, the database engine first fetches the current row (OLD). It then prepares the new data (NEW). Before saving, it checks for any BEFORE UPDATE triggers and runs their code, allowing modification of NEW. After saving, it runs AFTER UPDATE triggers. OLD and NEW are stored in memory for the trigger code to access. This process ensures data integrity and automation.

Why designed this way?

Triggers were designed to automate data checks and actions close to the data itself, reducing errors and duplication. OLD and NEW references provide a clear way to see what changed. Separating BEFORE and AFTER triggers allows flexible control over when changes happen and what can be modified. This design balances power with safety.

┌───────────────┐
│   UPDATE cmd  │
└──────┬────────┘
       │ fetch OLD
       ▼
┌───────────────┐
│   OLD values  │
└──────┬────────┘
       │ prepare NEW
       ▼
┌───────────────┐
│   NEW values  │
└──────┬────────┘
       │ run BEFORE UPDATE triggers (can modify NEW)
       ▼
┌───────────────┐
│ Save NEW data │
└──────┬────────┘
       │ run AFTER UPDATE triggers (read-only)
       ▼
┌───────────────┐
│  Commit done  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can AFTER UPDATE triggers modify the data being saved? Commit yes or no.

Common Belief: AFTER UPDATE triggers can change the data after it is saved.

Reality: AFTER UPDATE triggers run after the data is saved and cannot modify the data being updated.

Why it matters: Trying to change data in AFTER triggers leads to confusion and bugs because changes won't be saved.

Quick: Does OLD always contain the same data as NEW in an UPDATE trigger? Commit yes or no.

Common Belief: OLD and NEW always have the same values during an UPDATE trigger.

Reality: OLD has the original data before update; NEW has the new data that will be saved, which can be different.

Why it matters: Assuming they are the same prevents detecting changes or enforcing rules based on differences.

Quick: Can an UPDATE trigger cause itself to run again automatically? Commit yes or no.

Common Belief: Triggers cannot cause themselves to run again, so no risk of loops.

Reality: If a trigger updates the same table, it can cause recursive calls unless controlled.

Why it matters: Ignoring recursion risks can crash the database or cause performance issues.

Quick: Are triggers a replacement for all application-level data validation? Commit yes or no.

Common Belief: Triggers can replace all data validation done in applications.

Reality: Triggers help enforce rules at the database level but cannot replace all application logic or user interface checks.

Why it matters: Relying only on triggers can lead to poor user experience or missed validations.

Expert Zone

1

Triggers run per row or per statement; understanding this affects performance and logic.

2

Some databases allow modifying NEW only in BEFORE triggers, not AFTER; knowing this avoids errors.

3

Trigger execution order matters when multiple triggers exist on the same event; this can affect outcomes.

When NOT to use

Avoid using UPDATE triggers for complex business logic better handled in application code or stored procedures. Also, do not use triggers to perform heavy computations or external calls, as this can slow down database operations.

Production Patterns

Common patterns include auditing changes by logging OLD and NEW values, enforcing data constraints like non-negative values, and synchronizing related tables. Experts also use triggers to maintain derived data or enforce security policies transparently.

Connections

Event-driven programming

UPDATE triggers are a form of event-driven code that runs in response to data changes.

Understanding triggers as event handlers helps connect database automation to broader programming patterns.

Version control systems

Triggers that log OLD and NEW values create a history similar to commits in version control.

Seeing database auditing as version control clarifies how data changes can be tracked and reverted.

Legal contracts

Triggers enforce rules automatically like contract clauses that activate on certain conditions.

Recognizing triggers as automatic contract enforcers helps appreciate their role in maintaining data agreements.

Common Pitfalls

#1 Trying to modify NEW values inside an AFTER UPDATE trigger.

Wrong approach:

CREATE TRIGGER wrong_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
SET NEW.salary = 1000;
END;

Correct approach:

CREATE TRIGGER correct_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
SET NEW.salary = 1000;
END;

Root cause:

Misunderstanding that AFTER triggers cannot change data being saved.

#2 Not preventing recursive updates inside triggers.

Wrong approach:

CREATE TRIGGER recursive_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
UPDATE employees SET salary = salary + 1 WHERE id = NEW.id;
END;

Correct approach:

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

Root cause:

Failing to add conditions to stop triggers from causing themselves to run repeatedly.

#3 Assuming OLD and NEW are always identical in UPDATE triggers.

Wrong approach:

CREATE TRIGGER check_no_change
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary = NEW.salary THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No change detected';
END IF;
END;

Correct approach:

CREATE TRIGGER check_change
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
-- proceed
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No change detected';
END IF;
END;

Root cause:

Confusing equality and inequality operators or logic in triggers.

Key Takeaways

UPDATE triggers run automatically when data changes, using OLD and NEW to access before and after values.

OLD holds the original row data; NEW holds the new data to be saved, allowing comparison and modification.

BEFORE UPDATE triggers can modify NEW values; AFTER UPDATE triggers cannot change data but can react to changes.

Triggers help enforce data rules, audit changes, and automate database integrity without extra application code.

Careful design is needed to avoid trigger recursion and performance issues in production systems.