Hana Studio(SQL脚本)触发器上的条件更新 [英] Conditional update on Hana Studio (SQL Script) trigger
问题描述
我有两个示例表:
TABLE1:
ID | COLUMN_B| COLUMN_C
_________|_________|_________
1 | 0 | 1
________|_________|_________
2 | 0 | 1
________|_________|_________
3 | 0 | 1
TABLE2:
ID | COLUMN_E| COLUMN_F
________|_________|________
1 | Y | X
________|_________|_________
2 | Y | X
________|_________|_________
3 | Y | X
我想做的是,当表1中的B列更新为特定值(例如100)时,使用SQL脚本在Hana Studio上编写触发器以更新表2中的E列. 表1中的行在表2中带有第一列(ID)引用.
What I would like to do is to write a trigger on Hana Studio using SQL Script to update column E from table 2 when column B from table 1 is updated to a specific value (let's say, 100). Rows from table 1 are referenced in table 2 with the first column (ID).
我编写了以下代码:
create trigger "UPDATE_TABLES"
after update of "COLUMN_B" on "TABLE1"
referencing new row as new, old row as old
for each row
begin
update "TABLE2"
set "COLUMN_E" = 'my new value'
where :old."COLUMN_B" = '100' and "TABLE1"."ID" = :old."ID";
end;
当我将第1行的TABLE1.COLUMN_B设置为100时,我希望代码将第1行的TABLE2.COLUMN_E更改为我的新值",但是什么也没有发生.有人可以指出我在这到底在做什么错吗?
When I set TABLE1.COLUMN_B on row 1 to 100, I expect the code to change TABLE2.COLUMN_E to "my new value" on row 1, but nothing happens. Could anyone point what exactly I am doing wrong here?
推荐答案
我认为触发代码中的UPDATE语句应该稍有不同,如下所示
I guess the UPDATE statement in the trigger code should be slighly different as follows
create trigger "UPDATE_TABLES"
after update of "COLUMN_B" on "TABLE1"
referencing new row as new, old row as old
for each row
begin
update "TABLE2"
set "COLUMN_E" = 'my new value'
where :new."COLUMN_B" = '100'
and TABLE2.ID = :old."ID";
end;
否则,在创建触发器时出现语法错误 您能验证一下吗?
Otherwise, I get syntax error while creating the trigger Could you please verify?
这篇关于Hana Studio(SQL脚本)触发器上的条件更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!