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.

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.