O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Modifying the NEW record

Another form of auditing frequently used is to log information in fields in the same row as the data. As an example, let's define a trigger which logs the time and active user in fields last_changed_at and last_changed_by fields at each INSERT and UPDATE. In 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.

Timestamping trigger

To form the basis of our audit logging in the table, we start with creating a trigger that sets the user who made the last change and when ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required