On 2 February 2011 21:36, Jason Pickering <[email protected]> wrote: > I have a question regarding the datavalue_audit table. Is it actually > used for anything? Based on a requirement here, we need to implement a > data audit procedure whereby updates or deletes to values are > recorded. It would seem that changes to data values are not recorded > in the datavalue_audit table/object. I would sort of think that this > was the purpose of the data audit table, but it did not seem to behave > as I thought it would. > > A database procedure such as the one at the end of this mail does what > we want, but not sure what the implication of putting values in this > table actually is. Perhaps it better to put it in a separate schema > outside of this public schema used by DHIS2? > > Thoughts? > > Regards, > JPP > > > CREATE OR REPLACE FUNCTION update_routine_data() RETURNS trigger AS > $datavalue_audit$ > > BEGIN > > INSERT INTO datavalue_audit(dataelementid, periodid,sourceid, > categoryoptioncomboid,value, storedby, lastupdated,comment) > VALUES (old.dataelementid, old.periodid,old.sourceid, > old.categoryoptioncomboid,old.value, old.storedby, > old.lastupdated,old.comment); > RETURN old; > > END; > $datavalue_audit$ > LANGUAGE plpgsql; > > > CREATE TRIGGER dv_audit AFTER UPDATE OR DELETE ON datavalue FOR EACH > ROW EXECUTE PROCEDURE update_routine_data();
This looks fine. Followed by CREATE TRIGGER dv_auditaudit AFTER UPDATE OR DELETE ON audit FOR EACH ROW EXECUTE PROCEDURE update_audit_routine_data(); :-) I do see the value of moving this out of public schema. Maybe you could document such an auditable implementation process. > -- > Jason P. Pickering > email: [email protected] > tel:+260974901293 > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : [email protected] > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > _______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
