Trigger that is only executed once at the end of a transaction

Question

How can you program a trigger in PostgreSQL that is only called once at the end of a transaction as soon as a table x has been changed for which the trigger was defined? So the trigger should not fire for every row, nor for every statement, but only once, as soon as all statements of a transaction have been executed.

(The background is, I would like to save a time stamp for some tables when they were last changed.)

Answer 1

A possible way to solve this is by using a deferred trigger and a temporary table.

Lets assume you store the timestamps in a table like this:

CREATE TABLE TABLE_CHANGE_LOG(
    TABLE_NAME   VARCHAR(255)                        NOT NULL,
    CHANGED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

For the trigger function we need a temporary table that keeps track of every changed table, so we don't make duplicate entries in TABLE_CHANGE_LOG. We create the desired log entry in TABLE_CHANGE_LOG, and a row in the temporary UPDATE_FLAG table to avoid subsequent entries for the current triggered table.

CREATE OR REPLACE FUNCTION TABLE_CHANGE_LOGGER_TRIGGER_FUNCTION()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS UPDATE_FLAG(TABLE_NAME VARCHAR(255)) ON COMMIT DROP;
    IF (SELECT CASE WHEN COUNT(*) = 0 then 1 else 0 end 
        FROM UPDATE_FLAG WHERE TABLE_NAME = TG_TABLE_NAME)
    THEN
        INSERT INTO TABLE_CHANGE_LOG (TABLE_NAME) VALUES (TG_TABLE_NAME);
        INSERT INTO UPDATE_FLAG (TABLE_NAME) values (TG_TABLE_NAME);
    END IF;
    RETURN NEW;
END;
$$;

Onto the constraint trigger. Unfortunately you need a trigger for every table you want to log.

CREATE CONSTRAINT TRIGGER TABLE_CHANGE_LOGGER_TRIGGER
    AFTER INSERT
    ON %YOUR_TABLE_NAME%
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
EXECUTE FUNCTION TABLE_CHANGE_LOGGER_TRIGGER_FUNCTION();