Hi, I have the following table:
create table test(sumdgt number(5));
In the above table, I should not enter the row whose total comes to 8.
It should accept,
insert into test values(21); insert into test values(11);It should not accept,
insert into test values(71); (7+1=8) insert into test values(17000); (1+7+0+0+0)=8 insert into test values(26); (2+6=8)
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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.