Alastair McKinley wrote: > create function latest_record_update() returns trigger as > $$ > BEGIN > update location_records set latest_record = false where person_id = new.person_id and latest_record is true and id != new.id; > return new; > END; > $$ language plpgsql; > > create trigger latest_record_trigger before insert on location_records > for each row execute procedure latest_record_update(); For maximum safety, you should use BEFORE trigger only to modify the row being inserted/updated (or to abort the operation); any change to other rows should occur in an AFTER trigger. One risk associated with failing to do that is that some other BEFORE trigger further modifies the new row, making your trigger-invoked UPDATE wrong. Also, be sure to think about possible infinite recursion. Another angle to keep in mind is what happens with insertions of historical records, i.e. those that are not latest (today you think "ah, but that never happens" and three months from now this is requested as a feature. Time to rethink the whole development ...) You'd clobber the latest_record flag without a replacement for it, which is probably undesirable. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
