Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2003
    Posts
    78

    Red face Unanswered: create trigger problem, need help!

    Hello,

    Here is my script to create a trigger:

    create trigger addrhst_after_insert
    after insert on addrhst
    referencing new as new_addr
    for each row mode db2sql
    begin
    declare cmpny char(20);
    declare div char(20);
    declare ssn char(10);
    declare effD dec(8,0);
    declare effDNew dec(8,0);
    declare trmD dec(8,0);

    select aheffdt into :effD
    from addrhst a
    where a.ahcmpny = cmpny and
    a.ahdiv = div and
    a.ahssn = ssn and
    a.aheffdt < effDNew and
    (a.ahtrmdt = 0 or a.ahtrmdt > effDNew)
    order by aheffdt desc;

    set effDNew = new_addr.ahEffDt;
    set ssn = new_addr.ahssn;
    set cmpny = new_addr.ahcmpny;
    set div = new_addr.ahdiv;
    set trmD = subDays(Days(new_addr.ahEffDt),1);

    if(effD >= 0) then
    set transaction isolation level no commit;
    update addrhst a
    set a.ahtrmdt = trmD
    where a.ahcmpny = cmpny and
    a.ahdiv = div and
    a.ahssn = ssn and
    a.ahEffDt = effD;
    end if
    end

    Note: subDays is a user-defined function, and run correctly.

    I run the script in command center. Then I got the following error message:

    SQL0206N "TRMD
    " is not valid in the context where it is used.


    I'd appreciate any suggestions.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: create trigger problem, need help!

    Why noy just eliminate TRMD like this:

    create trigger addrhst_after_insert
    after insert on addrhst
    referencing new as new_addr
    for each row mode db2sql
    begin
    declare cmpny char(20);
    declare div char(20);
    declare ssn char(10);
    declare effD dec(8,0);
    declare effDNew dec(8,0);
    -- declare trmD dec(8,0);

    select aheffdt into :effD
    from addrhst a
    where a.ahcmpny = cmpny and
    a.ahdiv = div and
    a.ahssn = ssn and
    a.aheffdt < effDNew and
    (a.ahtrmdt = 0 or a.ahtrmdt > effDNew)
    order by aheffdt desc;

    set effDNew = new_addr.ahEffDt;
    set ssn = new_addr.ahssn;
    set cmpny = new_addr.ahcmpny;
    set div = new_addr.ahdiv;
    -- set trmD = subDays(Days(new_addr.ahEffDt),1);

    if(effD >= 0) then
    set transaction isolation level no commit;
    update addrhst a
    set a.ahtrmdt = subDays(Days(new_addr.ahEffDt),1)
    where a.ahcmpny = cmpny and
    a.ahdiv = div and
    a.ahssn = ssn and
    a.ahEffDt = effD;
    end if
    end


    Andy

    Originally posted by christine2003
    Hello,

    Here is my script to create a trigger:

    create trigger addrhst_after_insert
    after insert on addrhst
    referencing new as new_addr
    for each row mode db2sql
    begin
    declare cmpny char(20);
    declare div char(20);
    declare ssn char(10);
    declare effD dec(8,0);
    declare effDNew dec(8,0);
    declare trmD dec(8,0);

    select aheffdt into :effD
    from addrhst a
    where a.ahcmpny = cmpny and
    a.ahdiv = div and
    a.ahssn = ssn and
    a.aheffdt < effDNew and
    (a.ahtrmdt = 0 or a.ahtrmdt > effDNew)
    order by aheffdt desc;

    set effDNew = new_addr.ahEffDt;
    set ssn = new_addr.ahssn;
    set cmpny = new_addr.ahcmpny;
    set div = new_addr.ahdiv;
    set trmD = subDays(Days(new_addr.ahEffDt),1);

    if(effD >= 0) then
    set transaction isolation level no commit;
    update addrhst a
    set a.ahtrmdt = trmD
    where a.ahcmpny = cmpny and
    a.ahdiv = div and
    a.ahssn = ssn and
    a.ahEffDt = effD;
    end if
    end

    Note: subDays is a user-defined function, and run correctly.

    I run the script in command center. Then I got the following error message:

    SQL0206N "TRMD
    " is not valid in the context where it is used.


    I'd appreciate any suggestions.

  3. #3
    Join Date
    Jul 2003
    Posts
    78

    Re: create trigger problem, need help!

    Thank you, but another error message:

    SQL0206N "NEW_ADDR.AHEFFDT
    " is not valid in the context where it is used.

    Any idea then?


    Originally posted by ARWinner
    Why noy just eliminate TRMD like this:

    create trigger addrhst_after_insert
    after insert on addrhst
    referencing new as new_addr
    for each row mode db2sql
    begin
    declare cmpny char(20);
    declare div char(20);
    declare ssn char(10);
    declare effD dec(8,0);
    declare effDNew dec(8,0);
    -- declare trmD dec(8,0);

    select aheffdt into :effD
    from addrhst a
    where a.ahcmpny = cmpny and
    a.ahdiv = div and
    a.ahssn = ssn and
    a.aheffdt < effDNew and
    (a.ahtrmdt = 0 or a.ahtrmdt > effDNew)
    order by aheffdt desc;

    set effDNew = new_addr.ahEffDt;
    set ssn = new_addr.ahssn;
    set cmpny = new_addr.ahcmpny;
    set div = new_addr.ahdiv;
    -- set trmD = subDays(Days(new_addr.ahEffDt),1);

    if(effD >= 0) then
    set transaction isolation level no commit;
    update addrhst a
    set a.ahtrmdt = subDays(Days(new_addr.ahEffDt),1)
    where a.ahcmpny = cmpny and
    a.ahdiv = div and
    a.ahssn = ssn and
    a.ahEffDt = effD;
    end if
    end


    Andy

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

    Re: create trigger problem, need help!

    Try this. after the last END statement put the @ there:
    END @

    Then goto the Tools->Tool Settings
    and set the Statement Termination Character to @ from ;

    Do not forget to set it back when you are done.

    HTH

    Andy

    Originally posted by christine2003
    Thank you, but another error message:

    SQL0206N "NEW_ADDR.AHEFFDT
    " is not valid in the context where it is used.

    Any idea then?

  5. #5
    Join Date
    Jul 2003
    Posts
    78

    Re: create trigger problem, need help!

    sorry, that doesn't help. More wierd problems invoked. Is there anything wrong with the syntax? Thank you



    Originally posted by ARWinner
    Try this. after the last END statement put the @ there:
    END @

    Then goto the Tools->Tool Settings
    and set the Statement Termination Character to @ from ;

    Do not forget to set it back when you are done.

    HTH

    Andy

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: create trigger problem, need help!

    No, I do not see anything wrong with the syntax right offhand.
    I do see numerous logic errors though.
    for example in the first query cmpny, div, ssn, effDnew used in the where clause are not set to anything.

    I would suggest that you develop this trigger in parts. Start small and work up to what you want. That way when you get one of those "NOT VALID IN THE CONTEXT" errors, you will know where it is being complained about.

    Andy

    Originally posted by christine2003
    sorry, that doesn't help. More wierd problems invoked. Is there anything wrong with the syntax? Thank you

  7. #7
    Join Date
    Jul 2003
    Posts
    78

    Re: create trigger problem, need help!

    Thanks a lot for replys. But I guess some thing wierd about db2 environment, I try to start a piece, and no error message, but I could not see anything under the trigger in control center. What's happening?


    Originally posted by ARWinner
    No, I do not see anything wrong with the syntax right offhand.
    I do see numerous logic errors though.
    for example in the first query cmpny, div, ssn, effDnew used in the where clause are not set to anything.

    I would suggest that you develop this trigger in parts. Start small and work up to what you want. That way when you get one of those "NOT VALID IN THE CONTEXT" errors, you will know where it is being complained about.

    Andy

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: create trigger problem, need help!

    Check filter, also try doing a refresh.

    Andy

    Originally posted by christine2003
    Thanks a lot for replys. But I guess some thing wierd about db2 environment, I try to start a piece, and no error message, but I could not see anything under the trigger in control center. What's happening?

  9. #9
    Join Date
    Jul 2003
    Posts
    78

    Angry Re: create trigger problem, need help!

    I did, but still nothing.

    Originally posted by ARWinner
    Check filter, also try doing a refresh.

    Andy

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: create trigger problem, need help!

    What do you get when you:

    SELECT * from syscat.functions

    Andy

    Originally posted by christine2003
    I did, but still nothing.

  11. #11
    Join Date
    Jul 2003
    Posts
    78

    Re: create trigger problem, need help!

    I get system functions list table, that's fine. I think that it is not the permission problem.

    Originally posted by ARWinner
    What do you get when you:

    SELECT * from syscat.functions

    Andy

  12. #12
    Join Date
    Jul 2003
    Posts
    78

    Re: create trigger problem, need help!

    Do you where I could check logs for command center? I tried connect using db2admin, still doesn't help at all. Thanks a lot.

    Originally posted by christine2003
    I get system functions list table, that's fine. I think that it is not the permission problem.

  13. #13
    Join Date
    Jul 2003
    Posts
    78

    Angry Re: create trigger problem, need help!

    And I check systriggers, there is nothing created. There must be somewhere have something logged.

    Originally posted by christine2003
    Do you where I could check logs for command center? I tried connect using db2admin, still doesn't help at all. Thanks a lot.

  14. #14
    Join Date
    Jul 2003
    Posts
    78

    Talking Re: create trigger problem, need help!

    Hello,

    Just want to tell you guys, that I make it. I use the same script and get it done use control center->create trigger wiszard, but still cannot get anything back from command center. It is strange, but I am happy it is done.

    Thanks for your replys and wonderful ideas


    Originally posted by christine2003
    And I check systriggers, there is nothing created. There must be somewhere have something logged.

  15. #15
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: create trigger problem, need help!

    Really strange ...

    Well, thanks for giving us a feedback

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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