Triggers¶

HeliosDB-Lite 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¶

Timing	Description
`BEFORE`	Executes before the triggering operation
`AFTER`	Executes after the triggering operation
`INSTEAD OF`	Replaces the triggering operation (for views)

Trigger Events¶

Event	Description
`INSERT`	Fires when new rows are inserted
`UPDATE`	Fires when existing rows are modified
`UPDATE OF column`	Fires only when specific columns change
`DELETE`	Fires when rows are deleted

Trigger Granularity¶

Type	Description
`FOR EACH ROW`	Executes once per affected row
`FOR EACH STATEMENT`	Executes 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-Lite trigger syntax is fully compatible with PostgreSQL 17. Triggers created in HeliosDB-Lite can be migrated to PostgreSQL without modification.