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.

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