A PostgreSQL trigger binds a function to a table so that the function runs automatically when a defined event occurs — an INSERT, an UPDATE, a DELETE, or a TRUNCATE. Two separate pieces are required: the function itself, and the CREATE TRIGGER statement that attaches it to the table.

Setting up the table and the function

Because a trigger always belongs to a table, the example starts with one. The goal is to inspect incoming values and silently correct them: any value below zero becomes -1.

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

test=# CREATE TABLE t_temperature (

  id          serial,

  tstamp      timestamptz,

  sensor_id   int,

  value       float4

);

CREATE TABLE

test=# d t_temperature

                               Table 'public.t_temperature'

Column     | Type                     | Collation | Nullable | Default

-----------+--------------------------+-----------+----------+-------------------------------------------

id         | integer                  |           | not null | nextval('t_temperature_id_seq'::regclass)

tstamp     | timestamp with time zone |           |          |

sensor_id  | integer                  |           |          |

value      | real                     |           |          |

A function is then written to perform that correction. It reads NEW, the variable holding the row the trigger fired for; changing it changes what actually lands in the table.

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

CREATE OR REPLACE FUNCTION f_temp ()

RETURNS trigger AS

$

     DECLARE

     BEGIN

          RAISE NOTICE 'NEW: %', NEW;

          IF NEW.value < 0

          THEN

               NEW.value := -1;

               RETURN NEW;

          END IF;

          RETURN NEW;

     END;

$ LANGUAGE 'plpgsql';

NOTE: If the function returns NEW, the row will be inserted as expected. However, if you return NULL, the operation will be silently ignored. In case of a BEFORE trigger the row will not be inserted.

Attaching the trigger

With the function in place, the trigger itself is created and pointed at that function.

PgSQL

1

2

3

CREATE TRIGGER xtrig

     BEFORE INSERT ON t_temperature

     FOR EACH ROW EXECUTE PROCEDURE f_temp();

This trigger fires only on INSERT, shortly before it happens. PostgreSQL also distinguishes row-level from statement-level triggers; row-level triggers, which execute the function once per modified row, are the common choice.

The full syntax reference is at https://www.postgresql.org/docs/15/sql-createtrigger.html.

BEFORE vs. AFTER, and why naming matters

A trigger can be declared BEFORE or AFTER the operation. In the BEFORE case, the function runs before the row reaches the table, so it can still modify the row. In the AFTER case, the row is already inserted and the function sees it as stored.

The following image shows where to insert a trigger:

trigger execution postgresql before after

Any number of BEFORE and AFTER triggers may exist on one table. Order of execution has been deterministic since PostgreSQL 7.3: PostgreSQL runs all BEFORE triggers in alphabetical order, performs the operation, then runs all AFTER triggers in alphabetical order. That guarantee is what keeps trigger behaviour reproducible.

Testing it

Once deployed, the example can be exercised by inserting two rows — one is modified, the other passes through unchanged — and the trigger emits two log messages showing the contents of NEW.

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

test=# INSERT INTO t_temperature (tstamp, sensor_id, value)

  VALUES  ('2021-05-04 13:23', 1, -196.4),

          ('2021-06-03 12:32', 1, 54.5)

RETURNING *;

NOTICE: NEW: (4,'2021-05-04 13:23:00+02',1,-196.4)

NOTICE: NEW: (5,'2021-06-03 12:32:00+02',1,54.5)

id  | tstamp                 | sensor_id | value

----+------------------------+-----------+-------

4   | 2021-05-04 13:23:00+02 | 1         | -1

5   | 2021-06-03 12:32:00+02 | 1         | 54.5

(2 rows)

INSERT 0 2

Available row variables

The INSERT example makes NEW available, but other operations differ:

  • INSERT: NEW
  • UPDATE: NEW and OLD
  • DELETE: OLD
  • TRUNCATE: none

An UPDATE trigger therefore has access to both the old and the new version of the row, while a DELETE trigger can inspect the row about to be removed.

Predefined TG_* variables

A trigger function also knows the context it was called in, exposed through a set of predefined variables:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

CREATE OR REPLACE FUNCTION f_predefined ()

RETURNS trigger AS

$

     DECLARE

     BEGIN

          RAISE NOTICE 'NEW: %', NEW;

          RAISE NOTICE 'TG_RELID: %', TG_RELID;

          RAISE NOTICE 'TG_TABLE_SCHEMA: %', TG_TABLE_SCHEMA;

          RAISE NOTICE 'TG_TABLE_NAME: %', TG_TABLE_NAME;

          RAISE NOTICE 'TG_RELNAME: %', TG_RELNAME;

          RAISE NOTICE 'TG_OP: %', TG_OP;

          RAISE NOTICE 'TG_WHEN: %', TG_WHEN;

          RAISE NOTICE 'TG_LEVEL: %', TG_LEVEL;

          RAISE NOTICE 'TG_NARGS: %', TG_NARGS;

          RAISE NOTICE 'TG_ARGV: %', TG_ARGV;

          RAISE NOTICE ' TG_ARGV[0]: %', TG_ARGV[0];

          RETURN NEW;

     END;

$ LANGUAGE 'plpgsql';

CREATE TRIGGER trig_predefined

     BEFORE INSERT ON t_temperature

     FOR EACH ROW EXECUTE PROCEDURE f_predefined('hans');

INSERT INTO t_temperature (tstamp, sensor_id, value)

     VALUES ('2025-02-12 12:21', 2, 534.4);
  • TG_RELID: the internal object id of the table being triggered
  • TG_RELNAME + TG_TABLE_SCHEMA + TG_TABLE_NAME: table and schema names
  • TG_OP: whether the trigger fired for INSERT, UPDATE, DELETE or TRUNCATE
  • TG_WHEN: whether the trigger fired BEFORE or AFTER the operation
  • TG_LEVEL: statement or row level trigger
  • TG_ARGS: the number of arguments passed to the trigger
  • TG_ARGV: the list of arguments (index starts at 0)

Running the listing makes the trigger report exactly what is happening:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

NOTICE: NEW: (8,'2025-02-12 12:21:00+01',2,534.4)

NOTICE: TG_RELID: 98399

NOTICE: TG_TABLE_SCHEMA: public

NOTICE: TG_TABLE_NAME: t_temperature

NOTICE: TG_RELNAME: t_temperature

NOTICE: TG_OP: INSERT

NOTICE: TG_WHEN: BEFORE

NOTICE: TG_LEVEL: ROW

NOTICE: TG_NARGS: 1

NOTICE: TG_ARGV: [0:0]={hans}

NOTICE: TG_ARGV[0]: hans

NOTICE: NEW: (8,'2025-02-12 12:21:00+01',2,534.4)

That context is what allows one function to be reused across more than one table instead of being written per table.

Looking further

Triggers support considerably more than the introduction covered here, and the subject rewards closer study. Earlier material on sophisticated temporary tables covers another corner of PostgreSQL.