Cursor inside Trigger

[Pascal]

Hi There,

i am trying to create a Trigger. The Trigger should be executed on an "AFTER INSERT ON <TABLE> REFERENCING NEW ROW CURRENTMESSAGE FOR EACH ROW"

This works well for a limited version of my task, so the trigger gets created.

BUT when I want to iterate through a select statement to get some values from another table and add a Cursor inside the trigger  ...

DECLARE CURSOR cursor_GeoFence FOR

SELECT * FROM "GEOFENCES";

FOR cursor_row as cursor_GeoFence DO

        LAT_geofence := :cursor_row.GEOFENCE_LATITUDE;

        LON_geofence := :cursor_row.GEOFENCE_LONGITUDE;

        RADIUS_geofence := :cursor_row.GEOFENCE_RADIUS;

        NAME_geofence := :cursor_row.GEOFENCE_NAME;

        GFID_geofence := cursor_row.GEOFENCE_ID;

        ...

         <do something for example insert into event table>

END FOR;

i get an error "Error: (dberror) 257 - sql syntax error: incorrect syntax: line 14 col 12 (at pos 736)" which is the line before the "DECLARE CURSOR" line. And when i put a block comment around that DECLARE-FOR Statement it works. So somehow the problem must be inside the above Cursor - FOR Statement.

Any ideas how I can solve that issue?

Maybe there is an alternative to do a "SELECT" statement and iterate through the resultset within a trigger?

Thanks!

pascal

[pfefferf · Accepted solution]

Hello Pascal,

cursors in triggers are supported since SPS10. I think you are using an SPS below that.

Best Regards,

Florian

[Pascal · Accepted solution]

Thanks Florian,

a) how do I find out what SPS is on my dedicated HANA Box ond the HANA Cloud Platform?

b) Isn`t there a workaround to do something else instead to get all the GeoFences from my table inside a trigger? Or can i call a procedure (with a cursor) from the Trigger?

any suggestion welcome

[pfefferf · Accepted solution]

Hello Pascal,

you can check the SPS by checking the revision number. In HANA Studio make e.g. a double click on the system to system "Overview" tab. In area "General Information" you find the version number. In the HANA Cloud Platform you find the information under "HANA Instances" as "Database Version".

• 1.00.8x -> SPS08
• 1.00.09x -> SPS09
• 1.00.10x -> SPS10

Up to SPS09 I do not know a solution for your requirement. Calling a procedure will also not help you, because for called procedures the same restrictions are valid as for the trigger body. So also no cursors.

Regards,

Florian

[Pascal · Accepted solution]

So it seems i have SPS 9 as i am on 1.00.96.00.1432206182.

[Pascal · Accepted solution]

Okay, i did a workaround with a "while do ... end while" and incremented by the geofence id assuming that the geofences will never be deleted and no "id gaps" will happen.

-- calculates the length of the table -> # geofences / countgf

SELECT COUNT(*) INTO countgf FROM "COILADM"."GEOFENCES”;

-- While loop ends if  temp_i = countgf

temp_i := 1;

WHILE (temp_i <= countgf) DO

     <do something>

     temp_i := temp_i + 1;

END WHILE

So now another question arises. How would i catch the case, like mentioned above, if there would be a geofence deleted and hence an ID would be missing.

Is there something like a "hasNext" command, instead of using the id to increment and loop through the resultset?

thanks!