---
title: "Event Triggers"
layout: default
parent: Tutorials
has_children: false
nav_order: 4
---

# Using Event Triggers

Event triggers allow you to execute custom procedural logic automatically whenever data in a table is inserted, updated, or deleted. By hooking directly into the database engine, triggers ensure that critical business rules are enforced universally, regardless of which application or user issued the query.

Because Oxibase is a polyglot database, you can write these triggers in PL/SQL, Rhai, JavaScript, or Python.

## 1. Data Validation (`BEFORE INSERT` / `BEFORE UPDATE`)

The most common use case for triggers is to prevent bad data from entering your database. A `BEFORE` trigger fires before the storage engine processes the row. If your script throws an error, the operation aborts immediately.

### Example: Enforcing Positive Balances

Suppose you have an `accounts` table:

```sql
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner_name TEXT,
    balance FLOAT
);
```

You can define a trigger in **Rhai** to ensure a balance is never negative upon creation or modification:

```sql
CREATE TRIGGER ensure_positive_balance
    BEFORE INSERT ON accounts
    FOR EACH ROW
    LANGUAGE rhai
        AS '
    if oxibase.ctx.new.balance < 0.0 {
        throw "Account balance cannot be negative!";
    }
';
```

If you try to insert invalid data, the transaction aborts:
```sql
-- This succeeds
INSERT INTO accounts (id, owner_name, balance) VALUES (1, 'Alice', 100.0);

-- This throws the error and the insert is rolled back
INSERT INTO accounts (id, owner_name, balance) VALUES (2, 'Bob', -50.0);

-- Verify that Bob was not inserted
SELECT * FROM accounts;
```

## 2. Data Transformation (`BEFORE UPDATE`)

You can modify the `oxibase.ctx.new` row representation inside a `BEFORE` trigger. The mutated data is what gets saved to the disk.

### Example: Normalizing Strings with JavaScript

*(Note: Requires Oxibase to be compiled with the `js` feature)*

```sql
CREATE TRIGGER normalize_owner_name
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    LANGUAGE js
AS '
    // Force the owner name to be uppercase before saving
    oxibase.ctx.new.owner_name = oxibase.ctx.new.owner_name.toUpperCase();
';

-- Trigger the update
UPDATE accounts SET owner_name = 'alice smith' WHERE id = 1;

-- Verify the transformation
SELECT * FROM accounts WHERE id = 1;
```

## 3. Audit Logging (`AFTER UPDATE` / `AFTER DELETE`)

`AFTER` triggers execute once the data is safely persisted but before the transaction completes. They are perfect for generating audit trails by comparing the `oxibase.ctx.old` state of the row to the `oxibase.ctx.new` state.

### Example: Price Change Tracker in Python

*(Note: Requires Oxibase to be compiled with the `python` feature)*

First, create an audit table:
```sql
CREATE TABLE audit_log (
    account_id INTEGER,
    old_balance FLOAT,
    new_balance FLOAT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

Next, create a trigger that checks if the balance has changed, and if so, writes a record to the `audit_log`:

```sql
CREATE TRIGGER log_balance_changes
    AFTER UPDATE ON accounts
    FOR EACH ROW
    LANGUAGE python
AS '
import oxibase

# Only log if the balance actually changed
if oxibase.ctx.old["balance"] != oxibase.ctx.new["balance"]:
    # Use oxibase.execute to run DML side-effects
    stmt = "INSERT INTO audit_log (account_id, old_balance, new_balance) VALUES (" + str(oxibase.ctx.old["id"]) + ", " + str(oxibase.ctx.old["balance"]) + ", " + str(oxibase.ctx.new["balance"]) + ")"
    oxibase.execute(stmt)
';

-- Trigger the update
UPDATE accounts SET balance = 200.0 WHERE id = 1;

-- View the audit log
SELECT * FROM audit_log;
```

### Example: Syncing Updates with PL/SQL

You can use the native PL/SQL engine to access both `OLD` and `NEW` rows in the same transaction cleanly.

```sql
CREATE TRIGGER update_timestamp
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    LANGUAGE plsql
AS $$
BEGIN
    IF OLD.balance != NEW.balance THEN
        -- We can write to the new row dynamically
        NEW.updated_at := CURRENT_TIMESTAMP;
    END IF;
END;
$$;
```

## Dropping Triggers

To remove a trigger when it is no longer needed, use the `DROP TRIGGER` command:

```sql
DROP TRIGGER IF EXISTS log_balance_changes ON accounts;
```
