dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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();

-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+260974901293