log changes trigger.sql

create table laravel._keep_changes
(
	id serial not null
		constraint _keep_changes_pk
			primary key,
	operation char not null,
	transaction_id integer,
	changes text,
	created_at timestamp(0) not null,
	current_database_user varchar,
	table_name varchar,
	primary_key_value varchar,
	primary_key_name varchar,
	made_because_of_rollback boolean default false
);

-- When we rollback the changes, some new records will be made because of those rollback changes.
-- So usually we would skip these rollback changes to make them rollback again
-- This trigger will be dispatched after delete _keep_changes records because of current rollback.
-- So we mark those records that have same transaction_id with current delete trigger transaction id.
CREATE OR REPLACE FUNCTION save_records_rollback()
    RETURNS trigger AS

$BODY$
BEGIN

    UPDATE laravel._keep_changes SET made_because_of_rollback = true WHERE transaction_id = txid_current();

    RETURN NULL; -- result is ignored since this is an AFTER trigger

END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER save_changed_records_as_rollback_trigger
    AFTER DELETE
    ON laravel._keep_changes
    FOR EACH ROW
EXECUTE PROCEDURE save_records_rollback();


CREATE OR REPLACE FUNCTION save_changes_for_rollback_later()
    RETURNS trigger AS
$BODY$
DECLARE
    pk varchar array[2];
    affected_row JSON;
BEGIN


    -- if operation is DELETE or UPDATE save OLD value
    -- else if is INSERT operation save NEW value
    -- Then convert it into json to be readable for later use
    IF TG_OP in ('DELETE', 'UPDATE') THEN
        affected_row := row_to_json(OLD);
    ELSE
        affected_row := row_to_json(NEW);
    END IF;

    -- find primary key name and value of this table record
    -- and save these two values into an array
    -- index 1 => is key column name
    -- index 2 => is primary key value
    WITH pk_columns (attname) AS (
        SELECT CAST(a.attname AS TEXT)
        FROM pg_index i
                 JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey)
        WHERE i.indrelid = TG_RELID
          AND i.indisprimary
    )
    SELECT array [key, value]
    into pk
    FROM
        json_each_text(affected_row)
    WHERE key IN (SELECT attname FROM pk_columns);


    INSERT INTO laravel._keep_changes
        (
         operation,
         transaction_id,
         changes,
         created_at,
         current_database_user,
         table_name,
         primary_key_name,
         primary_key_value
         )
    values (
        -- first char of current operation
        TG_OP::char(1),
        -- current transaction id
        txid_current(),
        -- json encoded of affected row
        affected_row,
        -- current datetime
        current_timestamp(0),
        -- current database user
        user,
        -- current table name
        tg_table_name,
        -- primary key column name of this table
        pk[1],
        -- primary key value
        pk[2]
    );

    RETURN NULL; -- result is ignored since this is an AFTER trigger

END;
$BODY$
    LANGUAGE plpgsql;


CREATE TRIGGER keep_changes_trigger
    AFTER INSERT OR UPDATE OR DELETE
    ON vendor_settlement_process
    FOR EACH ROW
EXECUTE PROCEDURE save_changes_for_rollback_later();