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.