Modifying the NEW record
Another form of auditing that is frequently used is to log information in fields in the same row as the data. As an example, let's define a trigger that logs the time and the active user in the last_changed_at
and last_changed_by
fields at each INSERT
and UPDATE
trigger. In the row-level BEFORE
triggers, you can modify what actually gets written by changing the NEW
record. You can either assign values to some fields or even return a different record with the same structure. For example, if you return OLD
from the UPDATE
trigger, you effectively make sure that the row can't be updated.
The timestamping trigger
To form the basis of our audit logging in the table, we start by creating a trigger that sets the user who made the ...
Get PostgreSQL Server Programming - Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.