Help enforcing constraints
713267Oct 2 2009 — edited Oct 2 2009I have the following challenge in front of me:
create table company
(
companyid number(10),
code varchar2(50),
descr varchar2(100),
companylevel number(10),
parentcode varchar2(50)
)
Example of data:
insert into company values (1, 'A', 'A parent', 10, null);
insert into company values (2, 'B', 'B parent', 10, null);
insert into company values (3, 'C', 'C child', 0, 'A');
insert into company values (4, 'D', 'D child', 0, 'B');
insert into company values (5, 'E', 'E parent', 10, null);
companylevel is usually 0 or 10
0 is child company
10 is a parent company
Rules that I need to apply:*
1) code and parentcode should be unique
*2) We should not allow an insert of a code with companylevel of 0 if that code already exists as a companylevel 10*
*3) We should not allow an insert of a code with companylevel of 10 if that code already exists as a companylevel 0*
As example, we should NOT allow the following inserts:
insert into company values (6, 'B', 'B child', 0, 'A');
insert into company values (7, 'C', 'C parent', 10, null);
But we should allow:
insert into company values (8, 'C', 'C child', 0, 'B');
insert into company values (9, 'D', 'D child', 0, 'A');
I need to work with what I have. I cannot modify the table.
I have tried to create a trigger but was not very successful at it. I get the famous MUTATING problem so I was wondering if I can create a constraint for Rules 2 and 3.
I am open for any suggestions.
Thanks a bunch!