Hi Bob,
I think what you are really looking for potentially is a full-blown audit table for orgunits. Perhaps this is overkill, but agree that a custom trigger is probably the way to go with this and allow you to fine time exactly what gets audited. Downside of course is, you would need some way of working with this table. In one implementation I work on, we audit the datavalue table. Values whose period age (meaning the difference in time between the time of data entry and the period for which data is entered for) are audited into a separate audit table, which we work with through custom reports. We only consider UPDATES and DELETES, with two separate triggers
datavalue_audit_delete AFTER DELETE ON datavalue FOR EACH ROW WHEN (get_period_age(old.periodid) >= ‘30 days’::interval) EXECUTE PROCEDURE audit _routine_data()
datavalue_audit_update AFTER UPDATE ON datavalue FOR EACH ROW WHEN (get_period_age(old.periodid) >= ‘30 days’::interval AND old.value::text IS DISTINCT FROM new.value::text) EXECUTE PROCEDURE audit_routine_data()
Note that we use different conditions when the trigger happens, so I guess in your case, you could fine tune the trigger to your specific conditions, rather than perhaps auditing absolutely every change. It seems that the code in your case is important. Of course, using immutable codes to begin with would have been much better, but I suppose that was not a possibility. I would then put a trigger which looks for a change in the code, and then audit those, or redo the timestamp if putting it into a separate audit table is not going to work for you. The audit_routine_data() function looks like this.
CREATE OR REPLACE FUNCTION public.audit_routine_data()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
changed boolean;
BEGIN
IF TG_OP = ‘UPDATE’ then
INSERT INTO audit.datavalue_audit(dataelementid, periodid,sourceid,
categoryoptioncomboid, value, storedby,lastupdated, comment, value_new, changedby, comment_new, operation)
VALUES (old.dataelementid, old.periodid,old.sourceid,
old.categoryoptioncomboid,old.value, old.storedby,old.lastupdated, old.comment, new.value,new.storedby, new.comment, TG_OP);
RETURN old;
ELSIF TG_OP = ‘DELETE’ then
INSERT INTO audit.datavalue_audit(dataelementid, periodid,sourceid,
categoryoptioncomboid, value, storedby,lastupdated, comment, value_new, changedby, comment_new, operation)
VALUES (old.dataelementid, old.periodid,old.sourceid,
old.categoryoptioncomboid,old.value, old.storedby,old.lastupdated, old.comment, NULL,NULL, NULL, TG_OP);
RETURN old;
END IF;
END;
Again, slightly different operations depending on what is happening. I think you could use a similar approach for the orgunits and tweak it to fire when you wanted it to.
Regards,
Jason
···
On Tue, Oct 15, 2013 at 6:18 PM, Lars Helge Øverland larshelge@gmail.com wrote:
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
Okay. Could another temporary work-around be to just check for recent updates of org unit groups through the web api?
Short term, since I have no option currently except getting down and dirty in the sql to workaround, I guess I should instead create a new table for updated orgunit ids, but whose update stamp takes into account groups, attributes and what have you. And leave the orgUnit lastUpdated untouched.
There might be a secondary benefit of having a separate table for logging orgunit updates via trigger … I could possibly also record the deletions which is currently another missing functionality in the picture. Hopefully will get time to look more at this tomorrow.
