CODEWITHSUNDEEP
postgresqltriggers
geogrow
geogrow

Reputation: 505

Copy value from other table on insert trigger, postgres

I'm quite new to PostgreSQL and have spend too much time already to try to find an example to adapt for what i want to do. So I hope I can get some help here

I have 2 tables, call them person and address

I want to create a trigger that copy street and house_nr from table Address when I insert a new value for person_id in the first table where person_id = oid

Table person
person_id
street
house_nr
other_attributes

Table Address
oid
street
house_nr
other_attributes

Something like this

INSERT INTO person
set person.street = address.street,
    person.house_nr = address.house_nr
FROM address
WHERE person_id = oid

Hope someone will have time to help cheers

Upvotes: 7

Views: 11398

Answers (1)

Nuri Tasdemir
Nuri Tasdemir

Reputation: 9842

First you need to create a trigger function. (Detailed info on http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html)

CREATE OR REPLACE FUNCTION func_before_trigger_on_person()
  RETURNS trigger AS
$BODY$
BEGIN
    SELECT address.street, address.house_nr
        INTO NEW.street, NEW.house_nr
    FROM address
    WHERE address.oid = NEW.person_id;

    RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Then you need to add this function to the table. (Detailed info on http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html)

CREATE TRIGGER before_trigger_on_person
  BEFORE INSERT OR UPDATE
  ON person
  FOR EACH ROW
  EXECUTE PROCEDURE func_before_trigger_on_person();

I did not try this specific solution. However it should work, I just modified my own triggers on Postgres 9.3

Upvotes: 16

Related Questions