Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: DB2 trigger trouble

    Hello

    I am building a data base using DB2 9.7 and i am having a bit of trouble with creating a trigger.
    I have to tables:
    1. tab witch contains the data with the structure id(int), name(varchar 50), subscribe (int) witch cand be either 1 if i want to monitor the row or 0 if not
    2. updateTraker with the structure id_update(int autoincrement), name(varchar 50) witch will be populated through the trigger with the rows from tab that have been updated.

    I have done this in the past on Microsoft SQL Server 2005, MySQL and PostgreSQL and it worked, but for some reason i am getting a strange error here.

    This is the statement i use to create the trigger:
    Code:
    create trigger updateTab
    after update on tab
    REFERENCING NEW AS NEWROW
    for each row
    begin atomic
    	insert into updateTraker 
    		values (null, newrow.name) 
    		where newrow.subscribe=1;
    end
    and this is the error i get:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "end".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "end". Expected tokens may include: "JOIN <joined_table>


    I based my trigger on the example present in the offical documentation foud here: DB2 Database for Linux, UNIX, and Windows

    Does anibody have any ideas?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You have several things wrong with your trigger. If you are going to use "BEGIN ATOMIC - END" you need to use a different character for the statement termination other than the semicolon ( ; ) (most people use the @ symbol). Second, your syntax for the insert is incorrect. You cannot have a where clause with the values statement. And lastly, you do not really need the "BEGIN ATOMIC - END block. Try this:

    Code:
    create trigger updateTab
    after update on tab
    REFERENCING NEW AS NEWROW
    for each row when (newrow.subscribe=1)
    	insert into updateTraker  (name) 
    		values newrow.name) 
    ;
    Andy

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    Thank you very much. That solved my problem.
    Now how do i mark the topic solved?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You just did.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •