Re: Implementing a change log

On 9/20/05, Berend Tober <btober(at)seaworthysys(dot)com> wrote:
> /*
> The following is based on suggestion by Mike Rylander posted on
> Postgresql-General
> Sun, 18 Sep 2005 23:29:51 +0000
>
> Rylander's original suggestion employed a trigger and tracked
> only row updates. My implementation makes use of rules and
> handles both updates and deletions.
> */

I'm glad that was inspirational for you. Not to nit pick, but my
trigger based version did in fact track deletions:

CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
^^^^^^^
EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ();

You may want to consider using the LIKE style of table copying, as it
strips all constraints from the new table. It's safer IMHO, as this
way you wouldn't have to worry about the primary key being propagated
to the new table (and accidentally forgetting to remove it).

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org