Postgres audit trigger only fired by one row UPDATE

Question

Hi I'm up to develop a simple audit trigger for postgresql server. According to this document, I pretty much understand how it works. But I want to record my activity only when the certain row is updated. Below is the code from the link. And it records when there is update no matter what row is updated.

    IF (TG_OP = 'UPDATE') THEN
...

Please help me how to give a condition to above code. Thanks!

*"But I want to record my activity only when the certain row is updated."* Ok. Which row? In SQL, you need to know the name of the table, the name of one key's column or columns, and the value of that key. — Mike Sherrill 'Cat Recall', Feb 25 '14 at 03:19

score 2 · Accepted Answer · answered Feb 25 '14 at 04:08

The trigger is written in PL/PgSQL. I strongly suggest you study the PL/PgSQL manual if you're going to modify PL/PgSQL code.

In triggers, the row data is in OLD and NEW (for UPDATE triggers). So you can do IF tests on that like anything else. E.g.:

IF (TG_OP = 'UPDATE') THEN
  IF NEW."name" = 'name_to_audit' OR OLD."name" = 'name_to_audit' THEN
   -- do audit commands
  END IF;
END IF;

Both NEW and OLD are tested in case the name is being changed from/to the name of interest.

In this case you could instead change it to use a WHEN clause on the CREATE TRIGGER, so you never fire the trigger at all unless the conditions to audit are met. See the WHEN clause on triggers.

This is just a basic programming problem; you'll need to learn the programming language in order to use it.

See also the updated trigger for Pg 9.1.

Oh, and remember to think about NULL; remember NULL = 'anything' is NULL. Use IS DISTINCT FROM if you want to say "these things are equal, or are both null".

I cannot try this solution until I get back to work tommorow, but it seems perfect thanks! — user3242117, Feb 25 '14 at 04:15

score 0 · Answer 2 · answered Sep 14 '18 at 07:21

From Postgresql Docs:

CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE log_account_update();

This only work for UPDATE on that table. For INSERT AND DELETE you can use same without WHERE query. Hope this help others.

Postgres audit trigger only fired by one row UPDATE

2 Answers2