Triggers on $TA tables (Text Analysis Tables)

[DeepakVarandani]

Hi Experts,

    Can we create triggers on Text Analysis Tables i.e. $TA tables (index tables) ??

Also, I have created an Information View (Projection Type on $TA table) and tried to use it in trigger with "INSTEAD OF" option.

But, it is not allowing to create trigger and giving below error :-

Trigger :-

CREATE TRIGGER TEST_TA

INSTEAD OF INSERT ON "TA_TEST"

FOR EACH ROW

BEGIN

    INSERT INTO SAMPLE VALUES(5);

END;

Error :-

Could not execute 'CREATE TRIGGER TEST_TA INSTEAD OF INSERT ON "TA_TEST" FOR EACH ROW BEGIN INSERT INTO SAMPLE ...'

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "INSTEAD": line 2 col 1 (at pos 26)

[lbreddemann]

Up to SPS 9 INSTEAD OF triggers are not supported.

So no wonder you receive an error message.

With SPS 10 this changed, so you may try this on the current version of SAP HANA.

- Lars

[DeepakVarandani]

Hi Lars,

Thanks for your response !!!  

I am using SPS 9 version currently. Any idea whether we can use triggers on Text Analysis tables ($TA table) ??

[lbreddemann]

I haven't tried that, but since the $TA tables are system generated/managed I would say: no fiddling with those.

- Lars

[Former Member]

Thanks Lars.

But we have a need of trigger on $TA tables where we wanted to carry out some processing on a real time basis whenever there is negative sentiment. Trigger on $ table seems to be an only option but it did not work. (Trigger on $TA not allowed).

Other option we explored was to have trigger created on the base table on which "FULL TEXT INDEX" is created and then look for the $TA values for negative sentiment provided $TA is populated before my custom trigger (after insert) on base table is executed. This also did not work because we don't know if $TA is populated first then "after insert" trigger  is executed.

Do you have any suggestions on how to address this need in HANA?

[lbreddemann]

Ok, so you want to have sort of real time reaction to a negative sentiment that is found by analyzing the full text.

Triggers are not the way to do that here, as they are triggered whenever there is a single record change (besides not being supported on system managed tables).

Alternatively you might go for a regularly scheduled job that checks for those negative sentiment ratings or you look into smart data streaming where you can monitor incoming data as it comes in.

- Lars

[Former Member]

Thanks for your suggestion.