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();
EDIT: removed recursive part from trigger
CREATE TRIGGER update_village_id
AFTER INSERT
ON schema_example.er_table
FOR EACH ROW
EXECUTE PROCEDURE update_village_id_column();
