RE: Ref constraint: Numeric to Character

Perhaps the reply was poorly written but it was asking if you could put a
check constraint on the character field to ensure it was digits before it
fired the trigger.
For example, can you ensure that mypfkey only contained valid digits
before it fired off the trigger to populate myDerivedColumn.
In that case, a good question. You can try to create such a constraint

alter table rob.myddstable
add constraint ValidateDigits check(
mypfkey = digits(cast (mypfkey as decimal(5, 0))) )

alter trigger myddspftrigger disable

insert into rob.myddstable (mypfkey) values('1A345')
INSERT, UPDATE, or MERGE not allowed by CHECK constraint.
SQL0545

But if you leave the trigger active
alter trigger myddspftrigger enable
You'll find that it tries the trigger first, before the constraint.

SQL0723
Message . . . . : SQL trigger MYDDSPFTRIGGER in ROB failed with SQLCODE
-420
SQLSTATE 22018.
Cause . . . . . : An error has occurred in a triggered SQL statement in

trigger MYDDSPFTRIGGER in schema ROB. The SQLCODE is -420, the SQLSTATE
is
22018, and the message is Character in CAST argument not valid..

This may sound stupid in our example, but maybe someone else would cleanse
the data in the trigger and then want to verify it with the check
constraint. So, let's roll with that.

So you would have to put the appropriate checking in your trigger. You
can decide on whether to stay in pure SQL or write your trigger in RPG
based on your willingness to learn, time, which works, and so on.
You'll have to make some decisions. Like,

do you flag an error such as this example
...
WHEN (NEW_EMP.SALARY > (OLD_EMP.SALARY *1.20))
BEGIN ATOMIC
SIGNAL SQLSTATE '75001'('Invalid Salary Increase - Exceeds 20%');
END

Do you set the value for myDerivedColumn to null?

Do you email or otherwise alert someone to check on this?


Rob Berendt