It is what I want know, thanks

Tom -

This is likely to be a dumb question, but here goes
anyway. How can I use a simple view definition that
includes a "with check option" clause and also a
"group by" aggregation? My desire is to limit the
sum of a quantity to a certain upper bound,
as shown here:

drop table t1;
create table t1
(name varchar2(10),
sales_commission number);

insert into t1 values ('Tom',12);
insert into t1 values ('Tom',25);
insert into t1 values ('Steve',40);

-- Write a view that collects the sales
-- commission for each of the individuals.
-- The limit is 100, but I cannot get it to work.
drop view v1;
create view v1 as
select name, sum(sales_commission) ssc
from t1
group by name
having sum(sales_commission) <= 100
with check option;

insert into t1 values ('Tom',63);
-- succeeds, as it should because the sum = 100
insert into t1 values ('Steve',63);
-- succeeds, but I'd rather that it didn't...

At this point, the v1 view simply shows the "Tom" name and
that's it. I'd love for the second insert
to violate the constraint on the view, but
I can't get it to go at all.

The dumb part of the question is this: If I
can't use the very elegant and easy "with check
constraint" option on my view, then what approach
would you recommend? I suppose that I could
take advantage of some before row triggers, but I
sense I'm missing something real easy here. Can
you help?

Thanks very much,
Tom G.

Tom -

Gotcha. After thinking about this a while and reading a
very, very applicable past thread of yours (</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:42304816945767 <code>,
this is real easy, as shown below by my new test case
that uses a base table with a primary key:

drop materialized view v1;

-- Recreate my table, being sure to add a column such
-- that a primary key can be defined. The "name" column
-- is not a suitable PK by itself because a single person
-- can have more than one sales commission.
drop table t1;
create table t1
(name varchar2(10),
sales_commission_seq number,
sales_commission number);

alter table t1 add primary key (name, sales_commission_seq);

create sequence my_seq;

insert into t1 values ('Tom',my_seq.nextval,40);
insert into t1 values ('Steve',my_seq.nextval,40);
insert into t1 values ('Tom',my_seq.nextval,40);
insert into t1 values ('Steve',my_seq.nextval,20);

create materialized view log on t1
with primary key including new values;

create materialized view v1
refresh complete on commit as
select name, sum(sales_commission) ssc
from t1
group by name;

alter table v1 add constraint sales_commission_chk
check (ssc <= 100);

-- Now, add a couple of rows, committing each time.
-- The first one will
-- succeed, but the second one should cause the
-- ORA-12008 error.
insert into t1 values ('Tom',my_seq.nextval,10);
commit;
insert into t1 values ('Steve',my_seq.nextval,50);
commit;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
This is all fine and dandy, but is there an easy way to detect
"limit-breaking" inserts into my main table without
having to commit the changes first? I'd love for this
to operate similarly to the way a regular check constraint
on a regular table works.

Thanks so much for your fine work!

Tom

SQL> CREATE TABLE T1 (name varchar2(10),sales_commission NUMBER)
/  2  

Table created.

SQL> CREATE TABLE T1_SUM
  (  NAME VARCHAR2(10),
     SSC  NUMBER,
     SUM_CONS_VALUE NUMBER,
          CONSTRAINT T1_SUM_CHK CHECK (ssc < sum_cons_value) ENABLE
   ) 
/

  2    3    4    5    6    7  
Table created.

SQL> SQL> 
SQL> create or replace trigger t1_trg
before insert or update or delete
        on scott.t1
        referencing new as new old as old
        for each row
begin
   if ( INSERTING )
   then
      merge into t1_sum a
       using (select :new.name name,
                     :new.sales_commission comm
              from dual) b
       on (a.name = b.name)
       when matched then
          update set a.ssc = a.ssc + b.comm
       when not matched then
          insert (name,ssc,sum_cons_value) values (b.name,b.comm,100);
   elsif ( UPDATING )
   then
      update t1_sum a
         set a.ssc = a.ssc - :old.sales_commission
        where a.name = :old.name;

      merge into t1_sum a
      using (select :new.name name,
                    :new.sales_commission comm
              from dual) b
       on (a.name = b.name)
       when matched then
          update set a.ssc = a.ssc + b.comm
       when not matched then
          insert (name,ssc,sum_cons_value) values (b.name,b.comm,100);
   else
       update t1_sum a
          set a.ssc = a.ssc - :old.sales_commission;
   end if;
end;
/

Trigger created.

SQL> 


SQL> insert into t1 values ('Tom',100);
insert into t1 values ('Tom',100)
            *
ERROR at line 1:
ORA-02290: check constraint (SCOTT.T1_SUM_CHK) violated
ORA-06512: at "SCOTT.T1_TRG", line 4
ORA-04088: error during execution of trigger 'SCOTT.T1_TRG'


SQL> insert into t1 values ('Tom',50);

1 row created.

SQL> insert into t1 values ('Tom',35)
  2  /

1 row created.

SQL> insert into t1 values ('Tom',33);
insert into t1 values ('Tom',33)
            *
ERROR at line 1:
ORA-02290: check constraint (SCOTT.T1_SUM_CHK) violated
ORA-06512: at "SCOTT.T1_TRG", line 4
ORA-04088: error during execution of trigger 'SCOTT.T1_TRG'


SQL> insert into t1 values ('Amit',33);

1 row created.

SQL>                
SQL> update t1 set sales_commission =200 where name='Amit';
update t1 set sales_commission =200 where name='Amit'
       *
ERROR at line 1:
ORA-02290: check constraint (SCOTT.T1_SUM_CHK) violated
ORA-06512: at "SCOTT.T1_TRG", line 19
ORA-04088: error during execution of trigger 'SCOTT.T1_TRG'


 

Hi Tom,
Some business requirements can be solved with trigger. But there can be also mutating table problem. Consider this : In hr schema in employees table I do not want any salary to be larger than two times of maximum salary. I can solve this problem with trigger . but I must handle mutating trigger problem with implementing packages. And also I must lock the whole employees table.
Another solution I found is using a updateble view with check option. In my problem like this

create or replace view emp_view
as
select *
from hr.employees
where salary < (select max(salary)
from hr.employees)*2
with check option;

I want to learn which is better in performance, locking mechanism and scalibility. View with check option or Trigger ?
thanks,

EMPNO      SALARY
-------    ----------
1234       1000
5678        500