Database Triggers

Use database triggers to automatically start pgflow workflows when data changes, enabling event-driven architecture.

Note

For complete PostgreSQL trigger documentation including trigger timing, events, and advanced patterns, see the PostgreSQL Trigger Documentation.

Basic Pattern

The recommended pattern uses FOR EACH STATEMENT triggers with bulk processing:

-- Trigger function
CREATE OR REPLACE FUNCTION trigger_article_processing()
RETURNS TRIGGER AS $$
BEGIN
  -- Start one workflow per newly inserted article
  -- PERFORM FROM executes once per row, discards results
  PERFORM pgflow.start_flow(
    flow_slug => 'process_article',
    input => jsonb_build_object(
      'articleId', new_articles.id,
      'url', new_articles.url,
      'site', new_articles.site
    )
  )
  FROM new_articles;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Trigger fires once per INSERT statement
CREATE TRIGGER on_new_articles
  AFTER INSERT ON articles
  REFERENCING NEW TABLE AS new_articles
  FOR EACH STATEMENT
  EXECUTE FUNCTION trigger_article_processing();

Tip

Why FOR EACH STATEMENT?

This pattern is more efficient than FOR EACH ROW because:

• Trigger function runs once per SQL statement, not once per row
• Can process multiple rows in a single function call
• Avoids overhead of function invocation per row
• Better performance for bulk inserts

Reacting to Status Changes

Trigger workflows when order status changes to “shipped”:

CREATE OR REPLACE FUNCTION on_order_shipped()
RETURNS TRIGGER AS $$
BEGIN
  -- Only process orders that just changed to shipped status
  PERFORM pgflow.start_flow(
    flow_slug => 'send_shipping_notification',
    input => jsonb_build_object(
      'orderId', updated_orders.id,
      'trackingNumber', updated_orders.tracking_number
    )
  )
  FROM new_orders AS updated_orders
  JOIN old_orders ON old_orders.id = updated_orders.id
  WHERE updated_orders.status = 'shipped'
    AND old_orders.status != 'shipped';

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_order_status_change
  AFTER UPDATE OF status ON orders
  REFERENCING NEW TABLE AS new_orders OLD TABLE AS old_orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION on_order_shipped();

Caution

Avoiding Infinite Loops

Triggers that modify the same table they’re attached to can cause infinite loops. Design your workflows to update status in separate tables or use conditional logic to prevent re-triggering.

Learn More

Schedule Flows with pg_cron Run workflows on a recurring schedule