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