Re: Constraint problem: number of rows...

Volker Hetzer wrote:

> After having thought about it a bit more it's only 9 out of 10.
> All it takes to shoot down integrity is an update of the current
> pin count which is not done by the trigger. All "normal" integrity
> constraints are such that you can't break them using DML, after all
> DML changes is what one is guarding against.
> Right now the only way I can think of is preventing non-trigger
> updates on the current pin count. Can I do that without a second
> schema?
> Is there a competely different, better way?

A materialized view with a check constraint is somethign you could explore. Here is a very simple example:

SQL> create table master (id int primary key, num_details int);

Table created.

SQL> create table detail (master_id references master, detail_id int);

Table created.

SQL> create materialized view md_mv
  2 refresh complete on commit as
  3 select d.master_id, m.num_details, count(*) act_num_details   4 from master m, detail d
  5 where m.id = d.master_id
  6 group by d.master_id, m.num_details;

Snapshot created.

SQL> alter table md_mv add constraint md_mv_chk check (num_details = act_num_details);

Table altered.

SQL> insert into master values (1,2);

1 row created.

SQL> insert into detail values (1,1);

1 row created.

SQL> insert into detail values (1,2);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into detail values (1,3);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path ORA-02290: check constraint (TANDREWS.MD_MV_CHK) violated

SQL> delete detail where detail_id=2;

1 row deleted.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path ORA-02290: check constraint (TANDREWS.MD_MV_CHK) violated Received on Tue Jun 14 2005 - 06:49:52 CDT