postgres trigger to insert value on when condition

Question

I have to write a trigger in postgres trigger should insert values in rp_resourceUser table.

I have three table rp_user, rp_resourceUser, rp_resourceType they all under public schema

fields in rp_user
user_id bigint NOT NULL,
  is_gtl boolean,
  is_tsc_admin boolean,
  username character varying(255) NOT NULL,
  gtl_user_id bigint,

fields in rp_resource_type

  resource_type_id bigint NOT NULL,
  resource_template text,
  resource_type character varying(255),

fields in rp_resource_user_type

  resource_user_type_id bigint NOT NULL,
  gtl_user_id bigint NOT NULL,
  resource_type_id bigint NOT NULL,
  user_id bigint NOT NULL,

  CONSTRAINT rp_resource_user_type_pkey PRIMARY KEY (resource_user_type_id),
  CONSTRAINT fk6t2cd10tfar76hvvchyo81u66 FOREIGN KEY (user_id)
      REFERENCES public.rp_user (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkb273dt5c7b9s49jlwf1cmbxib FOREIGN KEY (gtl_user_id)
      REFERENCES public.rp_user (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkmbxelxexffw96mcgcu3y25ixl FOREIGN KEY (resource_type_id)
      REFERENCES public.rp_resource_type (resource_type_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uk994khkskqm5c77mlyipwxu0dv UNIQUE (user_id, gtl_user_id, resource_type_id)

so condition for trigger would be when gtl flag in user table is set than insert values in rp_resource_user_type

I have wrote something like this

CREATE TRIGGER gtlPermission AFTER UPDATE ON rp_user.is_gtl
FOR EACH ROW 
WHEN (public.rp_user.is_gtl IS true)
EXECUTE PROCEDURE setGtlPermission();

Create or Replace Function setGtlPsermission () RETURNS TRIGGER AS $rp_resource_user_type$
   BEGIN
      INSERT INTO rp_resource_user_type(resource_user_type_id, gtl_user_id, resource_type_id,user_id)
      VALUES (new.resource_user_type_id,  rp_user.gtl_user_id, rp_resource_type.resource_type_id ,rp_user.user_id);
      RETURN NEW;
   END;
$rp_resource_user_type$ LANGUAGE plpgsql;

I never wrote triggers before so i dont even know if thats right .. so needed suggestion

Answer 1

docs

1. you need sequence on resource_user_type_id and default value from it for this to work. rp_user has no such field resource_user_type_id, so new.resource_user_type_id will not work...
2. you set trigger on table, not column:

CREATE TRIGGER gtlPermission AFTER UPDATE ON rp_user
FOR EACH ROW 
WHEN (public.rp_user.is_gtl IS true)
EXECUTE PROCEDURE setGtlPermission();

3. if you want to insert data set from several tables, use join:

WITH c as (select * from rp_user join rp_resource_type on some not existing so far key)
INSERT INTO rp_resource_user_type (gtl_user_id, resource_type_id,user_id)
      select gtl_user_id, resource_type_id ,user_id from c where c.user_id = NEW.user_id;

4. From your structure I don't see any obvious way you populate rp_resource_type (neither how you reference it) which makes me doubt that needed row for joining with NEW will absent, so new row won't be inserted into rp_user table ever...

I would suggest you to first play with manipulating data manually on all three tables, writing down the order of manipulations and requirements, improve the schema first (especially with FK and sequences), and then come back to creating trigger.