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();
