Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2006
    Posts
    15

    Unanswered: trigger help needed - connection problem?

    I am getting a error :
    A database connection does not exist.
    when I try to run my new trigger.

    DB2 personal edition

    trigger code;

    create trigger newtrig
    after insert on on_hand
    for each row
    begin
    update on_hand set qty = qty + 1 ;
    end;

    Any idea's / thanks
    BJ


  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you trying to create the trigger? You must connect to the datbase before you can create any objects in the database.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2006
    Posts
    15

    database connection - trigger

    I am already connected and using a database.
    Do I have to reconnect just before running
    a trigger? and if so how ?

    PS- I am running the trigger in sql script I hope that right!
    Thanks very much
    BJ

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since the trigger has several ";" embedded in the code, you must a different terminator than the default when you run the CLI to create the trigger. Put a "@" in place of the ";" after the last "end" statement and run the script with the following command:

    db2 -td@ -vf script-name
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2005
    Posts
    41
    Try the following:

    drop trigger newtrig@
    create trigger newtrig
    after insert on on_hand
    for each row
    mode db2sql
    update on_hand set qty = qty + 1 @

    Not too sure about the logic you are trying to implement, but the above syntax is ok.

    Graham Martin
    http://www.ibm.com/software/data/db2/migration/

  6. #6
    Join Date
    Jan 2006
    Posts
    15

    help on triggers

    Instead of a script I went to: control center - tree
    triggers - actions - create trigger.
    Its much easier for a new user like me, it works fine but now
    when I try to run a trigger that updates my on_hand table
    using ADJUST table for the transaction data.
    I get an error .
    Here is my trigger:

    update on_hand qty = qty - ( select qty from adjust where adjust.part = on_hand.part);
    end

    Thanks
    BJ

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    update on_hand SET qty......

  8. #8
    Join Date
    Jan 2006
    Posts
    15

    Question error with trigger

    I have listed my trigger code below :


    begin
    update on_hand set qty = qty - ( adjust.qty where adjust.part = on.hand.part);
    end

    table structures;
    table - ADJUST
    id integer
    part char
    qty integer




    table - ON_HAND
    id integer
    part char
    qty integer

    Thanks
    BJ

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by bobjohnson360
    begin
    update on_hand set qty = qty - ( adjust.qty where adjust.part = on.hand.part);
    end
    Part in red is wrong. There got to be a SELECT and a FROM clause. Also in your earlier post you have a WHERE clause where you apears to be trying to join two tables but only listing one.

    It would also help if you have listed the error message that you are receiving
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    update on_hand set on_hand.qty = on_hand.qty - ( Select a.qty from adjust a, on_hand o where a.id = o.id and a.part = o.part);

    This is based on your table structure. Part in red I have added since it apears that ID column is a key. Modefy the where cluase based on the key in your table to avoid the duplicates.

    table structures;
    table - ADJUST
    id integer
    part char
    qty integer

    table - ON_HAND
    id integer
    part char
    qty integer
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Jan 2006
    Posts
    15

    trigger update syntax

    Your posted code does work however it updates
    every row not just the matching row ( id & part) .
    In the trigger wizard I selected
    (for each row) should it be( for each statement)?

    By the way you were right the 'id' field is a PrimaryKey
    in both tables.

    extra details : I am trying to use ADJUST table as a inventory adjustment table subtracting from on_hand
    to further explain my objective.

    Thanks very much
    BJ
    Last edited by bobjohnson360; 01-10-06 at 18:16.

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    You need to figure out how Cursor works. I do not do those.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  13. #13
    Join Date
    Jan 2006
    Posts
    15

    trigger update syntax

    I am sure you are right . I have not Tackled CURSORS
    yet but may have to in order to accomplish my task in
    this case.

    However keep in mind that in my trigger the table ADJUST
    never has more than 1 row of data in it and in fact gets
    deleted by the trigger right after all updates are performed.

    example:
    table - ADJUST

    id part qty
    1 a1000 12

    table on_hand

    id part qty
    1 a1000 100
    ( after trigger runs)

    table on_hand
    id part qty
    1 a1000 88


    any body have any ideas ? Thanks
    BJ

  14. #14
    Join Date
    Apr 2004
    Posts
    54

    Talking

    I can't figure out what you have in mind writing such trigger:

    create trigger newtrig
    after insert on [on_hand]
    for each row
    begin
    -- Do you wish update the same table?
    update [on_hand] set qty = qty + 1 ; -- Do you wish update all the table?
    end; -- May be you need change delimiter?

  15. #15
    Join Date
    Jan 2006
    Posts
    15

    trigger update

    what I want to do is update ( adjust) the on_hand table
    using the ADJUST table the ADJUST table is nothing more
    than a transaction table .
    example :
    if qty on hand for part a1000 is 100 and I want to write
    off 15 I would go to ADJUST table and enter :
    part a1000 qty 15 and the trigger would process
    the transaction for me.
    I am sure one could program a front end form to do this
    but I am not there yet.At this point a trigger seems to me
    is the fast way to do this task.

    I am open to any ideas / Thanks
    BJ

Posting Permissions

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