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 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:

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:

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

If you try to insert invalid data, the transaction aborts:

-- 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);

2. Data Transformation (BEFORE UPDATE)

You can modify the 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)

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

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 OLD state of the row to the NEW state.

Example: Price Change Tracker in Python

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

First, create an audit table:

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:

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 OLD["balance"] != NEW["balance"]:
    # Use oxibase.execute to run DML side-effects
    stmt = "INSERT INTO audit_log (account_id, old_balance, new_balance) VALUES (" + str(OLD["id"]) + ", " + str(OLD["balance"]) + ", " + str(NEW["balance"]) + ")"
    oxibase.execute(stmt)
';

Dropping Triggers

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

DROP TRIGGER IF EXISTS log_balance_changes ON accounts;