Click here to Skip to main content
13,667,300 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


16 bookmarked
Posted 20 Jan 2009
Licenced CPOL

Audit Trail for Postgres

, 20 Jan 2009
Rate this:
Please Sign up or sign in to vote.
Discussion of audit trail implementation for the Postrgres database


This article is how to implement audit tables in PostgresSQL. It is different than some of the other databases.


Cedric Baelemans wrote about using audit tables or the term he used was Shadow tables in his article Audit Trail Generator for Microsoft SQL. I decided to do the same for PostgresSQL to see how it was done in that database.

Using the Code

Most databases will have a trigger per database operation type. With that implementation, you will need a lot of triggers and code to do the work. This implementation using PostgresSQL you only need one function per table. Below I show the two table definitions for the primary and the audit table.

// The primary table 
	User_Name 			varchar (50) NOT NULL,
	User_Password			varchar (50) NOT NULL,
	User_Email			varchar (50),
	User_Role 			varchar (50),
	UNIQUE (User_Name)
// The Audit table 
CREATE TABLE MinUser_Audit (
	AuditUser_Id   			Serial PRIMARY KEY,
        operation 		        char(1)   NOT NULL,
        stamp            		timestamp NOT NULL,
        userid            		text      NOT NULL,
	User_Id   			UUID NOT NULL,
	User_Name 			varchar (50) NOT NULL,
	User_Password			varchar (50) NOT NULL,
	User_Email			varchar (50),
	User_Role 			varchar (50)

With PostgresSQL, a Function is called when a trigger is fired. I set this to fire the same function for Update, Insert, and Delete on each table. In the function, I check to see what the operation is and then write to the audit table. This function is identical to the one in the documentation that comes with PostgresSQL except my tables have a serial key, which made the sample code fail.

        -- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
        -- make use of the special variable TG_OP to work out the operation.
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'D', now(), user, OLD.*);
            RETURN OLD;
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'U', now(), user, NEW.*);
            RETURN NEW;
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'I', now(), user, NEW.*);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
$usr_audit$ LANGUAGE plpgsql;

The original code in the documentation had a SELECT to get the old data into the audit record. The code is shown below to show the differences. Since I am using a serial key you must use the DEFAULT keyword to get the serial to work or move each column independently. This could have been done by doing OLD.User_Id and OLD.User_Name, etc for all of the data members.

INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;

There are a few special operators are used in the function. The meanings are listed below

  • NEW      Data type RECORD; variable holding the new database row for INSERT/UPDATE operations.
  • OLD       Data type RECORD; variable holding the old database row for UPDATE/DELETE operations.
  • TG_OP   Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired.

For the complete set of keywords, please refer to the PostgresSQL documentation. The next thing to do is get it to fire. To do this you have to add a trigger. When you call the CREATE TRIGGER SQL you have to state if it is BEFORE or AFTER the change, and the type of operation you want to have this fire for. In this particular application, I want to fire it for the INSERT, UPDATE, and DELETE operations.


Now you can certainly do this with an implementation of having a separate trigger for each operation and table but that will create a lot of functions and triggers. This implementation only requires one for each table that you want to have a audit trail of.

Points of Interest

There are a few articles in Code Project on triggers that you should read if you are interested in triggers. There are also different implementations. One implementation will copy the record as I have done, another will only copy the changed items. Each has their own merits. You also do not have to have an audit on every table, only the ones you want to see the changes.


Jan 20 2009 First article


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
United States United States
I am a Director of Engineering, have an MBA and work in C# forms, Asp.Net and I have been writing Windows program since windows 3.0. I am currently working in the Healthcare industry.

I enjoy reading, music (most types), and learning new technology. I am involved in opensource projects at codeplex.

My linkedin link is

You may also be interested in...

Comments and Discussions

QuestionPHP script to generate the audit trail tables based on this article Pin
Douglas Pasqua28-Feb-13 2:57
memberDouglas Pasqua28-Feb-13 2:57 
QuestionExtend Pin
hamedmirzaei23-Oct-12 3:59
memberhamedmirzaei23-Oct-12 3:59 
AnswerRe: Extend Pin
Donsw24-Oct-12 0:25
memberDonsw24-Oct-12 0:25 
GeneralRe: Extend Pin
hamedmirzaei24-Oct-12 1:16
memberhamedmirzaei24-Oct-12 1:16 
GeneralNice work! Pin
wtwhite28-Jan-09 16:07
memberwtwhite28-Jan-09 16:07 
GeneralRe: Nice work! Pin
Donsw29-Jan-09 1:35
memberDonsw29-Jan-09 1:35 
Questionreferential integrity? Pin
M Towler27-Jan-09 22:06
memberM Towler27-Jan-09 22:06 
AnswerRe: referential integrity? Pin
Donsw28-Jan-09 5:24
memberDonsw28-Jan-09 5:24 
The answer is tricky. But both are correct. If your application is something like a defect tracker (for example), it may be ok to delete the audit data when the actual defect is deleted. But for applications that cater to government reporting such as Sarbanes Oxley you can not delete the audit table since it shows what has happened. One of my past applications was a financial application where you should not delete the data. If for some reason it was the audit table should show when and who deleted it. I hope this give more light into what I did and why. Smile | :)

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06-2016 | 2.8.180820.1 | Last Updated 20 Jan 2009
Article Copyright 2009 by Donsw
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid