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.

2
  • 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? Commented Feb 3, 2021 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. Commented Feb 4, 2021 at 8:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.