>_ IzzyLab

Hands-on task

This task is step 16 of the course “PostgreSQL: from your first database to transactions”

PostgreSQL: audit trigger

hard~20 min

In the shop database there are empty tables products(id,name,price) and audit(id serial, msg text). Write a PL/pgSQL trigger function that, on every INSERT into products, adds a record to audit, and attach it as an AFTER INSERT trigger. Then insert one product — a record should appear in audit.

Write the function and the trigger as the SQL script /root/audit.sql in the Editor tab — multi-line PL/pgSQL reads much better in a file than in one command — and run it in the shop database.

What you'll practise:
  • triggers
  • pl/pgsql
  • create function
  • create trigger
  • postgresql

Topic breakdown

A trigger in PostgreSQL is a pairing of two parts: a PL/pgSQL function and the trigger itself that invokes it on a table event. The function is declared with RETURNS trigger, and in its body the fields of the just-affected row are accessed via the special record NEW; it usually ends by returning that row. Then the trigger is attached to the table with the timing (AFTER INSERT) and mode (FOR EACH ROW) specified, linking the event to the function. Order matters: the function must exist before the trigger, and multi-line SQL is conveniently fed to psql via a heredoc or a file.

What's checked

  1. AFTER INSERT trigger on products exists
  2. inserting a product created a record in audit
  3. a new insert into products writes to audit by itself
  4. the /root/audit.sql script creates the trigger

What it looks like