dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

On 2 February 2011 21:36, Jason Pickering <jason.p.pickering@xxxxxxxxx> 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: jason.p.pickering@xxxxxxxxx
> tel:+260974901293
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>