Trigger for audit logging in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a trigger in PostgreSQL?

A trigger is a special function that automatically runs when certain events happen on a table, like insert, update, or delete.

beginner

Why use triggers for audit logging?

Triggers help record changes automatically in an audit table whenever data changes, so you can track who changed what and when.

beginner

What event types can a trigger listen to for audit logging?

Triggers can listen to INSERT, UPDATE, and DELETE events to capture data changes for auditing.

beginner

What is the purpose of an audit table in PostgreSQL?

An audit table stores records of changes made to data, including old and new values, who made the change, and when it happened.

intermediate

How do you create a trigger function for audit logging in PostgreSQL?

You write a PL/pgSQL function that inserts a record into the audit table whenever a data change event occurs.

Which PostgreSQL event can a trigger NOT listen to for audit logging?

A DELETE

B UPDATE

C SELECT

D INSERT

Triggers cannot be set on SELECT statements; they only work on data-changing events like INSERT, UPDATE, and DELETE.

What language is commonly used to write trigger functions in PostgreSQL?

A PL/pgSQL

B Python

C JavaScript

D Bash

PL/pgSQL is the procedural language used inside PostgreSQL to write trigger functions.

What is the main purpose of an audit trigger?

A To delete old data

B To automatically backup the database

C To speed up queries

D To log changes made to data

Audit triggers log changes made to data for tracking and security purposes.

Which of these is NOT typically stored in an audit log?

A Database server uptime

B New data values

C Old data values

D User who made the change

Database server uptime is not related to audit logs, which focus on data changes.

How do you attach a trigger function to a table in PostgreSQL?

A Using CREATE FUNCTION statement

B Using CREATE TRIGGER statement

C Using ALTER TABLE statement

D Using INSERT INTO statement

CREATE TRIGGER is used to link a trigger function to a table event.

Explain how a trigger can be used to implement audit logging in PostgreSQL.

Think about what happens when data changes and how to capture that automatically.

Describe the steps to create an audit logging system using triggers in PostgreSQL.

Start with where to store logs, then how to capture changes, and finally how to connect them.