Testing the Audit Trigger

Explore how to test audit triggers with the hstore extension to monitor changes in a PostgreSQL table. Learn to execute queries that display updates and inserts, review change details for auditing, and understand how these triggers help track data modifications efficiently.

We'll cover the following...

Viewing the updates in the artists table
Try it yourself

PostgreSQL

begin;
create temp table batch
 (
        like moma.artist
   including all
 )
 on commit drop;
\copy batch from '/usercode/artists/artists.2017-07-01.csv' with csv header delimiter ','
with upd as
(
     update moma.artist
        set (name, bio, nationality, gender, begin, "end", wiki_qid, ulan)
        
          = (batch.name, batch.bio, batch.nationality,
             batch.gender, batch.begin, batch."end",
             batch.wiki_qid, batch.ulan)
       from batch
       
      where batch.constituentid = artist.constituentid
        and (artist.name, artist.bio, artist.nationality,
             artist.gender, artist.begin, artist."end",
             artist.wiki_qid, artist.ulan)
         <> (batch.name, batch.bio, batch.nationality,
             batch.gender, batch.begin, batch."end",
             batch.wiki_qid, batch.ulan)
  returning artist.constituentid
),
    ins as
(
    insert into moma.artist
         select constituentid, name, bio, nationality,
                gender, begin, "end", wiki_qid, ulan
           from batch
          where not exists
                (
                     select 1
                       from moma.artist
                      where artist.constituentid = batch.constituentid
                )
    on conflict (constituentid) do nothing
      returning artist.constituentid
)
select (select count(*) from upd) as updates,
       (select count(*) from ins) as inserts;
commit;

This SQL statement outputs the following information:

BEGIN
CREATE TABLE
COPY 15226
 updates │ inserts 
═════════╪═════════
      52 │      61
(1 row)

COMMIT

And thanks to our audit trigger, we can have a look at what’s changed:

Testing the Audit Trigger

Viewing the updates in the artists table

Viewing the updates in the `artists` table