Trigger to update specific row from table SAP HANA

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?

Solution

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.