On Tuesday, May 3, 2016, drum.lu...@gmail.com <drum.lu...@gmail.com> wrote:
> > - This is what I did... > > -- Creating the tableCREATE TABLE public.company_seqs(company_id BIGINT NOT > NULL, > last_seq BIGINT NOT NULL DEFAULT 1000,CONSTRAINT company_seqs_pk PRIMARY KEY > (company_id)); > > -- Creating the function > CREATE OR REPLACE FUNCTION users_code_seq() > RETURNS "trigger" AS' > BEGIN > UPDATE public.company_seqs > SET last_seq = (last_seq + 1) > WHERE company_id = NEW.company_id; > SELECT INTO NEW.code last_seq > FROM public.company_seqs WHERE company_id = NEW.company_id; > END IF; > RETURN new; > END > ' > > LANGUAGE 'plpgsql' VOLATILE; > -- Creating the triggerCREATE TRIGGER tf_users_code_seq > BEFORE INSERT > ON public.users > FOR EACH ROW > EXECUTE PROCEDURE users_code_seq(); > > > When inserting data: > > INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) > VALUES (672,'te...@test.com > <javascript:_e(%7B%7D,'cvml','te...@test.com');>','bucefalo','0','2016-05-03 > 00:01:01','2016-05-03 00:01:01',default,'1'); > INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) > VALUES (672,'te...@test.com > <javascript:_e(%7B%7D,'cvml','te...@test.com');>','bucefalo','0','2016-05-03 > 00:01:01','2016-05-03 00:01:01','inserting my own data code column','1'); > > > - > > On the first query, nothing happens on the users.code column. The > column is null. > - > > On the second query, I can see the "inserting my own data code column" > inserted into the code column. This means my Trigger function is not > working.. I don't know why. > > > This sounds like database pollution... I'd suggest writing a psql script that can be executed against an empty database and does everything you want it to do. Execute it against an empty database. Capture the output to a file with echo all. Post the script and the output. Also, try "update returning" I would also advise adding STRICT. David J.
