How to use triggers in Supabase?

 

Step 1: Set Up a Supabase Project

 

Begin by setting up a Supabase project. If you don't have an account, sign up at Supabase. Once logged in, create a new project by following these steps:

• Click on the "New Project" button.
• Fill in the details, such as project name and database password.
• Choose a region that is geographically close to you.
• Click "Create Project."

This will set up a new Supabase project with its own dedicated PostgreSQL database.

 

Step 2: Access the Supabase Dashboard

 

Once the project is ready, you will be redirected to the Supabase dashboard:

• Navigate to the "Database" section in the menu.
• Select "SQL Editor" to execute SQL queries.

 

Step 3: Create a Table

 

To work with triggers, you'll first need a table. Create a new table using SQL commands:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

• Paste the SQL in the SQL Editor and run it to create the table.

 

Step 4: Create a Trigger Function

 

Create a trigger function that will execute a specific action automatically:

CREATE FUNCTION log_user_insertion() 
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_logs (user_id, action, created\_at)
  VALUES (NEW.id, 'insert', NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• This function logs every insertion into a separate user_logs table.

 

Step 5: Create the User Logs Table

 

Ensure you have a table to store trigger logs:

CREATE TABLE user\_logs (
  id SERIAL PRIMARY KEY,
  user\_id INTEGER NOT NULL,
  action TEXT NOT NULL,
  created\_at TIMESTAMP NOT NULL
);

• Execute this SQL to create the user_logs table.

 

Step 6: Attach the Trigger to the Users Table

 

Now, attach the trigger function to the users table:

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW EXECUTE FUNCTION log_user_insertion();

• Running this SQL will ensure that the log_user_insertion function is executed after every row insertion into the users table.

 

Step 7: Test the Trigger

 

Insert a new record into the users table to test if the trigger works:

INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');

• Check the user_logs table to see if the log has been created:

SELECT \* FROM user\_logs;

• Verify that the log entry corresponding to the insertion into users is recorded.

 

Step 8: Manage and Modify Triggers

 

To manage or modify triggers, navigate to the SQL Editor:

• Update the trigger function by redefining it.
• Drop a trigger if you no longer need it:

DROP TRIGGER IF EXISTS after_user_insert ON users;

• Adjust the trigger function logic for more complex scenarios as needed.

 

Conclusion

 

You've successfully set up and tested a PostgreSQL trigger within your Supabase project. Use this approach to automate database operations and maintain data consistency across your project.