CODEWITHSUNDEEP
triggersdb2db2-luw
RUC
RUC

Reputation: 53

DB2 Trigger creation setting in aqua studio

I am trying a create Triggers from Aquastudio and ended up with below error.

CREATE TRIGGER VO.ADMIN_TOOL_T02
  AFTER
  DELETE
  ON VO.ADMIN_TOOL
  REFERENCING
    OLD AS OLD
  FOR EACH ROW
  MODE DB2SQL
  BEGIN ATOMIC
      INSERT INTO VO.ADMIN_TOOL_AUD(
                TOOL_URI_X,
                MIGRATED_F,
                STATUS_X,
                UPDATED_LOGIN_NM,
                LAST_USE_TS,
                UPDATED_GUID_X,
                UPDATED_TS,
                ACTION_C)
        VALUES(old.TOOL_URI_X,
                old.MIGRATED_F,
                old.STATUS_X,
                old.UPDATED_LOGIN_NM,
                old.LAST_USE_TS,
                old.UPDATED_LOGIN_NM,
                CURRENT TIMESTAMP,
                'D');
  END;

Error as below :

Describe Error: Failed to disable execution plan: "SYSTOOLS.EXPLAIN_OPERATOR" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.67.28

[Error] Script lines: 1-10 ------------------------- An unexpected token "END-OF-STATEMENT" was found following "RENT TIMESTAMP, 'I')". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.28

[Executed: 8/14/2018 6:25:14 AM] [Execution: 0ms]

[Error] Script lines: 11-12 ------------------------ An unexpected token "END-OF-STATEMENT" was found following "END ". Expected tokens may include: "JOIN ".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.28

[Executed: 8/14/2018 6:25:14 AM] [Execution: 0ms]

If I remove BEGIN ATOMIC , END then it is working. As per company standards I need to include them and make my aqua studio to work with those settings. Please advice.

Upvotes: 1

Views: 524

Answers (2)

RUC
RUC

Reputation: 53

I still see the error while running. Below are the screenshots from my aquadata studio.

General

Scripts

Below is the script and error

Error

Upvotes: 0

mao
mao

Reputation: 12267

This is a frequently asked question. When you have a multi-statement block (user defined function, compound atomic SQL, trigger, stored procedure ... ), you need to tell the Db2-server the difference between an intra-statement delimiter and the overall-statement delimiter. In your example there is only one statement in the trigger (the insert), but there can be many each will be delimited by ; (for example) but the terminating delimiter for the block needs to be a different character.

Typically people use ; semicolon for the intra-statement delimiter and a different character like @ (or ! etc) for the alternative statement delimiter.

The method to do this varies with the tool that you use (whether a GUI or a command-line etc). Look at the settings for your GUI tool and learn how to do it.

For Aqua Data Studio v19, click File > Options > General , then see "@ at End of line terminates statement" and tick that box then click OK to apply the change. It also lets you use "/ at End of line terminates statement" if you prefer.

Check also if your site uses automated build tools that already have a standard for such a delimiter/terminator, because you should then use the same character. That will allow scripts and GUIs to both work with the same delimiters. SQL Scripts can also dynamically choose the alternative-delimiter if the script-processor is the Db2 CLP via the --#SET TERMINATOR method.

Upvotes: 1

Related Questions