RE: Ref constraint: Numeric to Character

Actually I meant have the physical have a new field, which in your
parlance would be a "derived column". So you already planned on adding a
new column. Just have one trigger on the PF which populates that derived
column.

create table rob.myddstable (
mypfkey char(5));

alter table rob.myddstable
add column myDerivedColumn dec(5, 0);

create trigger myddspftrigger
before insert or update of mypfkey on rob.myddstable
referencing new as new_row
for each row mode db2row
set new_row.myDerivedColumn = cast(new_row.mypfkey as dec(5, 0));

insert into rob.myddstable (mypfkey) values('12345');
select * from myddstable;
....+....1....+....2....
MYPFKEY MYDERIVEDCOLUMN
12345 12,345

update myddstable
set mypfkey='45678' where mypfkey='12345'

select * from myddstable
....+....1....+....2....
MYPFKEY MYDERIVEDCOLUMN
45678 45,678

insert into rob.myddstable (mypfkey) values('1A345')
SQL trigger MYDDSPFTRIGGER in ROB failed with SQLCODE -420 SQLSTATE 22018,
and the message is Character in CAST argument not valid
(note: insert did not happen)




Rob Berendt