> This page location: PostgreSQL Triggers > BEFORE UPDATE Trigger
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL BEFORE UPDATE Trigger

**Info:** BEFORE UPDATE triggers work the same way on any PostgreSQL database, so you can apply this tutorial wherever you run Postgres. If you're an enterprise looking for managed Postgres built for the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers high performance, strong security, and native integration with the Lakehouse. If you're a developer or startup who needs to ship and scale quickly, [Neon](https://neon.com) gives you the fastest path from idea to production on Postgres.

**Summary**: in this tutorial, you will learn how to define a PostgreSQL `BEFORE UPDATE` trigger that executes a function before an update event occurs.

## Introduction to the PostgreSQL BEFORE UPDATE trigger

In PostgreSQL, a trigger is a database object that is automatically activated when an event such as [`INSERT`](../postgresql-tutorial/postgresql-insert), [`UPDATE`](../postgresql-tutorial/postgresql-update), [`DELETE`](../postgresql-tutorial/postgresql-delete), or [`TRUNCATE`](../postgresql-tutorial/postgresql-truncate-table) occurs on the associated table.

A `BEFORE UPDATE` trigger is a type of trigger that activates before an `UPDATE` operation is applied to a table.

These `BEFORE UPDATE` triggers can be particularly useful when you want to modify data before an update occurs or enforce certain conditions.

In a `BEFORE UPDATE` trigger, you can access the following variables:

- `OLD`: This record variable allows you to access the row before the update.
- `NEW`: This record variable represents the row after the update.

Also, you can access the following variables:

- `TG_NAME`: Represent the name of the trigger.
- `TG_OP`: Represent the operation that activates the trigger, which is `UPDATE` for the `BEFORE UPDATE` triggers.
- `TG_WHEN`: Represent the trigger timing, which is `BEFORE` for the `BEFORE UPDATE` triggers.

To create a `BEFORE UPDATE` trigger, you follow these steps:

First, [define a trigger function](../postgresql-plpgsql/postgresql-create-function) that will execute when the `BEFORE UPDATE` trigger fires:

```plsql
CREATE OR REPLACE FUNCTION trigger_function()
   RETURNS TRIGGER
   LANGUAGE PLPGSQL
AS
$$
BEGIN
   -- trigger logic
   -- ...
   RETURN NEW;
END;
$$
```

Second, create a `BEFORE UPDATE` trigger that executes the defined function:

```sql
CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name
FOR EACH {ROW | STATEMENT}
EXECUTE FUNCTION trigger_function();
```

## PostgreSQL BEFORE UPDATE trigger example

First, [create a new table](../postgresql-tutorial/postgresql-create-table) called `employees` to store the employee data:

```sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC NOT NULL
);
```

Next, define a trigger function that [raises an exception](../postgresql-plpgsql/postgresql-exception) if the new salary is lower than the current salary. The trigger will prevent the update when the exception occurs.

```plsql
CREATE OR REPLACE FUNCTION fn_before_update_salary()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < OLD.salary THEN
        RAISE EXCEPTION 'New salary cannot be less than current salary';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```

Then, create a `BEFORE UPDATE` trigger that executes the `fn_before_update_salary()` before the update:

```sql
CREATE TRIGGER before_update_salary_trigger
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_before_update_salary();
```

This `BEFORE UPDATE` trigger ensures that the salary of the employee cannot be decreased. If you attempt to reduce the salary, the trigger will raise an exception and abort the update.

After that, [insert some rows](../postgresql-tutorial/postgresql-insert-multiple-rows) into the `employees` table:

```sql
INSERT INTO employees(name, salary)
VALUES
   ('John Doe', 70000),
   ('Jane Doe', 80000)
RETURNING *;
```

Output:

```text
 id |   name   | salary
----+----------+--------
  1 | John Doe |  70000
  2 | Jane Doe |  80000
(2 rows)
```

Finally, attempt to decrease the salary of `John Doe`:

```sql
UPDATE employees
SET salary = salary * 0.9
WHERE id = 1;
```

The `BEFORE UPDATE` trigger raises the following exception:

```
ERROR:  New salary cannot be less than current salary
CONTEXT:  PL/pgSQL function fn_before_update_salary() line 4 at RAISE
```

## Summary

- Use a `BEFORE UPDATE` trigger to automatically execute a function before an update.

---

## Related docs (PostgreSQL Triggers)

- [Introduction](https://neon.com/postgresql/triggers/introduction-postgresql-trigger)
- [CREATE TRIGGER](https://neon.com/postgresql/triggers/creating-first-trigger-postgresql)
- [DROP TRIGGER](https://neon.com/postgresql/triggers/drop-trigger)
- [ALTER TRIGGER](https://neon.com/postgresql/triggers/alter-trigger)
- [AFTER INSERT Trigger](https://neon.com/postgresql/triggers/after-insert-trigger)
- [BEFORE INSERT Trigger](https://neon.com/postgresql/triggers/before-insert-trigger)
- [AFTER UPDATE Trigger](https://neon.com/postgresql/triggers/after-update-trigger)
- [BEFORE DELETE Trigger](https://neon.com/postgresql/triggers/before-delete-trigger)
- [AFTER DELETE Trigger](https://neon.com/postgresql/triggers/after-delete-trigger)
- [INSTEAD OF Triggers](https://neon.com/postgresql/triggers/instead-of-triggers)
- [BEFORE TRUNCATE Trigger](https://neon.com/postgresql/triggers/before-truncate-trigger)
- [Disable Triggers](https://neon.com/postgresql/triggers/managing-postgresql-trigger)
- [Enable Triggers](https://neon.com/postgresql/triggers/enable-triggers)
- [List All Triggers](https://neon.com/postgresql/triggers/how-to-list-all-triggers-in-postgresql)
- [Event Triggers](https://neon.com/postgresql/triggers/event-trigger)
- [Conditional Triggers](https://neon.com/postgresql/triggers/trigger-when-condition)