Create a Postgresql trigger that updates column with PK of other table


Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback.

I’d like to update a column (village_id) every time a new row is entered or updated with the primary key value of a different table (which is not connected as a foreign key directly). See below for an outline of my tables and trigger + function.

Unfortunately, after using the code below I received an error each time I was entering a new row with information. I used an UPDATE statement, but I also considered using an insert into statement, but wasn’t sure what is more preferable. Thank you in advance.

CREATE TABLE register_table (   register_id integer CONSTRAINT register_id_pk PRIMARY KEY,   village_id integer NOT NULL );  CREATE TABLE er_table (   er_id integer CONSTRAINT er_id_pk PRIMARY KEY,   register_id integer NOT NULL,   village_id integer NOT NULL );  CREATE OR REPLACE FUNCTION update_village_id_column() RETURNS trigger AS $  BODY$   BEGIN    UPDATE schema_example.er_table   SET    village_id = register_table.village_id   FROM   schema_example.register_table   WHERE  (schema_example.register_id = new.register_id);    RETURN new;   END; $  BODY$   LANGUAGE plpgsql VOLATILE COST 100;  CREATE TRIGGER update_village_id AFTER INSERT OR UPDATE ON schema_example.er_table FOR EACH ROW EXECUTE PROCEDURE update_village_id_column();