Adding a trigger to set the tenant automatically

It makes sense to keep the tenant ID with the comment to be able to use simpler RLS policies, but it doesn’t make sense to allow the user to set or change it – the tenant ID should always be derived from the related ticket.

For that, let’s create a new trigger function named derive_tenant_from_ticket. We don’t need any special rights for this function – whoever has the rights to add or update a specific comment row will also have access to its related ticket – so simply put the following code in the function definition:

BEGIN
  NEW.tenant = (
   SELECT t.tenant FROM tickets t WHERE t.id = NEW.ticket
  );
  RETURN NEW;
END;

This code, when executed, sets the tenant value on the newly added row, derived from the given ticket ID (NEW.ticket).

You can see the complete setup of the derive_tenant_from_ticket trigger function here:

...