oracle - SQL implement complex data validation?

Question

I have a field which will contain a number, how can I do complex validation on that number on insertion?

I've got the validation algorithm implemented in java, just wondering how to "translate it" to SQL. Like I do some calculations with the inserted number and if the calculations don't match a predetermined result I want to reject that entry. How do I go about this?

I'm assuming I need to use a Check Constraint on the field, but how do I incorporate my validation algorithm in the constraint?

My algorithm looks like this

int s=0;
for (int i = 0; i < 10; i++)
    {
       s+=n%10;
       n/=10;
    }
if(n==s)
    //Good Value

Where n is initially my value to be inserted.

Answer 1

Since this check is complex you need to translate it in PL/SQL and add this logic in a BEFORE INSERT and/or a BEFORE UPDATE trigger.

Let's say you table is called MY_TABLE and contain a field MY_FIELD which as a NUMBER datatype :

CREATE OR REPLACE TRIGGER  TRIGGER_VALIDATE_N
BEFORE  
insert or update on MY_TABLE
for each row  
    s NUMBER := 0;
    n NUMBER;
begin  

    n := :OLD.MY_FIELD;

    FOR i IN 0..10 LOOP
        s := MOD(n,10);
        n := n/10;
    END LOOP;

    IF n = s THEN
        --Good Value
        null;
    ELSE
        --Wrong Value raise an error
        RAISE_APPLICATION_ERROR(-20000, TO_CHAR(:OLD.MY_FIELD)||' is a wrong value for MY_TABLE.MY_FIELD.');
    END IF;
end;  
/