I'm using triggers in Postgres to do audit logging in a system with parent/child tables. Think an invoice with line items.
The problem I'm trying to solve is that if an invoice is new, the invoice and line item records are inserted within a transaction. In that case, I want to log that the invoice was created, but I don't need to log the creation of each line item. However, if someone comes back after the fact and adds a new line item, I do want to log the creation of that line item.
I can think of some kludgy-feeling ways to do this involving extra flag fields or maintaining a table of session variables or suchlike, but I can't find a way to check for this condition inside the trigger function. I know I can pass args into a trigger function, but I don't see any way to check for "just inserted" or "inserted within this transaction" to supply the correct argument value without still having to add some sort of audit status field.
Is there a clean mechanism that addresses this or is the "extra fields" route the way to go?
Per
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT:
SELECT CURRENT_TIMESTAMP; [...] Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp. [...] transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. [...] now() is a traditional PostgreSQL equivalent to transaction_timestamp()
Therefore if you declare a timestamp field with a default of now() the row or rows entered will all end up with the exact same timestamp value for a given transaction. In your case that means the parent and child tables will all have the same timestamp value for the initial transaction that creates the invoice and its child line items. This means that a trigger function on the line item table can do something like:
DECLARE
inv_ts timestamptz;
BEGIN
SELECT INTO inv_ts FROM parent_tbl where invoice_no = NEW.invoice_no;
IF TG_OP = 'INSERT' and NEW.child_ts > inv_ts THEN
<Run audit code>
END IF;
END