"After Update of Attribute_name on Table_name " trigger not working properly

[Former Member]

Hi All,

• Trigger for  "After Update of Attribute_name on Table_name"
• Objective : Fire trigger only when update on single attribute (column), not for all attributes.
• It works fine in Oracle, PostgreSQL but not working properly in HANA.
• In HANA it fire on every attribute of table, instead of specified (e.g. region in following script)

CREATE TABLE salestest (region VARCHAR(100), product VARCHAR(100), amount INTEGER);
CREATE TABLE salestest_dummy (region VARCHAR(100), product VARCHAR(100), amount INTEGER);

insert into  salestest (region, product, amount) values('IN', 'cold coffee', 10);
insert into  salestest (region, product, amount) values('US', 'cold coffee', 20);

select * from salestest; // Display 2 rows
select * from  salestest_dummy;// Display 0 rows

CREATE  TRIGGER salestest_after_update
AFTER UPDATE of region  ON salestest   FOR EACH ROW
BEGIN
   INSERT INTO salestest_dummy (region, product, amount )
                        VALUES ('TESTREGION','TEA', 10);
END;

update salestest set region= 'Dummy' where amount=10;// trigger work as per need

select * from  salestest_dummy ;// Display 1 rows, correct here

update salestest set product= 'Coffee' where amount=10; //trigger not work properly, it insert new row to salestest_dummy

select * from  salestest_dummy ;// Display 2 rows, wrong here

Is it any mistake I did? or any workaround for above script?

Thanks,

Somnath A. Kadam

Message was edited by: Tom Flanagan

[lbreddemann · Accepted solution]

Hi Somnath,

up to SPS 8 SAP HANA does not provide the feature to restrict trigger actions for specific columns.

We don't state that we do so in the documentation. Instead it is pretty clear that the current trigger option is a rather simple tool (especially compared to other trigger implementations).

AFAIK a feature like column-specific trigger actions might be included in later releases - but as usually, we will have to wait until the feature is actually available.

Until then, you could go on and manually check for changed values in the columns you are interested in.

- Lars

[Former Member · Accepted solution]

True say Lars. HANA could sure use a more comprehensive core SQL implementation. Here's hoping.

[lbreddemann · Accepted solution]

only if SQL standard finally kicks out triggers heheh

[Former Member]

I don't think this is a bug, you asked for an INSERT and you got one. Did you consider the use of UPSERT?