-- How to know to that an orgunit has changed when you change its group membership
-- eg.  change type to 'Health Centre'
-- The lastUpdated field on organisationunit table is only timestamped when changes are made to
-- that table.

-- the solution below uses a trigger to stamp the change on organisationunit.lastUpdated if there
-- is any change in the orgunitgroupmember table affecting that orgunit

CREATE EXTENSION IF NOT EXISTS plpgsql;

-- update lastUpdated timestamp on organisationunit
CREATE OR REPLACE FUNCTION orgunit_stamp()
  RETURNS trigger AS
$BODY$
BEGIN
  update organisationunit SET lastUpdated = current_timestamp where
     organisationunit.organisationunitid = NEW.organisationunitid;
  return NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

-- listen for any changes on orgunitgroupmembers and pull the trigger
DROP TRIGGER IF EXISTS org_group_change ON orgunitgroupmembers;
CREATE TRIGGER org_group_change
  AFTER INSERT OR UPDATE OR DELETE
  ON orgunitgroupmembers
  FOR EACH ROW
  EXECUTE PROCEDURE orgunit_stamp();

  