Why my BEFORE UPDATE trigger takes values from the NEW record? [closed]

Posted on

Question :

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.

My trigger:

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$

BUT in 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.

Answer :

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.

Refer: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html

Leave a Reply

Your email address will not be published. Required fields are marked *