Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Duesseldorf - Germany - Europe - Earth - Galaxy
    Posts
    18

    Unanswered: DB2 Trigger - silly beginner's question ;-)

    Hi there,

    I'm currently trying to use more of DB2's features respectivley to tell our develpoers how to use them in an easy way.

    I like soundex, so I thougt to give them a chance top use soundex with less work. I tried adding a new clumn char(4) not null with default to a table. It should contain the soundex-values for the search-relevant column and the should have an index. It's oviously faster than using soundex in a where clause on both sides of the "=", as one of our developers suggested ;-)

    With an update sql, I filled the new column initially.

    Now I want to create an insert and an update trigger to fill and update this new column automatically. So our developers don't have even to think about how to fill it, they can use this transparently as my service ;-)

    I tried the following CREATE TRIGGER

    Given is a table with column NAME char(60) and column name_sx char(4) for the soundex-representation.

    The current sqlid is "MIS".

    I tried

    create trigger sx_mfirma_name_i
    after insert
    on mnutz referencing new as n
    for each row mode db2sql
    update n set n.name_sx = soundex(n.name) ;

    I get
    SQL0204N "MIS.N" is an undefined name. LINE NUMBER=3. SQLSTATE=42704

    with

    create trigger sx_mfirma_name_i
    after insert
    on mnutz referencing new_table as n
    for each row mode db2sql
    update n set n.name_sx = soundex(n.name) ;

    SQL0155N A trigger transition table cannot be modified. LINE NUMBER=3.
    SQLSTATE=42807

    when I use

    create trigger sx_mfirma_name_i
    after insert
    on mnutz
    for each row mode db2sql
    update mfirma set name_sx = soundex(name) ;

    I can create the trigger but it will update the whole table each time I insert a value.

    The same happens when I try this one:

    create trigger sx_mfirma_name_i
    after insert
    on mnutz referencing new as n
    for each row mode db2sql
    update mfirma set name_sx = soundex(name)
    where n.key = key;

    I can't imagine it's that difficult to just create a trigger that transparently changes one color in the same row that is updated or inserted.

    Can anyone help...?

    Regards,
    Volker.

  2. #2
    Join Date
    Dec 2003
    Location
    Duesseldorf - Germany - Europe - Earth - Galaxy
    Posts
    18

    Real beginner's fault ;-)))

    Sorry, but I found the reason ;-))

    A colleque and I tried to do this and we both worked with the same file on our AIX. There was the CREATE TRIGGER at the beginning and later older versions of the CREATE TRIGGER with comments ("--").

    I changed this first version but didn't realize that the last statement's comments were removed.... Since there's also a DROP TRIGGER and a COMMIT this version dropped my changes and created the version without WHERE -clause in die update.

    I finally wodnered why this ddl creates six rows with "DB20000I The SQL command completed successfully" isntead of the three I expected.

    Now I know that DB2 is Ok and so do I.

    Regards and thanks for listening ;-)))

    Volker.

Posting Permissions

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