DB2 trigger error

Question

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

What's missing? Your DB2 version and platform, for starters. — mustaccio, Jul 28 '16 at 19:17

score 1 · Answer 1 · answered Jul 28 '16 at 20:12

1

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
   @

answered Jul 28 '16 at 20:12

MichaelTiefenbacher

3,805
2
11
17

is '@' at the end intentional or a typo? – user6401178 Jul 28 '16 at 20:30
You have to have two terminators ";" is the one within the trigger code and I used "@" as the second (outer) one. You can specify this termoinator in DataStudio on the validation tab or via command line with db2 -td@ ... – MichaelTiefenbacher Jul 28 '16 at 20:35
i'm getting error still -- tried it with&witout `@`. not getting a specific error code this time though, a window popping up along the way saying "an error occurred when executing last statement(s)", asking me whether to continue. – user6401178 Jul 28 '16 at 20:45
i'm doing these on Eclipse – user6401178 Jul 28 '16 at 20:46
DataStudio is using Eclipse. Try following statement upfront (and use @) -- – MichaelTiefenbacher Jul 28 '16 at 20:52
upfront-- before 'create'? getting the same error still with it – user6401178 Jul 28 '16 at 20:55
yes upfront means before the create - try my example if this would work - it is proven to work – MichaelTiefenbacher Jul 28 '16 at 20:58
Which DB2 version are you using? Which OS? – MichaelTiefenbacher Jul 28 '16 at 21:15
db2 9.1 on Windows 10 – user6401178 Jul 28 '16 at 21:29
Ok - I should have asked that earlier - this syntax is not possible in DB2 9 - a workaround for you would be a INSERT trigger and a separate UPDATE trigger. – MichaelTiefenbacher Jul 28 '16 at 21:35
your previous Q triggered somthing here. :) db2's been giving odd errors like this before to my connections going thru Windows 10 to the tunnel while things are all fine on Eclipse. i think i should dig into its configuration on Windows. – user6401178 Jul 28 '16 at 21:37
separate insert&update triggers is what i have now. i want them together in one. – user6401178 Jul 28 '16 at 21:38

DB2 trigger error

1 Answers1