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