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.

Please describe the logic you want, in words, and provide sample data and desired results. Not all SQL folks speak Java fluently. — GMB
– GMB, Commented Dec 30, 2020 at 14:04
@GMB The validation algorithm is irrelevant. I'm wondering how to use an algorithm in a constraint (If that's where I should insert it). Any example (which can't be solved with a simple equation or comparison) would suffice. — Paul
– Paul, Commented Dec 30, 2020 at 14:06
by good value you means if the inserted value lies between say 1 to 10,not null, not negetive etc, it can be done in check constraint, — George Joseph
– George Joseph, Commented Dec 30, 2020 at 14:08

SuperPoney · Accepted Answer · 2021-01-07 10:04:10Z

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;  
/

answered Jan 7, 2021 at 10:04

SuperPoney

6808 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Over a year ago

This is what I ended up doing. Thank you for replying though, really appreciate it !

Collectives™ on Stack Overflow

SQL implement complex data validation?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related