UPDATE trigger with OLD and NEW in SQL

Introduction

An UPDATE trigger runs automatically when data in a table changes. OLD and NEW let you see the data before and after the change.

You want to log changes made to a table for tracking.

You need to check or modify data right after it is updated.

You want to prevent certain updates based on old or new values.

You want to keep related tables in sync when data changes.

You want to audit who changed what and when.

Syntax

SQL

CREATE TRIGGER trigger_name
BEFORE|AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
  -- use OLD.column_name for old data
  -- use NEW.column_name for new data
  -- trigger logic here
END;

OLD refers to the row before the update.

NEW refers to the row after the update.

Examples

This trigger logs salary changes after an employee record is updated.

SQL

CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(employee_id, old_salary, new_salary)
  VALUES (OLD.id, OLD.salary, NEW.salary);
END;

This trigger stops updates that try to lower an employee's salary.

SQL

CREATE TRIGGER prevent_salary_drop
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be decreased';
  END IF;
END;

Sample Program

This example creates two tables: employees and audit_log. It adds a trigger that logs salary changes after updates. Then it inserts one employee and updates their salary. Finally, it shows the audit log with the change.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT
);

CREATE TABLE audit_log (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT,
  old_salary INT,
  new_salary INT
);

DELIMITER $$
CREATE TRIGGER log_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(employee_id, old_salary, new_salary)
  VALUES (OLD.id, OLD.salary, NEW.salary);
END$$
DELIMITER ;

INSERT INTO employees VALUES (1, 'Alice', 5000);
UPDATE employees SET salary = 5500 WHERE id = 1;
SELECT * FROM audit_log;

Important Notes

Use BEFORE UPDATE if you want to change NEW values before they are saved.

Use AFTER UPDATE if you want to react after the update is done.

Not all databases support SIGNAL for errors; check your system.

Summary

UPDATE triggers run when data changes in a table.

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

Use triggers to log, check, or modify data during updates.