Triggers

Overview

A trigger in Supabase is a database object that automatically executes or "fires" a specified function in response to certain events on a table, such as INSERT, UPDATE, or DELETE operations. Triggers allow you to enforce rules, ensure data consistency, or perform additional actions automatically when specific database events occur.

Significance of Triggers

Triggers are essential for:

• Automating tasks like data logging, auditing, or cascading updates.
• Enforcing business rules by ensuring that certain conditions are met before data is inserted, updated, or deleted.
• Maintaining referential integrity by automatically updating related data in different tables when a change occurs.
• Performing complex validations that might be difficult to enforce through simple constraints.

Creating Triggers in Supabase

How to Create Triggers

Triggers can be created using SQL commands within your Supabase development environment (such as a local PostgreSQL database or Supabase CLI).

1. Create the Trigger Function: First, define the function that will execute when the trigger is fired. A function defines the logic to be executed upon the trigger event.

   Example:

   CREATE OR REPLACE FUNCTION log_inserted_user()
   RETURNS TRIGGER AS $$
   BEGIN
     INSERT INTO audit_log (user_id, action, timestamp)
     VALUES (NEW.id, 'INSERT', now());
     RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;

   For more information on functions refer supabase functions

2. Create the Trigger: Once the function is defined, create the trigger that will fire the function on specific table events (e.g., INSERT, UPDATE, DELETE).

   Example:

   CREATE TRIGGER log_user_insertion
   AFTER INSERT ON profiles
   FOR EACH ROW
   EXECUTE FUNCTION log_inserted_user();

Example of a Trigger for Insertion.

Insertion Trigger

CREATE OR REPLACE FUNCTION log_inserted_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (user_id, action, timestamp)
  VALUES (NEW.id, 'INSERT', now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER log_user_insertion
AFTER INSERT ON profiles
FOR EACH ROW
EXECUTE FUNCTION log_inserted_user();

Summary of What Happens

• Every time a new row is inserted into the profiles table, the trigger fires after the row is inserted.
• The trigger then calls the log_inserted_user() function, which:
  1. Logs the newly inserted profile's ID, the type of action (INSERT), and the current timestamp in the audit_log table.
  2. Returns the newly inserted row to maintain the integrity of the original INSERT operation.

You can create triggers for Updation and deletion of entries on tables.