Redefining triggers: Compound Triggers in EDB Postgres Advanced Server

Are you bored with writing multiple triggers? Do you want the ability to share details between the various triggers’ timing points for the same table? Let's use the compound triggers feature added in EDB Postgres Advanced Server 12 (EPAS 12). 

Compound triggers allow you to write ROW or STATEMENT level triggers and/or AFTER or BEFORE triggers within a single trigger body. Moreover, they allow you to write multiple procedures or define multiple variables within the trigger body, which can be referred to inside the timing points. Like regular triggers, they can be defined for DML operations like INSERT, UPDATE, or DELETE.

Syntax

Unlike regular triggers, compounded triggers in EDB Postgres Advanced Server take the following form (refer to the EDB Postgres Advanced Server user guide for the complete syntax):

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR { Triggering events } ON <table_name>
[ REFERENCING clause ] [ WHEN clause ]
COMPOUND TRIGGER
    [ Global variables ]
    [ Sub-procedures ]
    { One or More timing points }
END [ trigger_name ] ;

The timing points take the following form:

{ Timing Point } AS | IS
{ PL block i.e BEGIN .. END }
[ Timing Point ] ;

Apart from incorporating different triggers into a single one, a compound trigger has the advantage of sharing some data across different timing points. EPAS 12 supports the following timing points:

1. BEFORE STATEMENT
   Trigger fired before executing the statement
2. BEFORE EACH ROW
   Tigger fired before modifying each row affected by the statement
3. AFTER EACH ROW
   Trigger fired after modifying each row affected by the statement
4. AFTER STATEMENT
   Tigger fired after executing the statement
5. INSTEAD OF EACH ROW
       For views only

A compound trigger can have all or a few of these timing points in it, and they can appear in any order. The point to be noted here is that with compound triggers we can define both ROW and STATEMENT level actions into a single trigger body. The global variables section is like a regular DECLARE block, in which we can declare variables or types to be used within the trigger execution. Also, the sub-procedures section can define any number of user-defined functions or procedures that can be called from the timing points for various purposes.

As the compound trigger allows us to share variables within different timing points, it is a helpful tool for passing on the information from one timing point to the other. For example, some details can be collected in the BEFORE trigger that can then be used in the AFTER trigger. Or information can be collected for each row processed, and then those details can be used for logging or for analytics in an AFTER STATEMENT timing point. There can be many such use cases. Let’s see a simple example of using a compound trigger.

Example

Consider a simple use case where a user wants to display the names of all employees being updated or deleted. Here is the CREATE TABLE, a few INSERTs, and a COMPOUND TRIGGER definition for the same.

Create a simple table and insert a few records for demonstration:

CREATE TABLE empsal (
  eid integer,
  ename text,
  esal numeric,
  edept numeric
);

INSERT INTO empsal VALUES (1, 'xyz', 1000, 10);
INSERT INTO empsal VALUES (2, 'pqr', 1250, 20);
INSERT INTO empsal VALUES (3, 'abc', 2250, 10);
INSERT INTO empsal VALUES (4, 'lmn', 1750, 30);
INSERT INTO empsal VALUES (5, 'def', 1800, 30);

Now create a COMPOUND TRIGGER having a table type and its variable, a single sub-program to display the names of the employees, and a few timing points:

CREATE TRIGGER etrig FOR DELETE OR UPDATE ON empsal
COMPOUND TRIGGER
  TYPE etab IS TABLE OF text;
  eval etab DEFAULT etab();

  PROCEDURE disp(tag text) IS
  BEGIN
    dbms_output.put_line(tag || ':');
    -- Display employee names
    for i in 1 .. eval.count loop
      dbms_output.put_line('  ' || eval(i));
    end loop;
  END;

  BEFORE EACH ROW IS
  BEGIN
    dbms_output.put_line('in BEFORE EACH ROW');
    if updating then
      eval.extend(1);
      eval(eval.last) := new.ename;
    end if;
  END;

  AFTER EACH ROW IS
  BEGIN
    dbms_output.put_line('in AFTER EACH ROW');
    if deleting then
      eval.extend(1);
      eval(eval.last) := old.ename;
    end if;
  END;

  AFTER STATEMENT IS
  BEGIN
    dbms_output.put_line('in AFTER STATEMENT');
    if updating then
      disp('Updated the following employees');
    elsif deleting then
      disp('Deleted the following employees');
    end if;
  END;
END;

As you can see in the above compound trigger, both BEFORE and AFTER row-level triggers collect the employee names into the collection and then AFTER STATEMENT trigger calls the sub-program to display the list of employees affected by the statement at the end. If you want to make sure whether the trigger is attached to the table or not, then you can verify that by executing "desc empsal" or a PostgreSQL-style "\d empsal" command on an edb-psql prompt. You will see the compound trigger definition in the "Triggers:" section of the output.

Let's run UPDATE and DELETE commands on the table and see what we get as an output. All timing points should be fired and executed. Before that, let's have a look over the records we have in our table:

# SELECT * FROM empsal ORDER BY edept, ename;
 eid | ename | esal | edept
-----+-------+------+-------
   3 | abc   | 2250 |    10
   1 | xyz   | 1000 |    10
   2 | pqr   | 1250 |    20
   5 | def   | 1800 |    30
   4 | lmn   | 1750 |    30
(5 rows)

Now run UPDATE and DELETE on the table:

# UPDATE empsal SET esal = esal * 2 WHERE edept = 30;
in BEFORE EACH ROW
in BEFORE EACH ROW
in AFTER EACH ROW
in AFTER EACH ROW
in AFTER STATEMENT
Updated the following employees:
  lmn
  def
UPDATE 2

# DELETE empsal WHERE edept = 10;
in BEFORE EACH ROW
in BEFORE EACH ROW
in AFTER EACH ROW
in AFTER EACH ROW
in AFTER STATEMENT
Deleted the following employees:
  xyz
  abc
DELETE 2

The output is self-explanatory. Employee details are collected during the row-level trigger timing point execution into the eval variable, and then AFTER STATEMENT calls a sub-program disp() to display the list of employees. We can see that in both statement executions, all the timing points are executed, and, as there are two rows affected by each statement, we see that row-level timing points are called twice, whereas AFTER STATEMENT is called just once at the end. In the case of an UPDATE statement, BEFORE EACH ROW collects the employee details, whereas, in the case of a DELETE statement, AFTER EACH ROW timing point collects them.

Note that, if we wanted to do this with the regular PostgreSQL-style or an EDB-style (which is Oracle compatible) trigger, then we either need to display one line per employee, or we might need to use packages externally to manage the details. But with the compound triggers, it becomes easy and fast too. Also, if we wish to insert these logs into another table, then a regular trigger might have ended up having more INSERTs than actually required. With compound triggers, we can INSERT a single consolidated log or if required a bulk-insert can be used which is way faster than several individual INSERTs.

However, there are certain limitations on usage of the compound triggers; for example, the old and new trigger variables cannot be used in the DECLARE section or inside the BEFORE STATEMENT block. Also, the exceptions defined are non-transferable and must be handled inside the same timing point. A few additional limitations are listed in the EDB Postgres Advanced Server user guide.

Acknowledgments
I would like to thank my colleague Amit Khandekar for working on this with me and Rushabh Lathia who reviewed and committed this feature to v12. Thanks to Robert Haas for his valuable guidance and inputs throughout the implementation of the feature. My other colleague Prabhat Sahu helped us in testing this thoroughly; thanks to him as well. And last but not the least, thank you to all who have helped me in writing this blog.