Skip to content
HeliosDB HeliosDB Nano

Triggers

Triggers

HeliosDB Nano supports PostgreSQL-compatible triggers for automating database operations in response to data changes.

Overview

Triggers are special procedures that automatically execute when specific events (INSERT, UPDATE, DELETE) occur on a table. They are commonly used for:

  • Audit logging: Track all changes to sensitive data
  • Data validation: Enforce complex business rules
  • Automatic updates: Maintain derived columns (timestamps, calculated fields)
  • Cascading changes: Propagate updates to related tables

Syntax

CREATE TRIGGER

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE [OF column_name [, ...]] | DELETE}
[OR {INSERT | UPDATE | DELETE} ...]
ON table_name
[FOR EACH {ROW | STATEMENT}]
[WHEN (condition)]
EXECUTE {FUNCTION | PROCEDURE} function_name()

DROP TRIGGER

DROP TRIGGER [IF EXISTS] trigger_name ON table_name [CASCADE | RESTRICT]

Trigger Timing

TimingDescription
BEFOREExecutes before the triggering operation
AFTERExecutes after the triggering operation
INSTEAD OFReplaces the triggering operation (for views)

Trigger Events

EventDescription
INSERTFires when new rows are inserted
UPDATEFires when existing rows are modified
UPDATE OF columnFires only when specific columns change
DELETEFires when rows are deleted

Trigger Granularity

TypeDescription
FOR EACH ROWExecutes once per affected row
FOR EACH STATEMENTExecutes once per SQL statement

Examples

Audit Log Trigger

-- Track all changes to the users table
CREATE TRIGGER audit_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION audit_log();

Timestamp Update Trigger

-- Automatically update modified_at column
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_modified_at();

Column-Specific Trigger

-- Only fire when price or discount changes
CREATE TRIGGER track_price_change
AFTER UPDATE OF price, discount ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();

Multi-Event Trigger

-- Single trigger for all DML operations
CREATE TRIGGER multi_event
AFTER INSERT OR UPDATE OR DELETE ON items
FOR EACH ROW
EXECUTE FUNCTION track_changes();

Statement-Level Trigger

-- Execute once per bulk operation
CREATE TRIGGER bulk_audit
AFTER INSERT ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION audit_bulk_insert();

OR REPLACE Trigger

-- Replace existing trigger definition
CREATE OR REPLACE TRIGGER replace_audit
AFTER INSERT ON logs
FOR EACH ROW
EXECUTE FUNCTION audit_logs();

INSTEAD OF Trigger (Views)

-- Intercept delete operations on views
CREATE TRIGGER prevent_delete
INSTEAD OF DELETE ON user_view
FOR EACH ROW
EXECUTE FUNCTION log_delete_attempt();

Managing Triggers

View Existing Triggers

-- List all triggers (via system views)
SELECT * FROM pg_trigger;

Drop a Trigger

-- Remove trigger
DROP TRIGGER audit_insert ON users;


-- Safe removal (no error if doesn't exist)
DROP TRIGGER IF EXISTS old_trigger ON products;


-- With cascade
DROP TRIGGER legacy_trigger ON orders CASCADE;

Current Limitations

The trigger implementation currently supports:

  • Full SQL parsing for CREATE TRIGGER and DROP TRIGGER
  • All timing options (BEFORE, AFTER, INSTEAD OF)
  • All event types (INSERT, UPDATE, DELETE)
  • UPDATE OF column specifications
  • FOR EACH ROW and FOR EACH STATEMENT
  • OR REPLACE clause
  • IF EXISTS for DROP TRIGGER

Pending implementation:

  • Trigger body execution (stored procedures)
  • OLD/NEW row variables
  • WHEN clause evaluation
  • Trigger function resolution

PostgreSQL Compatibility

HeliosDB Nano trigger syntax is fully compatible with PostgreSQL 17. Triggers created in HeliosDB Nano can be migrated to PostgreSQL without modification.

See Also