Automate tasks with triggers

Use PostgreSQL triggers to automatically execute functions when data changes occur. Learn how to create triggers on TimescaleDB hypertables

Triggers are special functions that automatically execute when specific events occur on a table, such as inserts, updates, or deletes. They enable you to automate tasks like data validation, audit logging, maintaining derived data, or enforcing complex business rules without requiring application-level code.

Common use cases for triggers include:

• Data validation: Enforce complex validation rules beyond what constraints can provide
• Audit logging: Automatically track changes to sensitive data
• Derived data: Maintain summary tables or denormalized data in sync
• Error handling: Capture and route problematic data to separate tables for analysis
• Notifications: Send alerts or update external systems when data changes

supports the full range of triggers. Creating, altering, or dropping triggers on a propagates the changes to all of the underlying s.

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.

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

   CREATE OR REPLACE FUNCTION record_error()
     RETURNS trigger AS $record_error$
   BEGIN
    IF NEW.temperature >= 1000 OR NEW.humidity >= 1000 THEN
      INSERT INTO error_conditions
        VALUES(NEW.time, NEW.location, NEW.temperature, NEW.humidity);
    END IF;
    RETURN NEW;
   END;
   $record_error$ LANGUAGE plpgsql;
   
2. Create a trigger that calls this function whenever a new row is inserted into the hypertable

   CREATE TRIGGER record_error
     BEFORE INSERT ON conditions
     FOR EACH ROW
     EXECUTE PROCEDURE record_error();
   
3. Verify the trigger works

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

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 docs.