Triggers

TimescaleDB supports the full range of PostgreSQL triggers. Creating, altering, or dropping triggers on a hypertable propagates the changes to all of the underlying chunks.

Create a trigger

This example creates a new table called error_conditions with the same schema as conditions, but that only stores records which are considered errors. An error, in this case, is when an application sends a temperature or humidity reading with a value that is greater than or equal to 1000.

Creating a trigger

  1. Create a function that inserts erroneous data into the error_conditions table:

    1. CREATE OR REPLACE FUNCTION record_error()
    3.   RETURNS trigger AS $record_error$
    5. BEGIN
    7.  IF NEW.temperature >= 1000 OR NEW.humidity >= 1000 THEN
    9.    INSERT INTO error_conditions
    11.      VALUES(NEW.time, NEW.location, NEW.temperature, NEW.humidity);
    13.  END IF;
    15.  RETURN NEW;
    17. END;
    19. $record_error$ LANGUAGE plpgsql;

  2. Create a trigger that calls this function whenever a new row is inserted into the hypertable:

    1. CREATE TRIGGER record_error
    3.   BEFORE INSERT ON conditions
    5.   FOR EACH ROW
    7.   EXECUTE PROCEDURE record_error();

  3. All data is inserted into the conditions table, but rows that contain errors are also added to the error_conditions table.

TimescaleDB supports the full range of triggers, including BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE. For more information, see the PostgreSQL docs.