Utilizing OLD and NEW Pseudo-Records0%

Building an Automated Audit Logging System with Triggers

Utilizing OLD and NEW Pseudo-Records

Advanced14 min read•Updated: 2026-09-12

Utilizing OLD and NEW Pseudo-Records

Inside the body of a trigger, MySQL provides two virtual pseudo-records: OLD and NEW. These records represent the state of the row before and after the triggering DML modification.

Understanding the availability of OLD and NEW across different DML events is crucial for data auditing and integrity enforcement.

Availability Matrix: OLD vs NEW

Event	`OLD` Available?	`NEW` Available?	Modifiable via `SET`?
`INSERT`	No (Row didn't exist prior)	Yes (Represents new incoming row)	Yes (in `BEFORE INSERT`)
`UPDATE`	Yes (Represents values before edit)	Yes (Represents edited values)	Yes (in `BEFORE UPDATE`)
`DELETE`	Yes (Represents values being removed)	No (Row is deleted; no new state)	No (`OLD` is strictly read-only)

Modifying Column Values with `SET NEW.col`

In BEFORE triggers, you can overwrite the values that will be written to disk:

1DELIMITER //
2 
3CREATE TRIGGER trg_enforce_minimum_salary
4BEFORE INSERT ON employees
5FOR EACH ROW
6BEGIN
7    -- Prevent salaries below company minimum wage ($30,000)
8    IF NEW.salary < 30000.00 THEN
9        SET NEW.salary = 30000.00;
10    END IF;
11END //
12 
13DELIMITER ;

Rule: You can only assign values to NEW columns in BEFORE triggers. In AFTER triggers, the row has already been committed to storage, so NEW becomes read-only!

Enforcing Strict Business Rules (BEFORE UPDATE)

Suppose management mandates that no employee's salary can ever be decreased, and single raises cannot exceed 20%:

1DELIMITER //
2 
3CREATE TRIGGER trg_validate_salary_changes
4BEFORE UPDATE ON employees
5FOR EACH ROW
6BEGIN
7    -- 1. Check if salary is being decreased
8    IF NEW.salary < OLD.salary THEN
9        SIGNAL SQLSTATE '45000'
10            SET MESSAGE_TEXT = 'Salary reduction policy violation: Salaries cannot be decreased!';
11    END IF;
12 
13    -- 2. Check if raise exceeds 20%
14    IF NEW.salary > (OLD.salary * 1.20) THEN
15        SIGNAL SQLSTATE '45000'
16            SET MESSAGE_TEXT = 'Salary raise cap exceeded: Maximum allowable raise is 20%!';
17    END IF;
18END //
19 
20DELIMITER ;

Capturing State Changes in History Tables

By comparing OLD.col with NEW.col, you can determine precisely which columns were modified during an UPDATE:

1IF OLD.status != NEW.status THEN
2    INSERT INTO status_history (order_id, old_status, new_status, changed_at)
3    VALUES (NEW.order_id, OLD.status, NEW.status, NOW());
4END IF;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which pseudo-record is available during an INSERT trigger?

Question 2

Can you modify a column value using SET NEW.column = value in an AFTER INSERT trigger?

Question 3

Which pseudo-record represents the row being deleted in a DELETE trigger?

Question 4

How can a trigger detect if a customer's email address specifically changed during an UPDATE?

Question 5

What happens if a BEFORE UPDATE trigger executes a SIGNAL SQLSTATE statement?

Next Lesson

Building an Automated Audit Logging System with Triggers

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Trigger Fundamentals & Event Hooks	Building an Automated Audit Logging System with Triggers

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Building an Automated Audit Logging System with Triggers

Utilizing OLD and NEW Pseudo-Records

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Utilizing OLD and NEW Pseudo-Records

Inside the body of a trigger, MySQL provides two virtual pseudo-records: OLD and NEW. These records represent the state of the row before and after the triggering DML modification.

Understanding the availability of OLD and NEW across different DML events is crucial for data auditing and integrity enforcement.

Availability Matrix: OLD vs NEW

Event	`OLD` Available?	`NEW` Available?	Modifiable via `SET`?
`INSERT`	No (Row didn't exist prior)	Yes (Represents new incoming row)	Yes (in `BEFORE INSERT`)
`UPDATE`	Yes (Represents values before edit)	Yes (Represents edited values)	Yes (in `BEFORE UPDATE`)
`DELETE`	Yes (Represents values being removed)	No (Row is deleted; no new state)	No (`OLD` is strictly read-only)

Modifying Column Values with `SET NEW.col`

In BEFORE triggers, you can overwrite the values that will be written to disk:

1DELIMITER //
2 
3CREATE TRIGGER trg_enforce_minimum_salary
4BEFORE INSERT ON employees
5FOR EACH ROW
6BEGIN
7    -- Prevent salaries below company minimum wage ($30,000)
8    IF NEW.salary < 30000.00 THEN
9        SET NEW.salary = 30000.00;
10    END IF;
11END //
12 
13DELIMITER ;

Rule: You can only assign values to NEW columns in BEFORE triggers. In AFTER triggers, the row has already been committed to storage, so NEW becomes read-only!

Enforcing Strict Business Rules (BEFORE UPDATE)

Suppose management mandates that no employee's salary can ever be decreased, and single raises cannot exceed 20%:

1DELIMITER //
2 
3CREATE TRIGGER trg_validate_salary_changes
4BEFORE UPDATE ON employees
5FOR EACH ROW
6BEGIN
7    -- 1. Check if salary is being decreased
8    IF NEW.salary < OLD.salary THEN
9        SIGNAL SQLSTATE '45000'
10            SET MESSAGE_TEXT = 'Salary reduction policy violation: Salaries cannot be decreased!';
11    END IF;
12 
13    -- 2. Check if raise exceeds 20%
14    IF NEW.salary > (OLD.salary * 1.20) THEN
15        SIGNAL SQLSTATE '45000'
16            SET MESSAGE_TEXT = 'Salary raise cap exceeded: Maximum allowable raise is 20%!';
17    END IF;
18END //
19 
20DELIMITER ;

Capturing State Changes in History Tables

By comparing OLD.col with NEW.col, you can determine precisely which columns were modified during an UPDATE:

1IF OLD.status != NEW.status THEN
2    INSERT INTO status_history (order_id, old_status, new_status, changed_at)
3    VALUES (NEW.order_id, OLD.status, NEW.status, NOW());
4END IF;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which pseudo-record is available during an INSERT trigger?

Question 2

Can you modify a column value using SET NEW.column = value in an AFTER INSERT trigger?

Question 3

Which pseudo-record represents the row being deleted in a DELETE trigger?

Question 4

How can a trigger detect if a customer's email address specifically changed during an UPDATE?

Question 5

What happens if a BEFORE UPDATE trigger executes a SIGNAL SQLSTATE statement?

Next Lesson

Building an Automated Audit Logging System with Triggers

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Trigger Fundamentals & Event Hooks	Building an Automated Audit Logging System with Triggers

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Utilizing OLD and NEW Pseudo-Records

Availability Matrix: OLD vs NEW

Modifying Column Values with SET NEW.col

Enforcing Strict Business Rules (BEFORE UPDATE)

Capturing State Changes in History Tables

Multiple Choice Questions

Building an Automated Audit Logging System with Triggers

Related Lessons

Practice Quiz

Loading Page Content...

Utilizing OLD and NEW Pseudo-Records

Availability Matrix: OLD vs NEW

Modifying Column Values with SET NEW.col

Enforcing Strict Business Rules (BEFORE UPDATE)

Capturing State Changes in History Tables

Multiple Choice Questions

Building an Automated Audit Logging System with Triggers

Related Lessons

Practice Quiz

Modifying Column Values with `SET NEW.col`

Modifying Column Values with `SET NEW.col`