On 08/21/2012 01:52 PM, Jeff Davis wrote: <blockquote cite="mid:1345524771.30161.35.camel@jdavis" type="cite"><pre
wrap="">OnMon, 2012-08-20 at 16:32 -0700, Josh Berkus wrote:
</pre><blockquote type="cite"><pre wrap="">Personally, I would prefer a tool which just made it simpler to build my
own triggers, and made it automatic for the history table to track
changes in the live table. I think anything we build which controls
what goes into the history table, etc., will only narrow the user base.
</pre></blockquote><pre wrap="">That sounds like a good way to start. Actually, even before the tool,
how about just some really good examples of triggers for specific kinds
of audit logs, and some ways to run queries on them? I think that might
settle a lot of these details.
</pre></blockquote> Here is the example of triggers we use in our applications. This is the test implementation, the
productionone uses similar triggers written in C.<br /><a
href="http://softus.org/?page_id=63">http://softus.org/?page_id=63</a><br/><br /> 1. There are 3 tables: test contains
onlycurrent data, test_history contains only historical data and test_audit contains all data.<br /> 2. There must be a
fieldin an audited table system_time for a period of validity of the row.<br /> 3. Optional fields are: txid_modified,
user_modifiedfor txid/user that inserts or updated the row, txid_deleted, user_deleted for txid/user that deleted the
row.There may be other information in the audit table that was omitted in the example (client IP, host name, etc.)<br
/>3. We do not use txid_current() as transaction ID because backup/restore resets it.<br /> 4. User is set by the
application(audit.current_user() is just a dummy).<br /> 5. There is no exclusion constraint on (primary key,
system_time)in history table, integrity is maintained by triggers (however the user can damage the historical data by
modifyingtest_history table).<br /> 6. It's important to understand that when audit triggers are enabled some
modificationscan fail because the same row may be concurrently modified by another transaction CURRENT_TIMESTAMP of
whichis lower or the same as the current one.<br />