> > > 1) You attached users_code_seq() to a trigger on the users table. >
yes > 2) You have a where clause: company_id = NEW.id > 3) NEW refers to users > 4) NEW.id is obstensibly a USER ID > No... CREATE OR REPLACE FUNCTION users_code_seq() > RETURNS "trigger" AS $$ > DECLARE code character varying; > BEGIN > IF (TG_OP = 'INSERT') THEN > UPDATE public.companies SET client_code_increment = > (client_code_increment + 1) WHERE id = NEW.id; > END IF; > IF NEW.code IS NULL THEN > SELECT client_code_increment INTO NEW.code FROM public.companies > as c WHERE c.id = NEW.id ORDER BY client_code_increment DESC; > END IF; > RETURN NEW; > END; > $$ LANGUAGE plpgsql; I'm updating the companies table... The company table has a column called ID. If I change that to company_id, I get the error: column "company_id" does not exist, because that column is inside USERS and not COMPANIES. So as far as I can see here, that command is right. - If I'm wrong, please, explain to me. 5) So you are basically saying: WHERE company_id = user_id > 6) If you were to get match it would be entirely by accident - say because > you used the same integer for both id values > Just to be clear here: 1 - > ALTER TABLE public.companies ADD COLUMN client_code_increment integer; > ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT > NULL; > ALTER TABLE public.companies ALTER COLUMN client_code_increment SET > DEFAULT 1000; 2 - > CREATE OR REPLACE FUNCTION users_code_seq() > RETURNS "trigger" AS $$ > DECLARE code character varying; > BEGIN > IF (TG_OP = 'INSERT') THEN > UPDATE public.companies SET client_code_increment = > (client_code_increment + 1) WHERE company_id = NEW.id; > END IF; > IF NEW.code IS NULL THEN > SELECT client_code_increment INTO NEW.code FROM public.companies > as c WHERE c.id = NEW.id ORDER BY client_code_increment DESC; > END IF; > RETURN NEW; > END; > $$ LANGUAGE plpgsql; 3 - > CREATE TRIGGER tf_users_code_seq > BEFORE INSERT > ON public.users > FOR EACH ROW > EXECUTE PROCEDURE users_code_seq();
