How to Use NEW and OLD in MySQL Triggers

In MySQL triggers, OLD refers to the row data before an update or delete, while NEW refers to the new row data for insert or update operations. Use OLD.column_name and NEW.column_name inside triggers to access these values for conditional logic or data manipulation.

Syntax

In MySQL triggers, OLD and NEW are aliases to access row data. OLD is used in UPDATE and DELETE triggers to refer to the existing row before the change. NEW is used in INSERT and UPDATE triggers to refer to the new row data being inserted or updated.

You write them as OLD.column_name or NEW.column_name inside the trigger body.

sql

CREATE TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
BEGIN
  -- Use OLD.column_name for old values (UPDATE, DELETE)
  -- Use NEW.column_name for new values (INSERT, UPDATE)
END;

Example

This example shows a trigger that logs changes to a users table. It uses OLD to get the old email before update and NEW to get the new email after update.

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100)
);

CREATE TABLE email_changes (
  user_id INT,
  old_email VARCHAR(100),
  new_email VARCHAR(100),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.email <> NEW.email THEN
    INSERT INTO email_changes(user_id, old_email, new_email)
    VALUES (OLD.id, OLD.email, NEW.email);
  END IF;
END$$
DELIMITER ;

-- Insert a user
INSERT INTO users VALUES (1, '[e-mail]');

-- Update the user's email
UPDATE users SET email = '[e-mail]' WHERE id = 1;

-- Check the email_changes log
SELECT * FROM email_changes;

Output

user_id | old_email       | new_email       | changed_at
--------|-----------------|-----------------|---------------------
1       | [e-mail] | [e-mail] | 2024-06-01 12:00:00

Common Pitfalls

• Trying to use NEW in a DELETE trigger causes an error because NEW does not exist for deletes.
• Using OLD in an INSERT trigger is invalid because there is no old row.
• For UPDATE triggers, both OLD and NEW are available, but you must be careful to compare values correctly.
• Remember to use DELIMITER to define triggers properly in MySQL clients.

sql

/* Wrong: Using NEW in DELETE trigger */
DELIMITER $$
CREATE TRIGGER wrong_delete_trigger
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  SET @email = NEW.email; -- ERROR: NEW not available in DELETE
END$$
DELIMITER ;

/* Correct: Use OLD in DELETE trigger */
DELIMITER $$
CREATE TRIGGER correct_delete_trigger
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  SET @email = OLD.email; -- Correct usage
END$$
DELIMITER ;

Quick Reference

Trigger Type	OLD Available	NEW Available	Usage Example
INSERT	No	Yes	Use NEW.column_name to access inserted values
UPDATE	Yes	Yes	Use OLD.column_name for old values, NEW.column_name for new values
DELETE	Yes	No	Use OLD.column_name to access deleted row values

Key Takeaways

Use OLD to access existing row data before UPDATE or DELETE operations in triggers.

Use NEW to access new row data for INSERT or UPDATE operations in triggers.

OLD is not available in INSERT triggers; NEW is not available in DELETE triggers.

Always compare OLD and NEW values carefully in UPDATE triggers to detect changes.

Use DELIMITER command to define triggers correctly in MySQL.