Supabase Triggers Explained (with Real Use Cases)

Wait, today, I learnt subapabe triggers. That's what has triggered🚀 me to do this post😂. Here is a guide on what triggers are, what they do and how to make them work.

What is a trigger
A trigger is a special function in a database that automatically execute when a certain event happens — like when a row is inserted, updated, or deleted. Think of a trigger as an event listener for your database.

Why triggers
Triggers offload repetitive logic from your application. Instead of your app manually checking if something changed and then acting on it, the database handles it automatically.

A simple use case
Suppose you have a _secondary _table that depends on data from a _primary _table.
Instead of manually checking the _primary _table every time a new row is inserted — and then querying/inserting into the _secondary _table — you can use a trigger to listen to inserts on the _primary _table and automatically populate the _secondary _table. Hope its not confusing😕.

Syntax of a trigger

-- Create the function
DROP FUNCTION IF EXISTS function_name() CASCADE;

CREATE FUNCTION function_name()
RETURN TRIGGER AS $$ -- This function will be attached to a trigger

DECLARE
-- Optional: Declare any variables that will be used
variable1 DATATYPE;
varibale2 DATATYPE;

BEGIN
-- The logic of your function goes here
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create the trigger
CREATE TRIGGER ON trigger_name
AFTER INSERT ON schema.primary_table
FOR EACH ROW -- Runs once for each inserted row
EXECUTE FUNCTION function_name();

Line-by-line explanation of the syntax

DROP FUNCTION IF EXISTS function_name() CASCADE; - Drops any function named function_name to avoid the "duplicate function error" when the function is redefined or runed multiple times. CASCADE ensures that any trigger attached to this function is also droped.
CREATE FUNCTION function_name() - Creates the function
RETURN TRIGGER AS $$ - Its like saying "hey, his function will be attached to a trigger"
$$ LANGUAGE plpgsql SECURITY DEFINER;
- LANGUAGE plpgsql- The function is written in postgress sql procedural language.
- SECURITY DEFINER - Specifies that the function runs with the privilage of the user who created it, not the one who triggered it. For example, Let’s say: You have a table logs that normal users can't write to. You create a trigger function with SECURITY DEFINER that inserts into logs. When a user inserts data into another table, the trigger fires — and the log is saved, even though that user doesn’t have permission to write directly to logs table.

A real-world example
Let’s say you want to automatically add a user’s profile into a profiles table when they sign up via Supabase Auth.

-- Step 1: Drop old function if exists
DROP FUNCTION IF EXISTS handle_new_user() CASCADE; -- Here, cascade removes any trigger using this function

-- Step 2: Create the new function
CREATE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$ -- This function will be attached to a trigger
-- Variables to hold the number of existing users and assign the appropriate role
DECLARE
  existing_users_count INTEGER;
  assigned_role TEXT;
BEGIN
  -- Debugging info
  RAISE NOTICE 'NEW.id: %, NEW.email: %', NEW.id, NEW.email;

  -- Raise an error if email is not recieved
  IF NEW.email IS NULL THEN
    RAISE EXCEPTION 'Email is null – cannot insert profile';
  END IF;

  -- Count existing users
  SELECT COUNT(*) INTO existing_users_count FROM auth.users;

  -- Role logic
  IF existing_users_count = 1 THEN
    assigned_role := 'admin';
  ELSE
    assigned_role := 'user';
  END IF;

  -- Insert into profiles
  INSERT INTO public.profiles(id, email, role)
  VALUES (NEW.id, NEW.email, assigned_role);

  RETURN NEW;

EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Step 3: Create the trigger
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users -- Trigger fires after the action(insertion to the auth.users table)
FOR EACH ROW -- Runs once for each inserted raw
EXECUTE FUNCTION handle_new_user(); -- Connect the table to the trigger function

Isn't that amazing?
Now your database responds to events like a pro — automatically, securely, and efficiently.

Would you mind saying something before you leave?
Please say something 😂.
Anyway...
Have a beautiful day, my friend 🤗.

DEV Community

Supabase Triggers Explained (with Real Use Cases)

Top comments (0)