Server-side code is especially popular if you want to react to certain events that are happening in the database. A trigger allows you to call a function if an INSERT, UPDATE, DELETE, or TRUNCATE clause happens on a table. The function that is called by the trigger can then modify the data that's changed in your table, or simply perform a necessary operation.

In PostgreSQL, triggers have become more powerful over the years, and they now provide a rich set of features:

test=# h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

URL: https://www.postgresql.org/docs/12/sql-createtrigger.html

The first thing to observe is that a trigger is always fired for a table or a view, and calls a function. It has a name, and it can happen before or after an event. The beauty of PostgreSQL is that you can have umpteen number of triggers on a single table. While this will not come as a surprise to hardcore PostgreSQL users, I want to point out that this is not possible in many expensive commercial database engines that are still in use around the world.

If there is more than one trigger on the same table, then the following rule, which was introduced many years ago in PostgreSQL 7.3, will be useful: triggers are fired in alphabetical order. First, all of those BEFORE triggers happen in alphabetical order. Then, PostgreSQL performs the row operation that the trigger has been fired for, and continues executing after the triggers in alphabetical order. In other words, the execution order of triggers is absolutely deterministic, and the number of triggers is basically unlimited.

Triggers can modify data before or after the actual modification has happened. In general, this is a good way to verify data and to error out if some custom restrictions are violated. The following example shows a trigger that is fired in the INSERT clause, and which changes the data that's added to the table:

CREATE TABLE t_sensor ( 
        id serial, 
        ts timestamp, 
        temperature numeric 
); 

Our table just stores a couple of values. The goal now is to call a function as soon as a row is inserted:

CREATE OR REPLACE FUNCTION trig_func() 
RETURNS trigger AS 
$$ 
        BEGIN 
                IF NEW.temperature < -273 
                THEN 
                        NEW.temperature := 0; 
                END IF; 

                RETURN NEW; 
        END; 
$$ LANGUAGE 'plpgsql'; 

As we stated previously, the trigger will always call a function, which allows you to use nicely abstract code. The important thing here is that the trigger function has to return a trigger. To access the row that you are about to insert, you can access the NEW variable.

In my example, the code checks whether the temperature is too low. If it is, the value is not okay; it is dynamically adjusted. To ensure that the modified row can be used, NEW is simply returned. If there is a second trigger called after this one, the next function call will already see the modified row.

In the next step, the trigger can be created by using the CREATE TRIGGER command:

CREATE TRIGGER sensor_trig  
  BEFORE INSERT ON t_sensor  
  FOR EACH  ROW 
  EXECUTE PROCEDURE trig_func(); 

Here is what the trigger will do:

test=# INSERT INTO t_sensor (ts,  temperature)  
  VALUES ('2017-05-04 14:43', -300) RETURNING *; 
 id  |   ts                | temperature 
-----+---------------------+------------- 
 1   | 2017-05-04 14:43:00 |   0 
(1 row) 
 
INSERT 0 1 

As you can see, the value has been adjusted correctly. The content of the table shows 0 for the temperature.

If you are using triggers, you should be aware of the fact that a trigger knows a lot about itself. It can access a couple of variables that allow you to write more sophisticated code, and therefore achieve better abstraction.

Let's drop the trigger first:

test=# DROP TRIGGER sensor_trig ON t_sensor;  
DROP TRIGGER 

Then, a new function can be added:

CREATE OR REPLACE FUNCTION trig_demo() 
  RETURNS trigger AS 
$$ 
BEGIN 
  RAISE NOTICE 'TG_NAME: %', TG_NAME; 
  RAISE NOTICE 'TG_RELNAME: %', TG_RELNAME; 
  RAISE NOTICE 'TG_TABLE_SCHEMA: %', TG_TABLE_SCHEMA; 
  RAISE NOTICE 'TG_TABLE_NAME: %', TG_TABLE_NAME; 
  RAISE NOTICE 'TG_WHEN: %', TG_WHEN; 
  RAISE NOTICE 'TG_LEVEL: %', TG_LEVEL; 
  RAISE NOTICE 'TG_OP: %', TG_OP; 
  RAISE NOTICE 'TG_NARGS: %', TG_NARGS; 
-- RAISE  NOTICE 'TG_ARGV: %', TG_NAME; 
  RETURN NEW; 
END; 
$$ LANGUAGE 'plpgsql'; 

CREATE TRIGGER sensor_trig
    BEFORE INSERT ON t_sensor
    FOR EACH ROW
    EXECUTE PROCEDURE trig_demo();

All of the variables that are used here are predefined, and are available by default. All our code does is display them, so that we can see their content:

test=# INSERT INTO t_sensor (ts, temperature)
              VALUES ('2017-05-04 14:43', -300) RETURNING *;

NOTICE: TG_NAME: demo_trigger
NOTICE: TG_RELNAME: t_sensor
NOTICE: TG_TABLE_SCHEMA: public
NOTICE: TG_TABLE_NAME: t_sensor
NOTICE: TG_WHEN: BEFORE
NOTICE: TG_LEVEL: ROW
NOTICE: TG_OP: INSERT
NOTICE: TG_NARGS: 0

 id  | ts                  | temperature
-----+---------------------+-------------
 2   | 2017-05-04 14:43:00 | -300
(1 row)

INSERT 0 1

What we see here is that the trigger knows its name, the table it has been fired for, and a lot more. To apply similar actions on various tables, these variables help to avoid duplicate code by just writing a single function. This can then be used for all the tables that we are interested in.

So far, we have seen simple row-level triggers, which are fired once per statement. However, with the introduction of PostgreSQL 10.0, there are a couple of new features. Statement-level triggers have been around for a while already. However, it wasn't possible to access the data that's changed by a trigger. This has been fixed in PostgreSQL 10.0, and it is now possible to make use of transition tables, which contain all the changes that were made.

The following code contains a complete example, showing how a transition table can be used:

CREATE OR REPLACE FUNCTION transition_trigger()  
  RETURNS TRIGGER AS $$ 
    DECLARE 
  v_record  record; 
    BEGIN 
        IF  (TG_OP = 'INSERT') THEN 
    RAISE NOTICE 'new data: '; 
    FOR v_record IN SELECT * FROM new_table 
    LOOP 
      RAISE NOTICE '%', v_record; 
    END LOOP; 
        ELSE  
    RAISE NOTICE 'old data: '; 
    FOR v_record IN SELECT * FROM old_table 
    LOOP 
      RAISE NOTICE '%', v_record; 
    END LOOP; 
        END IF; 
        RETURN NULL; -- result is ignored since this is an AFTER trigger 
    END; 
$$ LANGUAGE plpgsql; 
 
CREATE TRIGGER transition_test_trigger_ins 
    AFTER INSERT ON t_sensor 
    REFERENCING NEW TABLE AS new_table 
    FOR EACH STATEMENT EXECUTE PROCEDURE transition_trigger(); 
 
CREATE TRIGGER transition_test_trigger_del 
    AFTER DELETE ON t_sensor 
    REFERENCING OLD TABLE AS old_table 
    FOR EACH STATEMENT EXECUTE PROCEDURE transition_trigger(); 

In this case, we need two trigger definitions, because we cannot just squeeze everything into just one definition. Inside the trigger function, the transition table is easy to use: it can be accessed just like a normal table.

Let's test the code of the trigger by inserting some data:

INSERT INTO t_sensor  
  SELECT   *, now(), random() * 20  
  FROM   generate_series(1, 5); 
 
DELETE FROM t_sensor; 

In my example, the code will simply issue NOTICE for each entry in the transition table:

NOTICE: new data:
NOTICE: (1,"2017-10-04 15:47:14.129151",10.4552665632218)
NOTICE: (2,"2017-10-04 15:47:14.129151",12.8670312650502)
NOTICE: (3,"2017-10-04 15:47:14.129151",14.3934494629502)
NOTICE: (4,"2017-10-04 15:47:14.129151",4.35718866065145)
NOTICE: (5,"2017-10-04 15:47:14.129151",10.9121138229966)
INSERT 0 5

NOTICE: old data:
NOTICE: (1,"2017-10-04 15:47:14.129151",10.4552665632218)
NOTICE: (2,"2017-10-04 15:47:14.129151",12.8670312650502)
NOTICE: (3,"2017-10-04 15:47:14.129151",14.3934494629502)
NOTICE: (4,"2017-10-04 15:47:14.129151",4.35718866065145)
NOTICE: (5,"2017-10-04 15:47:14.129151",10.9121138229966)
DELETE 5

Keep in mind that it is not necessarily a good idea to use transition tables for billions of rows. PostgreSQL really is scalable, but at some point, it is necessary to see that there are performance implications as well.