How to Use NEW and OLD in Trigger in PostgreSQL

In PostgreSQL triggers, OLD refers to the row data before an update or delete, and NEW refers to the row data after an insert or update. You use OLD and NEW inside trigger functions to access or modify row values during the trigger execution.

Syntax

In a PostgreSQL trigger function, OLD and NEW are special records representing the row before and after the triggering event.

• OLD: Available in UPDATE and DELETE triggers; holds the row before the change.
• NEW: Available in INSERT and UPDATE triggers; holds the row after the change.

You access columns using dot notation, like OLD.column_name or NEW.column_name.

sql

CREATE OR REPLACE FUNCTION trigger_function_example()
RETURNS trigger AS $$
BEGIN
  -- Access OLD and NEW records
  IF TG_OP = 'UPDATE' THEN
    RAISE NOTICE 'Old value: %, New value: %', OLD.column_name, NEW.column_name;
  ELSIF TG_OP = 'INSERT' THEN
    RAISE NOTICE 'New value: %', NEW.column_name;
  ELSIF TG_OP = 'DELETE' THEN
    RAISE NOTICE 'Deleted value: %', OLD.column_name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Example

This example shows a trigger that logs changes to a table using OLD and NEW. It prints old and new values on update, new values on insert, and old values on delete.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  salary INT
);

CREATE OR REPLACE FUNCTION log_employee_changes()
RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'UPDATE' THEN
    RAISE NOTICE 'Updated employee %: salary changed from % to %', OLD.id, OLD.salary, NEW.salary;
    RETURN NEW;
  ELSIF TG_OP = 'INSERT' THEN
    RAISE NOTICE 'Inserted employee % with salary %', NEW.id, NEW.salary;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    RAISE NOTICE 'Deleted employee % with salary %', OLD.id, OLD.salary;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employee_changes_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION log_employee_changes();

-- Test inserts and updates
INSERT INTO employees (name, salary) VALUES ('Alice', 50000);
UPDATE employees SET salary = 55000 WHERE name = 'Alice';
DELETE FROM employees WHERE name = 'Alice';

Output

NOTICE:  Inserted employee 1 with salary 50000
NOTICE:  Updated employee 1: salary changed from 50000 to 55000
NOTICE:  Deleted employee 1 with salary 55000

Common Pitfalls

• Trying to use NEW in a DELETE trigger or OLD in an INSERT trigger causes errors because those records do not exist.
• For BEFORE triggers, you must return NEW to apply changes or NULL to skip the operation.
• For AFTER triggers, the return value is ignored but still required.
• Not specifying FOR EACH ROW means you cannot use OLD or NEW because the trigger is statement-level.

sql

/* Wrong: Using NEW in DELETE trigger */
CREATE OR REPLACE FUNCTION wrong_delete_trigger()
RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Trying to access NEW in DELETE: %', NEW.id; -- ERROR
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

/* Correct: Use OLD in DELETE trigger */
CREATE OR REPLACE FUNCTION correct_delete_trigger()
RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Accessing OLD in DELETE: %', OLD.id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Trigger Event	OLD Available	NEW Available	Return Value in BEFORE Trigger
INSERT	No	Yes	Return NEW to insert row
UPDATE	Yes	Yes	Return NEW to update row
DELETE	Yes	No	Return OLD to delete row

Key Takeaways

Use OLD to access the row before update or delete events in triggers.

Use NEW to access the row after insert or update events in triggers.

Return NEW in BEFORE INSERT/UPDATE triggers to apply changes; return OLD in BEFORE DELETE triggers.

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

Triggers must be defined FOR EACH ROW to use OLD and NEW records.