How to create a trigger in SAP HANA
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
Thanks for penning this article and specifically for the formal quality. Even though it should be self-understood to release properly formatted and proof-read text, it's more the exception than the rule here in SCN.
And naming the SPS your article refers to (even with a docu-link) provides the required context. Good job on that part, I think!
What I personally don't like is what occurs to be a slightly naive position on the usefulness of DB triggers,
As this is one of the old-debates in the DB field it's easy to just point to some of the discussions on that:
Ask Tom "Why are triggers a bad idea"
Ask Tom: The Trouble with Triggers</title><meta name="Title" content="Ask Tom: The Trouble with …
Database developers: triggers; good or bad? : oracle
SQL Triggers and when or when not to use them. - Programmers Stack Exchange
Reasons to Avoid Triggers | SQL Server content from Dev Pro
http://stackoverflow.com/questions/460316/are-database-triggers-evil
and there's of course tons more of that.
A very important aspect (to me at least) is that triggers are 'invisible' in the sense that as long as you don't specifically look out for them, you don't know that they are there.
For any DB user that can easily become very confusing, that e.g. values in a table change automatically with apparently no reason.
They make the DB a lot more complex and harder to understand, support and maintain and - in the majority of cases - can be replaced by better solutions (e.g. a stored procedure that encapsulates the data cleansing).
That's my 2 cts on triggers
Thanks for your comment!
This article is not about triggers vs no triggers, but focused on basics of triggers which is to adjust the data on the fly (by the way this is very efficient compared to polling or scheduling jobs).
In fact this article has been written with the goal to counter the following problem : data is being pushed to HANA without having an ETL tool, and still some data changes are needed for a proper usage in HANA and reporting later on.
Of course that triggers can be tricky for a person who does not know how they work... but they still exist as a feature in HANA so let's use them when really needed.
We will have to stay in disagreement on that.
Just because the feature is there, doesn't mean in any way that it is a good idea to use it.
And the notion of "as long as you know what the trigger does there's no problem" is simply not true.
Few months down the road from this development decision, even the developer might not remember what the trigger does, why it does it or that it is even there.
Based on your use case description, I'd argue that you actually don't need a trigger at all. What your solution needs is a staging mechanism that separates the apparently completely uncontrolled influx of raw data from the core data model. Doing this allows to make required data cleansing explicit and controllable.
This staging mechanism could be a stored procedure or a staging table or a client function.
Anyhow, you are not the first developer who discovers triggers and thinks they are great and you won't be the last. So all I can do is to make sure that there are some information available to get a more complete picture - which I did.
Hi Lars,
The best practice for staging mechanism, is to use a right tool, like SAP Data Services. I have created this example to help a customer who had a fixed data model, and do not want to add extra tools to the landscape, they want to minimize the TCO.
PS: Just FYI, I have discovered triggers almost 20 years ago when developing an application using an Informix DB (by that time they also had fancy stuff like in-memory table caching and views, etc., though no columnar storage yet in 1998).
Kind regards,
Dan
Hi,
I've been through all the links and comments you said above. In my case I have a table in BW. I want to access to this table, pre-process it and apply a AFL predictive function (Single Exponential Smoothing). Then, I want to store the results and access them from BW. After some research, I managed to find 5 different ways to approach this problem:
My question remains at most time the same: How should I modify my trigger to be able to launch my AFM/SQL/SAP PAA procedure?
Please, have a look to https://answers.sap.com/questions/148420/five-approaches-to-execute-a-predictive-afl-functi.html.
I think you may be able to help me. Thanks and regards!