add_functions_and_triggers.sql

--add triggers and trigger functions to radb (note, there are also the notification triggers in the add_notifications.sql file)

BEGIN;

-- only issue >warnings log messages. (only during this transaction)
SET LOCAL client_min_messages=warning;

CREATE OR REPLACE FUNCTION resource_allocation.on_task_updated()
  RETURNS trigger AS
$BODY$
DECLARE
    claim_tentative_status_id int := 0; --beware: hard coded instead of lookup for performance
    claim_claimed_status_id int := 1; --beware: hard coded instead of lookup for performance
    task_approved_status_id int := 300; --beware: hard coded instead of lookup for performance
BEGIN
  IF NEW.status_id <> OLD.status_id THEN
    IF NEW.status_id = task_approved_status_id THEN
        UPDATE resource_allocation.resource_claim rc SET status_id=claim_tentative_status_id WHERE rc.task_id=NEW.id AND rc.status_id <> claim_tentative_status_id;
    ELSIF NEW.status_id = ANY(ARRAY[400, 500, 600, 900, 1000, 1100]) THEN
        --prevent task status to be upgraded to scheduled (or beyond) when not all its claims are claimed
        IF EXISTS (SELECT id FROM resource_allocation.resource_claim WHERE task_id = NEW.id AND status_id <> claim_claimed_status_id) THEN
            RAISE EXCEPTION 'Cannot update task status from % to % when not all its claims are claimed', OLD.status_id, NEW.status_id;
        END IF;
    END IF;
END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.on_task_updated()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.on_task_updated()
  IS 'function which is called by task table update trigger, which updates all the tasks resource claims to tentative state.';

DROP TRIGGER IF EXISTS T_on_task_updated ON resource_allocation.task CASCADE;
CREATE TRIGGER T_on_task_updated
  AFTER UPDATE
  ON resource_allocation.task
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.on_task_updated();
COMMENT ON TRIGGER T_on_task_updated ON resource_allocation.task
  IS 'task table update trigger, calls the resource_allocation.on_task_updated() function.';

---------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION resource_allocation.delete_conflict_reasons_after_resource_claim_update()
  RETURNS trigger AS
$BODY$
BEGIN
  IF OLD.status_id = 2 AND NEW.status_id <> 2 THEN   --new status is not conflict
    DELETE FROM resource_allocation.resource_claim_conflict_reason rccr WHERE rccr.resource_claim_id = NEW.id;
  END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.delete_conflict_reasons_after_resource_claim_update()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.delete_conflict_reasons_after_resource_claim_update()
  IS 'function which is called by resource_claim table update trigger, which deletes resource_claim_conflict_reasons when the claim status is updated to !conflict.';

DROP TRIGGER IF EXISTS T_delete_conflict_reasons_after_resource_claim_update ON resource_allocation.resource_claim CASCADE;
CREATE TRIGGER T_delete_conflict_reasons_after_resource_claim_update
  AFTER UPDATE
  ON resource_allocation.resource_claim
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.delete_conflict_reasons_after_resource_claim_update();