Change Log for Text Long

The purpose of this page is help you create a change log for fields of Reference = "Text Long". The reason that Text Long needs its own change log solution is the New Value / Old Value in the normal AD_ChangeLog table are Character Varying 2000. Since Text Log fields are infinitely long, they do not fit into the AD_ChangeLog table.

Here is an example script:

--
-- Name: zito_provision_message_chg; Type: TABLE; Schema: adempiere; Owner: adempiere
--

CREATE TABLE adempiere.zito_provision_message_chg (
    ad_client_id numeric(10,0) NOT NULL,
    ad_org_id numeric(10,0) NOT NULL,
    created timestamp without time zone DEFAULT statement_timestamp() NOT NULL,
    createdby numeric(10,0) NOT NULL,
    isactive character(1) DEFAULT 'Y'::bpchar NOT NULL,
    updated timestamp without time zone DEFAULT statement_timestamp() NOT NULL,
    updatedby numeric(10,0) NOT NULL,
    newvalue text,
    oldvalue text,
    zito_provision_message_chg_id numeric(10,0) NOT NULL,
    zito_provision_message_chg_uu character varying(36) DEFAULT NULL::character varying,
    zito_provision_message_id numeric(10,0) NOT NULL,
    CONSTRAINT zito_provision_message_chg_isactive_check CHECK ((isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])))
);


ALTER TABLE adempiere.zito_provision_message_chg OWNER TO adempiere;

--
-- Name: zito_provision_message_chg zito_provision_message_chg_key; Type: CONSTRAINT; Schema: adempiere; Owner: adempiere
--

ALTER TABLE ONLY adempiere.zito_provision_message_chg
    ADD CONSTRAINT zito_provision_message_chg_key PRIMARY KEY (zito_provision_message_chg_id);


--
-- Name: zito_provision_message_chg zito_provision_message_chuuidx; Type: CONSTRAINT; Schema: adempiere; Owner: adempiere
--

ALTER TABLE ONLY adempiere.zito_provision_message_chg
    ADD CONSTRAINT zito_provision_message_chuuidx UNIQUE (zito_provision_message_chg_uu);


--
-- Name: zito_provision_message_chg adclient_zitoprovisionmessagec; Type: FK CONSTRAINT; Schema: adempiere; Owner: adempiere
--

ALTER TABLE ONLY adempiere.zito_provision_message_chg
    ADD CONSTRAINT adclient_zitoprovisionmessagec FOREIGN KEY (ad_client_id) REFERENCES adempiere.ad_client(ad_client_id) DEFERRABLE INITIALLY DEFERRED;


--
-- Name: zito_provision_message_chg adorg_zitoprovisionmessagechg; Type: FK CONSTRAINT; Schema: adempiere; Owner: adempiere
--

ALTER TABLE ONLY adempiere.zito_provision_message_chg
    ADD CONSTRAINT adorg_zitoprovisionmessagechg FOREIGN KEY (ad_org_id) REFERENCES adempiere.ad_org(ad_org_id) DEFERRABLE INITIALLY DEFERRED;


--
-- Name: zito_provision_message_chg zitoprovisionmessage_zitoprovi; Type: FK CONSTRAINT; Schema: adempiere; Owner: adempiere
--

ALTER TABLE ONLY adempiere.zito_provision_message_chg
    ADD CONSTRAINT zitoprovisionmessage_zitoprovi FOREIGN KEY (zito_provision_message_id) REFERENCES adempiere.zito_provision_message(zito_provision_message_id) DEFERRABLE INITIALLY DEFERRED;


--
-- PostgreSQL database dump complete
--

CREATE SEQUENCE adempiere.zito_func_provision_message_chg_sq START 1000000;


CREATE FUNCTION adempiere.zito_func_provision_message_chg_log () RETURNS TRIGGER as $zito_provision_message_chg_log$
BEGIN
        INSERT INTO adempiere.Zito_Provision_Message_Chg (
                AD_Client_ID,
                AD_Org_ID,
                CreatedBy,
                UpdatedBy,
                newvalue,
                oldvalue,
                Zito_Provision_Message_Chg_ID,
                Zito_Provision_Message_Chg_UU,
                Zito_Provision_Message_ID)
        VALUES (
                NEW.AD_Client_ID,
                NEW.AD_Org_ID,
                NEW.CreatedBy,
                NEW.CreatedBy,
                NEW.Message,
                OLD.Message,
                nextval('zito_func_provision_message_chg_sq'),
                generate_uuid(),
                NEW.Zito_Provision_Message_ID);
RETURN NULL;
END;
$zito_provision_message_chg_log$ LANGUAGE plpgsql;

CREATE TRIGGER zito_provision_message_chg_log 
AFTER UPDATE OF message ON adempiere.Zito_Provision_Message
FOR EACH ROW EXECUTE PROCEDURE adempiere.zito_func_provision_message_chg_log();