avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 01, 2014 at 03:26 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 29, 2014 at 12:41 PM

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

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content