---
title: "Triggers"
---

# 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

```sql
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

```sql
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

```sql
-- 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

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

### Column-Specific Trigger

```sql
-- 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

```sql
-- 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

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

### OR REPLACE Trigger

```sql
-- 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)

```sql
-- 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

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

### Drop a Trigger

```sql
-- 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.

## See Also

- [SQL Features](https://www.heliosdb.com/docs/lite/user-guide/sql-reference/)
- [Procedural Language](https://www.heliosdb.com/docs/lite/features/procedural-language/)