Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    12

    Unanswered: DB2 Trigger - Please help!!!

    I have this table:

    id char 10
    id_a char 3
    id_b char 3
    id_c char 3
    id_d char 1


    For Ex: ID = "1234A6789"

    The 5th position of the ID can be A, B, or C. We based on this letter to break the ID down into id_a, id_b, id_c, id_d and insert them into the table.

    If 5th position = "A" then
    id_a = substr(id,1,3)
    id_b = substr(id,4,3)
    id_c = substr(id,7,3)
    id_d = substr(id,10,1)

    If 5th position = "B" then
    id_a = substr(id,4,3)
    id_b = substr(id,7,3)
    id_c = substr(id,1,3)
    id_d = substr(id,10,1)

    If 5th position = "C" then
    id_a = substr(id,7,3)
    id_b = substr(id,4,3)
    id_c = substr(id,1,3)
    id_d = substr(id,10,1)

    Would you please show me the way to do this in BEFORE INSERT TRIGGER using CASE (5th position) to insert it into the table. Thank you very much.
    Last edited by xauxi; 08-26-02 at 20:02.

  2. #2
    Join Date
    Aug 2002
    Posts
    2
    Hi Xauxi,

    > For Ex: ID = "1234A6789"
    But this is only char(9)

    > Would you please show me the way to do this in BEFORE INSERT
    > TRIGGER using CASE (5th position) to insert it into the table. Thank
    > you very much.
    It's not much like CASE(5th position), but what about this:

    create trigger trigtest.trigger
    no cascade before insert on trigtest.table
    referencing new as nu
    for each row mode db2sql
    when (substr(nu.id,5,1) in ('A','B','C'))
    set id_a =
    case
    when (substr(nu.id,5,1) = 'A')
    then substr(nu.id,1,3)
    else
    case
    when (substr(nu.id,5,1) = 'B')
    then substr(nu.id,4,3)
    else
    substr(nu.id,7,3)
    end
    end,
    id_b =
    case
    when (substr(nu.id,5,1) = 'B')
    then substr(nu.id,7,3)
    else substr(nu.id,4,3)
    end,
    id_c =
    case
    when (substr(nu.id,5,1) = 'A')
    then substr(nu.id,7,3)
    else substr(nu.id,1,3)
    end,
    id_d = substr(nu.id,10,1);

    The Results are like this:

    ID ID_A ID_B ID_C ID_D
    ---------- ---- ---- ---- ----
    1234A5678 123 4A5 678
    1234B5678 4B5 678 123
    1234C5678 678 4C5 123

    (I told you your ID had only 9 chars), so ID_D is always " "

    Hope this helps

    kt1311

Posting Permissions

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