0

In PostgreSQL 12.2, I wan't to save modification history of records ​​that have been updated. My approach is : a first table contain some records and if a value is updated, a trigger insert old and new values into another table. With this method, the second table record modification activity of the fisrt one.

Table structure

CREATE TABLE IF NOT EXISTS table_source(id INT NOT NULL PRIMARY KEY, date_created DATE, active BOOLEAN);

CREATE TABLE IF NOT EXISTS table_activity(id INT NOT NULL PRIMARY KEY, id_table_source INT, date_created DATE, date_updated DATE, old_status TEXT, new_status TEXT);

Records (for example) :

INSERT INTO table_source (date_created, active) VALUES (NOW()::timestamp::date, TRUE);
INSERT INTO table_source (date_created, active) VALUES (NOW()::timestamp::date, FALSE);

Function

CREATE FUNCTION table_update_activity()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
IF (OLD.active IS DISTINCT FROM NEW.active) THEN
INSERT INTO table_activity(id_table_source, date_created, date_updated, old_status, new_status)  
    SELECT
        id AS id_table_source,
        date_created,
        NOW()::timestamp::date AS date_updated,
        old.active AS old_status,
        new.active AS new_status
        FROM table_source;
  END IF;
        RETURN new;
END
$BODY$;

Trigger

CREATE TRIGGER tg_table_update_activity
    AFTER UPDATE 
    ON table_source
    FOR EACH ROW
    EXECUTE PROCEDURE table_update_activity();

When I updated a value in table_source, for example set table_source.active from TRUE to FALSE, I have two inserts in table_activity not just row who have been updated.

I there something wrong in the IF statement ? What the best way to insert only values who have been updated ?

EDIT :

My function wasn't logic, this way is better.

BEGIN
INSERT INTO table_activity(id_table_source, date_created, date_updated, old_status, new_status)
    VALUES (NEW.id, NEW.date_created, NOW()::timestamp::date, OLD.active, NEW.active);
        RETURN new;
END

Thanks to this post.

GeoGyro
  • 487
  • 12
  • 32
  • Your question isn't entirely clear. In your insert statements, you've not specified the id. Are you sure that the ids of the two statements are not different? – nikhil Feb 03 '21 at 17:20
  • You're right. Each table have is own unique id. In my insert statement I use id of table_source, table_activity have a distinct one. – GeoGyro Feb 04 '21 at 08:33

0 Answers0