Supabase’s Realtime Engine can track and broadcast data changes, but event triggers give you a way to react to those changes before they’re even committed to the database.
Let’s see this in action. Imagine we have a tasks table. We want to automatically mark a task as completed_at when its is_completed boolean changes to true.
Here’s the table schema:
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
description TEXT NOT NULL,
is_completed BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
completed_at TIMESTAMP WITH TIME ZONE
);
Now, let’s create an event trigger that fires before an UPDATE on the tasks table.
CREATE OR REPLACE FUNCTION audit_task_completion()
RETURNS TRIGGER AS $$
BEGIN
-- Check if the 'is_completed' column is being updated to TRUE
IF NEW.is_completed IS TRUE AND OLD.is_completed IS NOT TRUE THEN
-- Set the 'completed_at' timestamp to the current time
NEW.completed_at := NOW();
END IF;
-- Return the modified row to be inserted/updated
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tasks_completion_trigger
BEFORE UPDATE ON tasks
FOR EACH ROW
EXECUTE FUNCTION audit_task_completion();
Now, when we update a task:
-- First, let's insert a task
INSERT INTO tasks (description) VALUES ('Buy groceries');
-- Then, let's mark it as completed
UPDATE tasks
SET is_completed = TRUE
WHERE description = 'Buy groceries';
If we then select the task:
SELECT * FROM tasks WHERE description = 'Buy groceries';
We’ll see that completed_at has been automatically populated:
id | description | is_completed | created_at | completed_at
----+---------------+--------------+-----------------------------+-----------------------------
1 | Buy groceries | t | 2023-10-27 10:00:00+00 | 2023-10-27 10:05:00+00
(1 row)
The core problem event triggers solve is enabling declarative, database-level logic that runs synchronously with data modifications, before they are committed. This is crucial for maintaining data integrity, enforcing business rules, and automating related actions without application-level complexity or the risk of race conditions. Unlike stored procedures that are explicitly called, event triggers are automatically invoked by the database itself based on specific DML events (INSERT, UPDATE, DELETE) and row-level or statement-level execution.
The BEFORE keyword is key here. It means the trigger function executes before the actual INSERT, UPDATE, or DELETE operation. Inside the function, NEW refers to the row that will be inserted or updated, and OLD refers to the row before the update or delete. By modifying NEW within a BEFORE trigger, we can alter the data that eventually gets written to the table. This is how we automatically set completed_at in our example. If the trigger function returns NULL, the operation is aborted. If it returns anything else, the operation proceeds with the returned value.
The FOR EACH ROW clause means the trigger function will be executed once for every single row affected by the DML statement. If you had an UPDATE statement affecting 100 rows, the trigger function would run 100 times. For actions that need to happen only once per statement, regardless of the number of rows, you would use FOR EACH STATEMENT. This is useful for actions like logging the statement itself or performing aggregate checks.
A subtle but powerful aspect of BEFORE triggers is their ability to prevent operations. If our audit_task_completion function didn’t find NEW.is_completed to be TRUE when OLD.is_completed was not, it would simply RETURN NEW; without modification. If, however, we wanted to prevent an update under certain conditions, we could RETURN NULL;. For instance, if we wanted to disallow un-completing a task once it’s done:
CREATE OR REPLACE FUNCTION prevent_uncompletion()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.is_completed IS FALSE AND OLD.is_completed IS TRUE THEN
RAISE EXCEPTION 'Cannot un-complete a task.';
RETURN NULL; -- This line is technically unreachable due to RAISE EXCEPTION
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tasks_prevent_uncompletion_trigger
BEFORE UPDATE ON tasks
FOR EACH ROW
EXECUTE FUNCTION prevent_uncompletion();
This RAISE EXCEPTION within a BEFORE trigger effectively cancels the UPDATE operation and rolls back any changes made within that transaction so far.
When you’re debugging triggers, remember that they execute within the same transaction as the DML statement that invoked them. If a trigger fails (e.g., with a RAISE EXCEPTION), the entire transaction is rolled back. This atomicity is a major benefit for data consistency.
The next concept you’ll likely explore is using AFTER triggers for side effects that don’t need to be part of the committed data itself, like sending notifications or updating other tables where the primary operation’s success is already guaranteed.