Using PostgreSQL triggers to automate processes with Supabase

Functions in Postgres allow us to encapsulate some logic in our database, but we don't want to have to call these functions manually, whenever data in our database changes. In order to automate this, we can use Postgres Triggers.

Triggers in PostgreSQL allow us to subscribe to particular events that occur in the database - insert, update or delete - and call a Postgres function whenever they occur. This allows us to automate some of those manual processes - such as creating a row in our profiles table for each user who signs in.

---
Learn more about Supabase 👇

Jon Meyers 👇

---

[selique]

dude if you knew how much the community racked their brains over this authoring feature...

[yoapps]

I appreciate the way you are also describing, the options on the path you arent taking... it helps because when a developer really sits down to make his scratch they come across the same options and wonder "...but what might this do?"... and you have already prepped us for that. thanks

[humn]

Please keep this going!! We need this beautiful level of documentation.
Im loving supabase! :)
Really grateful for it!!

[deoarlo]

note that this is 2 years ago. now you must create a trigger with SQL editor if you listen to auth

[__joellee__]

0:47 -- Application introduction
1:32 -- Auth settings configuration
2:11 -- Github Repo
3:43 -- Overview of the profiles table to store additional user data
5:07 -- Adding a column to store click count
5:40 -- Adding a trigger
6:09 -- Adding a function which takes effect on the trigger
6:50 -- Description of the "new" value
7:25 -- Changing security roles and rationale(Change to security Definer)
8:55 -- Explanation of trigger types(Row vs Statement)
10:17 -- Supabase Twitter and Discord

[evanrosz]

Thanks for the overview. I pressed the like button twice to send you a very kind message!

[tattran]

it's exactly what I'm looking for, thank you!

[ofeenee]

Please make more videos like these on triggers and plpgsql! 🙏🏼🙏🏼🙏🏼

[anthonyngooo]

8:05 This part needs to be updated. As of sometime in October 2023, the auth schema is available, but the users table is not visible and only public tables are shown

[Broski_Rodragweez]

I almost scoffed at the idea of using Supabase because for one I don't care much for SQL (got burned out on it a few years ago), for two I don't care for putting business logic in my database. BUT, since it appears that I can build apps without all the API layer boilerplate I am starting to come around. Plus having the AI available to help write sql makes it less painful.

[MishaMoroshko]

@JonMeyers @Supabase I'd like to create a profile row only after the user confirms their email. Do I understand correctly that the UPDATE event will call the trigger function on any update, and there is no way to trigger the function on update of a specific column? Ideally, I'd like to trigger the function when `email_confirmed_at` is changing from NULL to a non-NULL value in `auth.users`. Is this possible to achieve this?

[aahl_work]

The auth schema is managed by Supabase and is read-only through the dashboard.
Edit: Use the SQL Editor

[loribryant]

He please upddate this video there is no auth autho option anymore in trigger

[zb]

It's no longer possible to create the trigger through the UI, but I figured out how to create the trigger in the SQL editor.

create trigger
after insert on auth.users
for each row
execute function create_profile_for_user();

[Troy-olfk]

How to do scheduled publish in Supabase ?

[yellowsubmarine]

Every time I try this I get a "Database error saving new user" 500 error response. I've rewatched many times now and I'm doing exactly what you are. Any ideas?

[leandroosterne]

How would the automatic creation of records in a monthly table be, so that every month records were automatically generated in a table?

[PinasPiliNa]

is it a good practice for a production application for Inventory management to use Triggers? Hope you can make a video to demonstrate inventory management using Triggers. Thanks!

[fanizul]

Trigger from auth is not available yet, how to trigger when user created?

[rahulagarwal]

When we are using email authentication then we can send raw user meta data and fetch inside functions. But How can we get the username and profile image value from functions if we are authenticating with Google ?