I’m updating data with QGis on a Postgres table. The operation consists of splitting a polygon in two parts, which means that QGis updates the original polygon (to become one of the 2 new ones) and creates a second one. On save the result is stored in the table
table1 as 2 records, one with the original
id (the PK) and the other with a new
id. But I need to keep the original polygon (for history) and I try to do this with a trigger function that simply should make a copy of the original record from
table1 to another table
table2, totally similar to the first one, except that I removed the SERIAL and the PK.
CREATE TRIGGER area_reduc_1 BEFORE UPDATE ON table1 FOR EACH ROW WHEN ((pg_trigger_depth() < 1)) EXECUTE PROCEDURE save_area_before_upd();
My trigger function is:
CREATE OR REPLACE FUNCTION field_survey.save_area_before_upd() RETURNS trigger AS $BODY$ BEGIN IF (TG_TABLE_NAME = 'table1') AND (TG_OP = 'UPDATE') THEN INSERT INTO table2 SELECT DISTINCT * FROM table1 d WHERE NEW."id" = d."id" AND NEW.area_ref = d.area_ref ; END IF; RETURN NEW; END; $BODY$
table2 I end up with attribute values that are the ones I recorded in QGis, i.e. the ones coming from
NEW! Why and how can I solve this?
I use QGis 2.8.2 (but that should not matter) and PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit.
Use record type OLD to access the old values as you do NEW to access the new values when updating a record in a table.