Re: How to specify that a trigger should fire when column is NOT in SET-clause? - Mailing list pgsql-general

create table person
(    id serial primary key,    username varchar not null unique,    password varchar not null,    credentials_last_updated timestamp NOT NULL default CURRENT_TIMESTAMP,    created timestamp NOT NULL default CURRENT_TIMESTAMP,    modified timestamp
);

Then this trigger to update "credentials_last_updated" whenever "password" is modified.

create or replace FUNCTION person_password_updated_tf() returns TRIGGER AS
$$
BEGIN
    NEW.credentials_last_updated = NEW.modified; -- OR CURRENT_TIMESTAMP if "modified" isn't specified
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

create TRIGGER person_password_updated_t BEFORE UPDATE OF password ON onp_user
    FOR EACH ROW WHEN (OLD.password <> NEW.password )
EXECUTE PROCEDURE person_password_updated_tf();