cursor inside trigger

[Former Member]

Hi all,

i wanted to use cursor inside the trigger but i am getting syntax error. i am not able to remove it.error is "AP DBTech JDBC: [257] (at 92): sql syntax error: incorrect syntax near "declare": line 3 col 1 (at pos 92) ".need urgent help.code is below:

CREATE TRIGGER "RGAJULA"."TRIGGER1" AFTER UPDATE ON "RGAJULA"."ZOTC_SETTLE2" FOR EACH ROW

declare CURSOR MYCUR FOR

SELECT "FIELDNAME" FROM "RGAJULA"."ZOTC_CHGLOG_OPTN" WHERE TABNAME = 'ZOTC_SETTLE';

BEGIN

OPEN MYCUR;

declare v_field varchar (30);

FOR cur_row as MYCUR DO

FETCH MYCUR INTO V_FIELD;

IF :new.V_FIELD <> :old.V_FIELD

THEN insert

into "RGAJULA"."ZOTC_CDPOS" (MANDT,

  ZSETTLEDOC,

  ZSETTLEITM,

  CHANGENR,

  FNAME,

  CHNGIND,

  VALUE_NEW,

  VALUE_OLD ) VALUES(:new.MANDT,

  :new.ZSETTLEDOC,

  :new.ZSETTLEITM,

  :V_ID,

  :V_FIELD,

  'I',

  :new.V_FIELD,

  ' ' ) ;

END IF ;

END FOR;

CLOSE MYCUR;

END;

[Former Member]

AS  we capture the application user name using  application_user_name, in the same way can we capture application T-code( From ECC) used to update the table.??

[lbreddemann]

There's no standard functionality for that.

However, what you could do is to access the APPLICATION_* session variables and store their values.

The NetWeaver DBSL maintains them usually with the current ABAP user and ABAP module location - which is actually more helpful than the T-code.

[pfefferf]

Hello Askash,

which HANA revision/SPS you are using?

To my knowlege cursors are supported in triggers since SPS10. In the reference guides before SPS10 cursors are not listed as supported SQLScript statements. This changed with SPS10.

In your case you can reach your result w/o using a cursor. Just select/join your data from the :new parameter and your table (which you access with the cursor) and store in a temporary table. Afterwards do an insert of the temporary table into your target table. You can also avoid the temporary table if you wanna. This avoids the usage of the cursor and in consequence the usage of inefficient imperative logic.

Best Regards,

Florian

[Former Member]

can you please give some code examples.

my requirement is to select fields from table ZOTC_CHGLOG_OPTN and capture the changes of only those fields in table ZOTC_SETTLE2.

thanks,

akash

[Former Member]

really appreciate if you could provide some  sample code.

[pfefferf]

Hello Akash,

unfortunately selects in form <table> = select x from tab; are also still not supported. W/o thinking to much about the problem, the simplest solution which comes in my mind at the moment is to create a procedure which gets the table name, field name, new value and old value as parameters. The procedure then checks with SELECT x INTO var ... if logging of your field should be done or not and writes the log entry if necessary. The procedure can/has to be called in the triggers for all fields which should be perhaps logged. Of course is this no "nice" approach, because for each new field again changes have to be done. But that's the situation at the moment.

Maybe you can also think about the approach logging changes in the form you wanna do it. Creating for each single field a new line in the log table makes more noise than having an advantage in my opinion. Maybe it is more useful to log the complete changed line or using HISTORY COLUMN tables (CREATE TABLE - SAP HANA SQL and System Views Reference - SAP Library).

Best Regards,

Florian

[Former Member]

Hi Florian,

Also wanted to know that can we capture the t-code used to update or change the table entry from ECC using the trigger.