Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    11

    Unanswered: DB2 Trigger compilation error

    Hi,

    I am creating a trigger in DB2 express version.

    When i use the following syntax to create trigger

    CREATE TRIGGER USER_PK_TRIGGER
    BEFORE INSERT On users
    REFERENCING NEW As N
    FOR EACH ROW
    Begin
    if((N.user_id is null) or (N.user_id <= 0)) then
    set N.user_id = nextval for user_seq;
    end if;
    End@


    I am getting the following error.

    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 "(" was found following "EACH ROW Begin if(".
    Expected tokens may include: "<space>". LINE NUMBER=6. SQLSTATE=42601

    But when i change my create trigger syntax to include atomic word it works.

    CREATE TRIGGER USER_PK_TRIGGER
    BEFORE INSERT On users
    REFERENCING NEW As N
    FOR EACH ROW
    Begin Atomic
    if((N.user_id is null) or (N.user_id <= 0)) then
    set N.user_id = nextval for user_seq;
    end if;
    End@

    Why do i need to make my trigger atomic? When it commits does it commit everything (in the ongoing transaction) or only the compound statement in the trigger? What is wrong in my first create trigger syntax?

    I am new in DB2 and in most of the examples they have shown that something like first syntax should work, i am not able to figure what what is wrong here. Any help to fix this will be highly appreciated.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The ATOMIC indicates that if an error occurs in the compound statement all SQL statements in the compound statement are rolled back and nothing else in the compound statement is processed.

    Triggers must have ATOMIC compound statements. It is in the syntax diagrams of the manusl.

    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
  •