DB2触发错误 [英] DB2 trigger error

问题描述

当有 THIS_TABLE 的插入或更新时，我有以下触发器触发 THAT_TABLE 插入。

以下查询的工作方式应该如此。同样 - 当我评论 line-A 和取消注释（和删除 c>或 in） line-B 时，它会起作用。

 创建或替换触发器t99 
 
更新后 -  line-A 
  - 或者在THIS_TABLE 
上插入行-B 
将每个行的新参数作为newRow 
 MODE DB2SQL 
插入到THAT_TABLE 
值（newRow.tnumber，'O' ，newRow.cocode，CURRENT TIMESTAMP，null，null）
  

但是，当我发现以下错误时取消注释两行：

在之后找到一个意外的标记OR INSERT。预期令牌可能包括：.. SQLCODE = -104，SQLSTATE = 42601，DRIVER = 4.21.29

缺少什么？ / p>

我在Windows 10上运行DB2 9.1

解决方案

在一个BIGIN END块中的声明，如我的例子：

 创建表test.triggerevent（id int，text varchar（50）） @ 
创建表test.log（id int，text varchar（50），ts timestamp）@ 
 
创建或替换触发器tlog 
更新后或插入
 test.triggerevent 
将每个行模式引用为newRow 
 db2sql 
 begin 
 insert into test.log values（newRow.ID，newRow.text，current timestamp）; 
 end 
 @ 
  

i have the following trigger that triggers an insertion on THAT_TABLE whenever there's an insertion or update to THIS_TABLE.

The following query works as is the way it should. Likewise-- it works when i comment line-A and uncomment (and remove or in) line-B.

    create or replace trigger t99 
    after  
    update -- line-A
    --or insert -- line-B
    on THIS_TABLE
    REFERENCING new as newRow
    for each row MODE DB2SQL
    insert into THAT_TABLE
        values  (newRow.tnumber, 'O', newRow.cocode, CURRENT TIMESTAMP, null, null)

However, i get the following error when i uncomment both lines:

An unexpected token "OR INSERT" was found following "". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.21.29

What's missing?

I am running DB2 9.1 on Windows 10

解决方案

Try enclosing your statement in a BIGIN END block like in my example:

create table test.triggerevent (id int, text varchar(50))@
create table test.log (id int, text varchar(50), ts timestamp)@

create or replace trigger tlog
   after update or insert 
   on test.triggerevent
   referencing new as newRow
   for each row mode db2sql
   begin
     insert into test.log values (newRow.ID, newRow.text, current timestamp);
   end
   @

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