Trigger to update specific row from table SAP HANA

Question

I am trying to create a trigger on Hana Studio to update a given registry (column F2 from table T2) when an update happens in table T1. A row from T1 is referenced in table T2 with the column X1 (that is, X1 in T1 is equal to X2 on T2).

What I would like to do is to get the value from the column X1 from table T1 (that is the table that's being updated) and use it as a where clause to know which row I should update on table T2.

This is my trigger (or what I would like it to do):

create trigger TRIGGERNAME
after update on "SCHEMANAME.T1" for each row
begin
update "SCHEMANAME.T2" 
set F2 = "MY NEW VALUE" 
where X2 = X1
end;

Problem is X1 is a column from my updated row. Is there a way to access data from the updated row on my trigger?

score 1 · Accepted Answer · answered Sep 12 '18 at 02:33

You are looking for the REFERENCING new row as ... | old row as ... clause. Details on this can be found in the reference documentation.

A simple example:

Table setup

create table one (ID int primary key, F2 nvarchar(100));
create table two (ID int primary key, F2 nvarchar(100));

insert into one values (1, 'Bla');
insert into one values (2, 'Blupp');

insert into two values (1, 'Bla');
insert into two values (2, 'Blupp');

Create the trigger

create trigger "ONE_TO_TWO"
    after update 
    on "ONE"   
    referencing new row as new, old row as old 
    for each row
begin
    update "TWO" t
    set "F2" = :new."F2"
    where t."ID" = :old."ID";
end;

Check current data

select 
      one.id as ONE_ID, one.f2 as ONE_F2
    , two.id as TWO_ID, two.f2 as TWO_F2
from 
    one full outer join two
    on one.id = two.id;

/*
ONE_ID  ONE_F2  TWO_ID  TWO_F2
1       Bla     1       Bla   
2       Blupp   2       Blupp 
*/

Update a record and check data again

update "ONE" 
    set "F2" = 'CHANGED THIS'
    where "ID" = 1;

/*
ONE_ID  ONE_F2          TWO_ID  TWO_F2      
1       CHANGED THIS    1       CHANGED THIS
2       Blupp           2       Blupp       
*/

While using a trigger may often appear like a good approach, I recommend reconsidering what this is going to be used for and if there aren't any better approaches to handle the requirements. Triggers per se always introduce "magic" into the data model since they change the semantics of normal statements - your UPDATE suddenly updates multiple tables - without being obvious to the DB user. In fact, only if one actively looks for triggers there's a chance that the impact they have on how the database 'behaves' is understood.

For multi-table updates, a stored procedure may that handles the dependencies may be the more obvious and better maintainable way to achieve the goal.

Seems like the right path, but I still have one more doubt. I would like the trigger to fire only when a given column from table ONE is updated, not just on any update on table ONE. Is is possible to set a specific column on the " after update on "ONE" " statement? For example, if I care only about the F2 column updates on table ONE, is it possible to set the statement to " after update on "ONE"."F2" "? — Nicole, Sep 12 '18 at 13:07
Yes, the linked documentation shows how one can limit the trigger action to specific columns or exclude specific columns. If you update the question with this additional requirement then I’ll update the answer accordingly. — Lars Br., Sep 12 '18 at 13:17
I believe you answered the question I asked. Thank you. Since I have doubts that differ from what I asked, I asked a different question. https://stackoverflow.com/questions/52297810/conditional-update-on-hana-studio-sql-script-trigger Thank you for your time. — Nicole, Sep 12 '18 at 14:41

Trigger to update specific row from table SAP HANA

1 Answers1