Sometimes we need to change data on the fly, just when being inserted in the DB. For example this can be useful when not using an ETL application but just inserting some data exported from another source.
In order to change the data, we can simply use triggers, which means that the data is "corrected" before arriving in HANA, in a seamless way, another advantage could be that this transformation is no longer needed in future queries.
In my example below, I am replacing a text column on the fly with only the first 2 letters of the initial text value; after inserting the values 121,'','zzzz' the following will be saved in HANA 121,'','zz'
CREATE COLUMN TABLE "DATA_BI"."TEST_TRIGGERS
( "COL_NUMBER" INTEGER NULL, "COL_DATE" DATE NULL, "COL_TEXT" VARCHAR (200) NULL);
CREATE TRIGGER TEST_TRIGGER BEFORE
INSERT ON TEST_TRIGGERS REFERENCING NEW ROW MYNEWROW
FOR EACH ROW
BEGIN
MYNEWROW.COL_TEXT = SUBSTRING (:MYNEWROW.COL_TEXT,0,2);
END;
INSERT INTO "DATA_BI"."TEST_TRIGGERS" VALUES(121,'','zzzz');
SELECT TOP 1000 * FROM "DATA_BI"."TEST_TRIGGERS";
* this has been tested with HANA SPS 11; more information on triggers can be found here:
https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d5a65575191014946db96aaedbef5b/content.htm
