Database Administrators Asked by PGerrits on September 25, 2020
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();
1 Asked on November 14, 2021
1 Asked on November 14, 2021 by mag-musik
1 Asked on November 14, 2021 by ziimakc
1 Asked on November 14, 2021
execution plan index index tuning performance postgresql query performance
1 Asked on November 14, 2021 by wilson-eduardo-millan-vargas
1 Asked on November 14, 2021 by user1664043
4 Asked on November 14, 2021 by sharvari
3 Asked on November 13, 2021 by saravana-kumar
1 Asked on November 11, 2021 by plamenbv
1 Asked on November 11, 2021 by palash-jadhav
3 Asked on November 11, 2021
1 Asked on November 11, 2021
1 Asked on November 11, 2021 by alf47
1 Asked on November 11, 2021 by chad-kieffer
1 Asked on November 8, 2021
1 Asked on November 8, 2021 by derek-czarny
replication sql server sql server 2012 transactional replication
1 Asked on November 8, 2021 by lenniey
1 Asked on November 8, 2021 by fabian-sierra
Get help from others!
Recent Answers
Recent Questions
© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP