-- Creating the tableCREATETABLEpublic.company_seqs
(company_id BIGINT NOTNULL,
last_seq BIGINT NOTNULLDEFAULT1000,CONSTRAINT company_seqs_pk PRIMARYKEY(company_id));-- Creating the functionCREATEOR 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 triggerCREATETRIGGER tf_users_code_seq BEFORE INSERTONpublic.users FOR EACH ROWEXECUTEPROCEDURE users_code_seq();
When inserting data:
INSERTINTOpublic.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)VALUES(672,'test2@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'1');INSERTINTOpublic.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)VALUES(672,'test3@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.