I'm with you now. I'm not terribly concerned about getting characters in the key. Authority is locked down and users have to go thru a maintenance app the uses adopted authority. If they somehow manage to get a character in there, I'm OK with the trigger raising an error and halting the write.
-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Tuesday, May 23, 2017 8:06 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: 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
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
midrange.com
