DatabaseProgramming: AutoClose ticket trigger.sql

CREATE OR REPLACE FUNCTION trg_fn_auto_close_ticket()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
--zatvoranje
    IF NEW.status IN ('resolved', 'closed')
       AND OLD.status NOT IN ('resolved', 'closed')
       AND NEW.closed_at IS NULL
    THEN
        NEW.closed_at := CURRENT_TIMESTAMP;
    END IF;
--ako povtorno se otvori
    IF OLD.status IN ('resolved', 'closed')
       AND NEW.status IN ('open', 'in_progress', 'escalated')
    THEN
        NEW.closed_at := NULL;
    END IF;

    RETURN NEW;
END;
$$;


DROP TRIGGER IF EXISTS trg_auto_close_ticket ON crm_tickets;

CREATE TRIGGER trg_auto_close_ticket
BEFORE UPDATE OF status ON crm_tickets
FOR EACH ROW
EXECUTE FUNCTION trg_fn_auto_close_ticket();