Skip to Content
author's profile photo Mathias Scherer
0
Mathias Scherer

use "create trigger" statements in hdbsql command line tool

Posted on Dec 04, 2014 at 02:33 PM 38 Views

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Lars Breddemann
    Lars Breddemann
    Posted on Dec 04, 2014 at 03:02 PM
    2

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Mathias Scherer
      Dec 04, 2014 at 03:58 PM

      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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.