dhis2-devs team mailing list archive

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

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@xxxxxxxxx>wrote:

>
>
>
>> 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.
>>
>>
> Okay. Could another temporary work-around be to just check for recent
> updates of org unit groups through the web api?
>
>
>
> _______________________________________________
> 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
>
>