|
The only way to handle this advanced type of error checking (or business
rules) is to use a trigger. This trigger must then parse the number being
inserted, character by character, and sum up the individual digits. Such a
trigger might look like:
CREATE OR REPLACE TRIGGER test_no_8
BEFORE INSERT ON test
FOR EACH ROW
DECLARE
i NUMBER;
sum NUMBER;
len NUMBER;
BEGIN
/* set sum of digits to zero */
sum:=0;
/* get length of string to be inserted */
len:=LENGTH(:new.column_1);
/* loop through string. Add individual digits */
/* to the sum. */
FOR i IN 1..len LOOP
sum:=sum+SUBSTR(:new.column_1,i,1);
END LOOP;
/* If sum is 8, then raise an error. */
/* otherwise, do nothing */
IF sum=8 THEN
RAISE insert_error;
EXCEPTION
WHEN insert_error THEN
RAISE_APPLICATION_ERROR(-20001,'String digits equals 8');
END;
You should test this code as I just wrote it off the top of my head. I have
not tested it myself, but it should work.
For More Information
|
