CODEWITHSUNDEEP
postgresqltriggersaudit-logging
user3242117
user3242117

Reputation: 35

Postgres audit trigger only fired by one row UPDATE

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!

Upvotes: 0

Views: 1041

Answers (2)

Manish Yadav
Manish Yadav

Reputation: 435

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.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324475

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".

Upvotes: 2

Related Questions