Creating SQL triggers

Often you want something to happen on certain events in a application or webapp and at most times you want the process to be automated. By using functions in your database you can separate concerns making back- and frontend less coupled.

Creating triggers for automation in SQL

I am currently creating a membership management platform for an alumni organisation. They need a way for their members to pay their membership fee via bank transfer without using a bank payment implementation. My idea is to generate invoices for each new member and store metadata of them in the database for bookkeeping practices in accordance to Swedish law. To minimise Admin hands on for membership handling I want to automate invoice generation.

One way to do this is to create a trigger and a function in the database layer that runs and creates a new invoice. I am using PostgreSQL for this application.

SQL trigger

The function itself won't do anything unless something runs it. In this case a trigger! We will create a trigger for INSERTcommands on a table of our choice.

CREATE TRIGGER on_user_profile_created
      AFTER INSERT ON public.user_profiles
      FOR EACH ROW
      EXECUTE FUNCTION create_membership_invoice_on_signup();

Take note to the keyword AFTER, making sure to trigger the function after a new user has been created since the NEW keyword in our function refers to the newly created row in the user_profiles table. This trigger will run for each new user being added to the user_profiles table. Meaning that if you would do a bulk insert of 50 new users, this trigger would run on all of them.

SQL function

Now we can create the function that will be called each time the trigger is activated. This function specifies what happens when the trigger activates.

When creating a function to be used by a trigger, you need to declare it as returning a TRIGGER type.

CREATE OR REPLACE FUNCTION create_membership_invoice_on_signup()
  RETURNS TRIGGER AS $$
  BEGIN
      INSERT INTO public.membership_invoices (
          user_id,
          amount,
          status,
          ref_no,
          due_date
      ) VALUES (
          NEW.id,
          500,
          'pending',
          'MF-' || TO_CHAR(NOW(), 'YYYY') || '-' || SUBSTRING(NEW.id::text, 1, 8),
          NOW() + INTERVAL '30 days'
      );
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER;

We declare a function that will insert a new row into the table membership_invoices. The keyword NEW here refers to the new row being inserted that fired the trigger from earlier. In this case the row with the user data created when signing up. We use it to extract the user id to the invoice, giving it a unique reference number.

Security Definer

SECURITY DEFINER is used to specify what privileges the function will be run with. DEFINER means that the function will be run with the privileges of whomever created it regardless of what the privileges of the user calling it. This means that since I create this function as an admin, it will run with admin privileges and bypass RLS(Row Level Security). If you don't want to cause this effect, INVOKER sets the function to run with user privileges and will check against any RLS policies set on the table.

Conclusion

Now we have a flow that looks like this:

user signs up -> trigger fires -> function runs -> invoice gets added

This automates the invoice generation and creates new invoices for each new member.

Why not read more about SQL and PostgreSQL?