> This page location: PostgreSQL Triggers > INSTEAD OF Triggers
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL INSTEAD OF Triggers

**Info:** INSTEAD OF triggers work the same way on any PostgreSQL database, so you can apply these patterns wherever your Postgres runs. If you're an enterprise looking for managed Postgres in the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers fast, secure Postgres fully integrated with the Lakehouse. If you're a developer or startup who needs to ship and scale quickly, [Neon](https://neon.com) is the Postgres platform built for that pace.

**Summary**: in this tutorial, you will learn about PostgreSQL `INSTEAD OF` a trigger to insert, update, or delete data of base tables through a view.

## Introduction to PostgreSQL INSTEAD OF triggers

In PostgreSQL, `INSTEAD OF` triggers are a special type of triggers that **intercept** insert, update, and delete operations on views.

It means that when you execute an [`INSERT`](../postgresql-tutorial/postgresql-insert), [`UPDATE`](../postgresql-tutorial/postgresql-update), or [`DELETE`](../postgresql-tutorial/postgresql-delete) statement on a view, PostgreSQL does not directly execute the statement. Instead, it executes the statements defined in the `INSTEAD OF` trigger.

To create an `INSTEAD OF` trigger, you follow these steps:

First, [define a function](../postgresql-plpgsql/postgresql-create-function) that will execute when a trigger is fired:

```plsql
CREATE OR REPLACE FUNCTION fn_trigger()
RETURNS TRIGGER AS
$$
   -- function body
$$
LANGUAGE plpgsql;
```

Inside the function, you can customize the behavior for each operation including `INSERT`, `UPDATE`, and `DELETE`.

Second, create an `INSTEAD OF` trigger and bind the function to it:

```sql
CREATE TRIGGER trigger_name
INSTEAD OF INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH ROW
EXECUTE FUNCTION fn_trigger;
```

## PostgreSQL INSTEAD OF trigger example

Let's take an example of creating an `INSTEAD OF` trigger.

### 1) Setting up a view with an INSTEAD OF trigger

First, [create two tables](../postgresql-tutorial/postgresql-create-table) `employees` and `salaries`:

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

CREATE TABLE salaries (
    employee_id INT,
    effective_date DATE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL DEFAULT 0,
    PRIMARY KEY (employee_id, effective_date),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
);
```

Next, [insert rows](../postgresql-tutorial/postgresql-insert) into the `employees` and `salaries` tables:

```sql
INSERT INTO employees (name)
VALUES
   ('Alice'),
   ('Bob')
RETURNING *;

INSERT INTO salaries
VALUES
   (1, '2024-03-01', 60000.00),
   (2, '2024-03-01', 70000.00)
RETURNING *;
```

Then, [create a view](../postgresql-views/managing-postgresql-views) based on the `employees` and `salaries` tables:

```sql
CREATE VIEW employee_salaries
AS
SELECT e.employee_id, e.name, s.salary, s.effective_date
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id;
```

After that, create a function that will execute when the `INSTEAD` `OF` trigger associated with the view activates:

```plsql
CREATE OR REPLACE FUNCTION update_employee_salaries()
RETURNS TRIGGER AS
$$
DECLARE
    p_employee_id INT;
BEGIN
    IF TG_OP = 'INSERT' THEN
	-- insert a new employee
        INSERT INTO employees(name)
        VALUES (NEW.name)
	RETURNING employee_id INTO p_employee_id;

	-- insert salary for the employee
        INSERT INTO salaries(employee_id, effective_date, salary)
	VALUES (p_employee_id, NEW.effective_date, NEW.salary);
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE salaries
	SET salary = NEW.salary
	WHERE employee_id = NEW.employee_id;

    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM employees
	WHERE employee_id = OLD.employee_id;
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
```

If you execute an insert against the `employee_salaries` view, the `INSTEAD OF` trigger will insert a new row into the employees table first, then insert a new row into the salaries table.

When you update an employee's salary by id, the `INSTEAD OF` trigger will update the data in the `salaries` table.

If you delete a row from the `employee_salaries` view, the `INSTEAD OF` trigger will delete a row from the `employees` table. The `DELETE CASCADE` will automatically delete a corresponding row from the salaries table.

Finally, create an `INSTEAD OF` trigger that will be fired for the `INSERT`, `UPDATE`, or `DELETE` on the `employee_salaries` view:

```sql
CREATE TRIGGER instead_of_employee_salaries
INSTEAD OF INSERT OR UPDATE OR DELETE
ON employee_salaries
FOR EACH ROW
EXECUTE FUNCTION update_employee_salaries();
```

### 1) Inserting data into tables via the view

First, insert a new employee with a salary via the view:

```sql
INSERT INTO employee_salaries (name, salary, effective_date)
VALUES ('Charlie', 75000.00, '2024-03-01');
```

PostgreSQL does not execute this statement. Instead, it executes the statement defined in the `INSTEAD` `OF` trigger. More specifically, it executes two statements:

1) Insert a new row into the `employees` table and get the employee id:

```sql
INSERT INTO employees(name)
VALUES (NEW.name)
RETURNING employee_id INTO p_employee_id;
```

2) Insert a new row into the salaries table using the employee id, salary, and effective date:

```sql
INSERT INTO salaries(employee_id, effective_date, salary)
VALUES (p_employee_id, NEW.effective_date, NEW.salary);
```

Second, verify the inserts by retrieving data from the `employees` and `salaries` tables:

```sql
SELECT * FROM employees;
```

Output:

```text
 employee_id |  name
-------------+---------
           1 | Alice
           2 | Bob
           3 | Charlie
(3 rows)
```

```sql
SELECT * FROM salaries;
```

Output:

```text
 employee_id | effective_date |  salary
-------------+----------------+----------
           1 | 2024-03-01     | 60000.00
           2 | 2024-03-01     | 70000.00
           3 | 2024-03-01     | 75000.00
(3 rows)
```

### 2) Updating data into tables via the view

First, update the salary of the employee id 3 via the `employee_salaries` view:

```sql
UPDATE employee_salaries
SET salary = 95000
WHERE employee_id = 3;
```

Second, retrieve data from the `salaries` table:

```sql
SELECT * FROM salaries;
```

Output:

```text
 employee_id | effective_date |  salary
-------------+----------------+----------
           1 | 2024-03-01     | 60000.00
           2 | 2024-03-01     | 70000.00
           3 | 2024-03-01     | 95000.00
(3 rows)
```

### 3) Deleting data via views

First, delete the employee with id 3 via the `employee_salaries` view:

```sql
DELETE FROM employee_salaries
WHERE employee_id = 3;
```

Second, retrieve data from the `employees` table:

```sql
SELECT * FROM employees;
```

Output:

```text
 employee_id | effective_date |  salary
-------------+----------------+----------
           1 | 2024-03-01     | 60000.00
           2 | 2024-03-01     | 70000.00
(2 rows)
```

Because of the `DELETE` `CASCADE`, PostgreSQL also deletes the corresponding row in the `salaries` table:

```sql
SELECT * FROM salaries;
```

Output:

```text
 employee_id | effective_date |  salary
-------------+----------------+----------
           1 | 2024-03-01     | 60000.00
           2 | 2024-03-01     | 70000.00
(2 rows)
```

## Summary

- Use the `INSTEAD OF` trigger to customize the behavior of `INSERT`, `UPDATE`, and `DELETE` operations on a database view.

---

## 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)
- [BEFORE UPDATE Trigger](https://neon.com/postgresql/triggers/before-update-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)
- [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)