0

I am trying to write an audit trigger for a database in postgresql. This audit log should record date, time, user etc., but also the specific values that were updated. My challenge is in distinguishing values in an update action that were specifically requested from those that inherited from the previous value.

For example:

CREATE TABLE dummy_table (id SERIAL NOT NULL PRIMARY KEY, a INTEGER, b INTEGER); 
INSERT INTO dummy_table (a,b) values (1,2);
UPDATE dummy_table SET (a) = (1) WHERE id = 1;

In this update the OLD and NEW records will both contain (a,b) = (1,2). But only a was actually part of the update. Is it possible to detect this distinction in the trigger?

prauchfuss
  • 1,930
  • 3
  • 17
  • 20
  • 1
    See Craig Ringer's version of the audit trigger here https://github.com/2ndQuadrant/audit-trigger – bma Jul 27 '13 at 19:52
  • Indeed this is a duplicate. Apologies. – prauchfuss Jul 28 '13 at 02:16
  • Except for its title, it's not a duplicate. It would be better summarized by its last sentence: _recognize when an UPDATE request doesn't have an identifier so I can substitute in a default value_. It 's quite different from doing an audit trigger and the answers won't help at all with the audit part, unlike the github link above which might have been an answer. – Daniel Vérité Jul 28 '13 at 14:10

0 Answers0