Unable to create Triggers

[Former Member]

Hi Frzz,

There is a requirement where in I have two tables T1 and T2.

I need to move data from T1 to T2.

we are using BODS to load data to T1,when the record got inserted in HANA T1 Table I am trying to write a trigger to load the same record in T2 as well with the below trigger syntax.

But I am getting an error.

Please suggest me how I can achieve the same.

Sample Code:

CREATE TRIGGER TEST

AFTER INSERT ON TABLE1 FOR EACH ROW

BEGIN

DECLARE EMP_ID_VAR varchar(10);

DECLARE EMP_NAME_VAR VARCHAR(20);

SELECT EMP_ID INTO EMP_ID_VAR FROM TABLE1;

SELECT EMP_NAME INTO EMP_NAME_VAR FROM TABLE1;

INSERT INTO TABLE2(EMP_ID,EMP_NAME)

VALUES(:EMP_ID_VAR,:EMP_NAME_VAR);

END;

Error: SAP DBTech JDBC: [7]: feature not supported: select on the subject table (SOMAYAJIV.TABLE1) inside a trigger is not allowed

Please suggest whats wrong with the code.

[former_member186082 · Accepted solution]

Hi Krishna,

Subject_table_name and table used in Select query shouldn't be same as per my knowledge, this is not supported. This will not work.

I suggest two alternatives:

1. In the BODS job, map to Table2 also so that same job will load to both the tables.

2. Write a SP in HANA to copy data from Table1 to Table2 and call the SP in BODS job.

Regards,

Chandu.

[Former Member · Accepted solution]

Hi Chandra,

Thanks a lot for your valuable suggestions.

Best Regards,

Krishna.

[DeepakVarandani]

Hi Krishna,

   Triggers does not allow to use subject table in the body of trigger on which trigger is created.

You can use REFERENCING option and access the data of the new inserted row without performing select operation on the subject table.

Trigger :-

CREATE TRIGGER TEST

AFTER INSERT ON TABLE1

REFERENCING NEW ROW mynewrow

FOR EACH ROW

BEGIN

INSERT INTO TABLE2(EMP_ID,EMP_NAME)

VALUES(:mynewrow.EMP_ID,:mynewrow.EMP_NAME );

END;

[lbreddemann]

Hmm...

what exactly are you trying to do here?

The select statements could retrieve any number of records but still you want to use the result in an INSERT statement?

If you want to access the data that is _currently_ inserted, I suggest to read the documentation on triggers a bit more thoroughly. The new row data can be accessed directly - without selecting from the subject table again.

- Lars