use "create trigger" statements in hdbsql command line tool

[former_member410684]

Hello colleagues,

I have to use a complex hdbsql statement loke this in a script:

"CREATE TRIGGER \"SAP_CUAN_CRM\".\"SAP_IC_EXTR_CRMM_BUT_FRG0041_S555_L566_U\" AFTER UPDATE ON \"SAP_CUAN_CRM\".\"CRMM_BUT_FRG0041\" REFERENCING NEW ROW CRMM_BUT_FRG0041_REF FOR EACH ROW \nBEGIN IF :CRMM_BUT_FRG0041_REF.CLIENT = '555' THEN INSERT INTO \"SAPANK\".\"CUAND_CE_IC_CFRG\" VALUES('555', :CRMM_BUT_FRG0041_REF.CLIENT, :CRMM_BUT_FRG0041_REF.PARTNER_GUID, :CRMM_BUT_FRG0041_REF.VALID_TO, CURRENT_UTCTIMESTAMP, 'U' );\nEND IF; \nEND;"

This statement has been read from the SYS.TRIGGERS view and my intention was to use it in a non-interactive hdbsql call on operating system level

I tried both to use it directly in an hdbsql call and to use a  file (I did not mask the quotes in the file!)  via hdbsql -I, but each time it is ending up in a syntax error. Tried to remove the newlines \n and the semicolons, without any success.

Does anyone have an idea hot to overcome the errors?

Thanks and best regards,

Mathias

[lbreddemann · Accepted solution]

Not sure how exactly you retrieved the statement text - when I select on TRIGGERS (leave the SYS away and just access the views via the public synonym!) I don't get escaped characters.

Anyhow, your problem is likely that the trigger contains SQLScript commands that are delimited by semi-colon (;) just as the CREATE TRIGGER command itself is.

As hdbsql assumes a statement to be over after the first ; you'll get syntax errors.

The option to overcome this is to chose another command separator e.g. ## and put it directly after your CREATE TRIGGER command.

You can then call

     hdbsql -U <userkey> -I <inputfile> -c ##

to run the statement.

- Lars

[former_member410684 · Accepted solution]

Hi Lars,

the statement text is return as result when calling a hdbsql select on Linux OS level on the TRIGGERS view and the definition column.

My intention was to use it as it is in a hdbsql call again. Failed as described above.

But your reply was the hint I needed to solve the problem.

Thanks a lot

I could even manage to use the statement directly in the hdbsql call on OS level by removing

the newlines and substituting the trailing ";" by another char combination as described by you.

Thanks again.

Best regards,

Mathias