---
title: "Trigger Execution - Quick Reference"
---

# Trigger Execution - Quick Reference

**Task**: Task 9 - DML Integration

---

## Quick Start

### 1. Register a Trigger

```rust
use heliosdb_nano::{EmbeddedDatabase, sql};

let db = EmbeddedDatabase::new_in_memory()?;

// Create trigger definition
let trigger = sql::TriggerDefinition::new(
    "my_trigger".to_string(),         // Trigger name
    "users".to_string(),               // Table name
    sql::logical_plan::TriggerTiming::Before,  // BEFORE, AFTER, or InsteadOf
    vec![sql::logical_plan::TriggerEvent::Insert], // INSERT, UPDATE, DELETE
    sql::logical_plan::TriggerFor::Row,  // ROW or STATEMENT
    None,                               // WHEN clause (optional)
    vec![],                             // Trigger body (LogicalPlan statements)
);

// Register trigger
db.trigger_registry.register_trigger(trigger)?;
```

### 2. Execute DML (Triggers Fire Automatically)

```rust
// INSERT - triggers execute automatically
db.execute("INSERT INTO users (id, name) VALUES (1, 'Alice')")?;
// BEFORE INSERT trigger fires
// INSERT executes
// AFTER INSERT trigger fires

// UPDATE - triggers execute automatically
db.execute("UPDATE users SET name = 'Alice Updated' WHERE id = 1")?;
// BEFORE UPDATE trigger fires (with OLD and NEW tuples)
// UPDATE executes
// AFTER UPDATE trigger fires

// DELETE - triggers execute automatically
db.execute("DELETE FROM users WHERE id = 1")?;
// BEFORE DELETE trigger fires (with OLD tuple)
// DELETE executes
// AFTER DELETE trigger fires
```

### 3. List Registered Triggers

```rust
// Get triggers for a specific table
let triggers = db.trigger_registry.get_triggers_for_table("users")?;
for trigger in triggers {
    println!("Trigger: {}", trigger.name);
}

// Get all triggers
let all_triggers = db.trigger_registry.list_all_triggers()?;
```

### 4. Drop a Trigger

```rust
// Drop a specific trigger
let dropped = db.trigger_registry.drop_trigger("users", "my_trigger")?;

// Drop all triggers for a table
let count = db.trigger_registry.drop_table_triggers("users")?;
```

---

## Trigger Types

### BEFORE Triggers

Execute **before** the DML operation:

```rust
sql::logical_plan::TriggerTiming::Before
```

**Use Cases**:
- Validation
- Data modification before insert/update
- Preventing operation (abort)
- INSTEAD OF triggers

**Example**:
```rust
let trigger = sql::TriggerDefinition::new(
    "validate_email".to_string(),
    "users".to_string(),
    sql::logical_plan::TriggerTiming::Before,
    vec![sql::logical_plan::TriggerEvent::Insert],
    sql::logical_plan::TriggerFor::Row,
    None,
    vec![/* validation logic */],
);
```

### AFTER Triggers

Execute **after** the DML operation:

```rust
sql::logical_plan::TriggerTiming::After
```

**Use Cases**:
- Audit logging
- Notification
- Cascading updates
- Statistics updates

**Example**:
```rust
let trigger = sql::TriggerDefinition::new(
    "audit_insert".to_string(),
    "users".to_string(),
    sql::logical_plan::TriggerTiming::After,
    vec![sql::logical_plan::TriggerEvent::Insert],
    sql::logical_plan::TriggerFor::Row,
    None,
    vec![/* INSERT INTO audit_log */],
);
```

### INSTEAD OF Triggers

Replace the DML operation entirely:

```rust
sql::logical_plan::TriggerTiming::InsteadOf
```

**Use Cases**:
- Views (make updatable)
- Complex data transformations
- Conditional logic

---

## Trigger Events

### INSERT Event

```rust
sql::logical_plan::TriggerEvent::Insert
```

**Context Available**:
- `NEW` tuple (new row being inserted)

### UPDATE Event

```rust
// All columns
sql::logical_plan::TriggerEvent::Update(None)

// Specific columns
sql::logical_plan::TriggerEvent::Update(Some(vec!["email".to_string(), "name".to_string()]))
```

**Context Available**:
- `OLD` tuple (row before update)
- `NEW` tuple (row after update)

### DELETE Event

```rust
sql::logical_plan::TriggerEvent::Delete
```

**Context Available**:
- `OLD` tuple (row being deleted)

---

## Trigger Granularity

### FOR EACH ROW

Execute once per affected row:

```rust
sql::logical_plan::TriggerFor::Row
```

**Characteristics**:
- Most common
- Access to individual row data
- Can modify/validate each row

### FOR EACH STATEMENT

Execute once per SQL statement:

```rust
sql::logical_plan::TriggerFor::Statement
```

**Characteristics**:
- Executes once regardless of rows affected
- Efficient for bulk operations
- Limited access to row data

---

## Trigger Actions

### Continue

Proceed with the DML operation:

```rust
sql::triggers::TriggerAction::Continue
```

### Skip (INSTEAD OF)

Skip the DML operation for this row:

```rust
sql::triggers::TriggerAction::Skip
```

**Use Cases**:
- INSTEAD OF triggers
- Conditional skipping
- View updates

### Abort

Abort the DML operation with error:

```rust
sql::triggers::TriggerAction::Abort("Validation failed".to_string())
```

**Behavior**:
- Entire operation rolls back
- Error propagated to caller
- Transaction aborted (if in transaction)

---

## Cascading Triggers

### Depth Limit

Maximum cascading depth: **16 levels** (PostgreSQL compatible)

```rust
pub const MAX_TRIGGER_DEPTH: usize = 16;
```

### Depth Tracking

```rust
let mut context = sql::TriggerContext::new();

// Enter trigger
context.enter("trigger_name")?; // Increments depth

// Exit trigger
context.exit(); // Decrements depth

// Check depth
let current_depth = context.depth();
let at_max = context.at_max_depth();
```

### Depth Exceeded Error

```
Error: Maximum trigger cascading depth (16) exceeded.
       Trigger chain: trigger1 -> trigger2 -> ... -> trigger16
```

---

## Row Context

### TriggerRowContext

Provides access to OLD and tuples:

```rust
pub struct TriggerRowContext {
    pub old_tuple: Option<Tuple>,
    pub new_tuple: Option<Tuple>,
}
```

### Creating Context

```rust
// INSERT - NEW only
let ctx = sql::triggers::TriggerRowContext::for_insert(new_tuple);

// UPDATE - OLD and NEW
let ctx = sql::triggers::TriggerRowContext::for_update(old_tuple, new_tuple);

// DELETE - OLD only
let ctx = sql::triggers::TriggerRowContext::for_delete(old_tuple);
```

---

## Performance

### Overhead

| Scenario | Overhead |
|----------|----------|
| No triggers | ~0% (early exit) |
| BEFORE trigger | ~5-10% |
| AFTER trigger | ~5-10% |
| Both BEFORE+AFTER | ~10-20% |
| Cascading triggers | Multiplicative |

### Optimization Tips

1. **Use event-specific triggers**: Don't use `TriggerEvent::Update(None)` if you only care about specific columns
2. **Minimize trigger body**: Keep trigger logic simple
3. **Avoid deep cascading**: Limit trigger chains to 2-3 levels
4. **Use STATEMENT triggers for bulk**: More efficient than ROW for large operations

---

## Error Handling

### Trigger Execution Errors

```rust
match db.execute("INSERT INTO users VALUES (1, 'bad@email')") {
    Ok(count) => println!("Inserted {} rows", count),
    Err(e) => {
        // Could be:
        // - "INSERT aborted by trigger: Validation failed"
        // - "Maximum trigger cascading depth (16) exceeded"
        println!("Error: {}", e);
    }
}
```

### Best Practices

1. **Always handle errors**: Trigger execution can fail
2. **Provide clear error messages**: Use descriptive abort messages
3. **Test trigger error paths**: Ensure rollback works correctly
4. **Log trigger failures**: For debugging and auditing

---

## Common Patterns

### Pattern 1: Audit Log

```rust
// AFTER INSERT/UPDATE/DELETE trigger to log changes
let trigger = sql::TriggerDefinition::new(
    "audit_changes".to_string(),
    "users".to_string(),
    sql::logical_plan::TriggerTiming::After,
    vec![
        sql::logical_plan::TriggerEvent::Insert,
        sql::logical_plan::TriggerEvent::Update(None),
        sql::logical_plan::TriggerEvent::Delete,
    ],
    sql::logical_plan::TriggerFor::Row,
    None,
    vec![/* INSERT INTO audit_log (table, action, timestamp, data) */],
);
```

### Pattern 2: Email Validation

```rust
// BEFORE INSERT trigger to validate email format
let trigger = sql::TriggerDefinition::new(
    "validate_email".to_string(),
    "users".to_string(),
    sql::logical_plan::TriggerTiming::Before,
    vec![sql::logical_plan::TriggerEvent::Insert],
    sql::logical_plan::TriggerFor::Row,
    None,
    vec![/* Check NEW.email format, abort if invalid */],
);
```

### Pattern 3: Timestamp Auto-Update

```rust
// BEFORE UPDATE trigger to set updated_at timestamp
let trigger = sql::TriggerDefinition::new(
    "update_timestamp".to_string(),
    "users".to_string(),
    sql::logical_plan::TriggerTiming::Before,
    vec![sql::logical_plan::TriggerEvent::Update(None)],
    sql::logical_plan::TriggerFor::Row,
    None,
    vec![/* SET NEW.updated_at = NOW() */],
);
```

### Pattern 4: Cascading Delete

```rust
// BEFORE DELETE trigger to cascade to child tables
let trigger = sql::TriggerDefinition::new(
    "cascade_delete".to_string(),
    "users".to_string(),
    sql::logical_plan::TriggerTiming::Before,
    vec![sql::logical_plan::TriggerEvent::Delete],
    sql::logical_plan::TriggerFor::Row,
    None,
    vec![
        /* DELETE FROM user_sessions WHERE user_id = OLD.id */
        /* DELETE FROM user_preferences WHERE user_id = OLD.id */
    ],
);
```

---

## Debugging

### List Triggers

```rust
// Check what triggers are registered
let triggers = db.trigger_registry.get_triggers_for_event(
    "users",
    &sql::logical_plan::TriggerEvent::Insert,
    &sql::logical_plan::TriggerTiming::Before,
)?;

for trigger in triggers {
    println!("Trigger: {} (enabled: {})", trigger.name, trigger.enabled);
}
```

### Enable/Disable Triggers

```rust
// Disable a trigger temporarily
db.trigger_registry.disable_trigger("users", "my_trigger")?;

// Re-enable
db.trigger_registry.enable_trigger("users", "my_trigger")?;
```

### Check Trigger Exists

```rust
if db.trigger_registry.trigger_exists("users", "my_trigger")? {
    println!("Trigger exists");
}
```

---

## Files and Locations

| Component | Location |
|-----------|----------|
| TriggerRegistry | `src/sql/triggers.rs` |
| DML Integration | `src/lib.rs` (lines 303-590) |
| Exports | `src/sql/mod.rs` (line 41) |
| Demo | `examples/trigger_execution_demo.rs` |
| Test Script | `scripts/test_trigger_execution.sh` |
| Documentation | `docs/reports/implementation/TRIGGER_EXECUTION_IMPLEMENTATION.md` |

---

## Supported Trigger Surface

- Trigger execution during INSERT/UPDATE/DELETE
- BEFORE, AFTER, INSTEAD OF timing
- Cascading depth tracking (16 levels)
- Thread-safe TriggerRegistry
- Error handling and rollback
- CREATE TRIGGER and DROP TRIGGER statements
- OLD and NEW row contexts for row-level triggers
- WHEN clause conditions
- WAL logging for trigger create/drop operations

---

## Quick Reference Card

```
┌─────────────────────────────────────────────────────────┐
│                 TRIGGER EXECUTION FLOW                  │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  1. Parse DML statement (INSERT/UPDATE/DELETE)         │
│  2. Initialize TriggerContext (depth = 0)              │
│  3. Build affected tuples                              │
│                                                         │
│  For each affected row:                                │
│    4. Create TriggerRowContext (OLD/NEW)               │
│    5. Execute BEFORE triggers                          │
│       ├─ Continue → Proceed                            │
│       ├─ Skip → Skip row (INSTEAD OF)                  │
│       └─ Abort → Rollback & error                      │
│    6. Perform DML operation                            │
│    7. Execute AFTER triggers                           │
│       ├─ Continue → Success                            │
│       └─ Abort → Rollback & error                      │
│                                                         │
│  8. Complete transaction                               │
│                                                         │
├─────────────────────────────────────────────────────────┤
│ Depth Limit: 16 levels (prevents infinite loops)       │
│ Thread-Safe: Arc<RwLock<TriggerRegistry>>              │
│ Transaction: ACID guarantees maintained                │
└─────────────────────────────────────────────────────────┘
```

---

**For detailed documentation, see**: `TRIGGER_EXECUTION_IMPLEMENTATION.md`