DB2触发非法令牌 [英] DB2 trigger illegal token

问题描述

相当新的DB2 sql，所以原谅我的无知:)
我有一个触发条件里面。我想根据条件插入一些参数。这里是：

Fairly new to DB2 sql, so forgive my ignorance :) I have a trigger with a condition inside. I want to then insert some params depending on the condition.. Here it is:

 CREATE OR REPLACE TRIGGER AUTO_INSERT_DEPO_NOMINEE
AFTER INSERT ON CA_ENTITLEMENT 
REFERENCING NEW AS N
FOR EACH ROW

    IF  NOT EXISTS (SELECT D.DEPO_CD FROM DEPO D WHERE D.DEPO_CD = N.DEPO_CD)
    THEN    
        INSERT INTO DEPO (DEPO_CD, DEPO_NME, BRANCH_CD, AUTO_GENERATED) VALUES(N.DEPO_CD, NULL, N.BRANCH_CD, 'Y');
    END IF;

我查看了DB2文档中的触发器以及if语句，至少对我的眼睛它似乎符合它，但是我在插入行上收到-104错误（非法符号令牌）。

I've looked at DB2 documentation for triggers and also for if statements, and at least to my eyes it appears to comply with it, however i get a -104 error (Illegal symbol token) on the insert line.

插入工作正常，只要我使用不是' N'。

The insert works fine provided i use values not from 'N'.

确定，如果我在if then语句中没有任何内容，但只有我没有任何内容才有效。

OK, it works if i have nothing in the if then statement.. but only if i have nothing!

谢谢

推荐答案

我的猜测是，DB2被您的语句终结者混淆。您使用分号终止 CREATE TRIGGER 语句，但同时在 CREATE TRIGGER 语句，在 INSERT 之后。

My guess would be that DB2 is confused by your statement terminators. You use a semicolon to terminate the CREATE TRIGGER statement, but at the same time you use the same terminator inside the CREATE TRIGGER statement, after the INSERT.

在您用于执行代码的任何客户端中，重新定义语句终止符在 END ID 之后，在 CREATE TRIGGER 的末尾放置终止符。例如，如果使用命令行处理器，将其保存到文件 trig.sql ：

In whatever client you use to execute your code, redefine the statement terminator to something else and place that terminator at the end of CREATE TRIGGER, after END ID. For example, if you use the command line processor, save this to a file trig.sql:

 CREATE OR REPLACE TRIGGER AUTO_INSERT_DEPO_NOMINEE
  AFTER INSERT ON CA_ENTITLEMENT 
  REFERENCING NEW AS N
  FOR EACH ROW

  IF  NOT EXISTS (SELECT D.DEPO_CD FROM DEPO D WHERE D.DEPO_CD = N.DEPO_CD)
  THEN    
    INSERT INTO DEPO (DEPO_CD, DEPO_NME, BRANCH_CD, AUTO_GENERATED) 
           VALUES(N.DEPO_CD, NULL, N.BRANCH_CD, 'Y');
  END IF@

然后运行它，将@指定为语句终结符： / p>

then run it, specifying "@" as the statement terminator:

db2 -td@ -f mytrig.sql

这篇关于DB2触发非法令牌的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！