[crescentfresh]

Audit tables are awesome, this technique is great. We had one additional requirement that got us away from using a trigger for this: we wanted to know what user (in the application) caused the change. So we moved the logic of "insert into audit_table" into the application code itself using a CTE, roughly translated as:

    with affected_rows as (
        insert/update/delete into/from ...
        returning *
    )
    insert into my_audit_table
      select @op, current_timestamp, @userinfo, * from affected_rows

where @op is bound to one of "insert", "update" or "delete" and @userinfo is the user(name|id|various|etc) of the user that caused the change.

[kmdupree]

RETURNING * is a clever way to do this. What is this * from affected_rows syntax you're using here? Any chance you have a link to the docs that describes this?

[AaronFriel]

The affected_rows identifier is the name of the common table expression, they're just doing:

    select *
      from affected_rows

With some additional columns that happen to be provided by postgres and/or a parameter to the SQL statement.

[mrcarruthers]

We wanted to do the same thing, but still keep it in the DB as triggers. For every change we store both the DB user making the change and the application user. In postgres (and mysql) you can set variables in your transaction and then reference them from your trigger. This way we can capture when things change via the application and on the rare occasion where we need to make manual updates.