You Asked
Hi Tom,
I like to keep equal the cumulative values of two columns.
Here is creating query.
======================
create table t2 (dtl_no varchar2(20), amt1 number, amt2 number);
insert into t2 (DTL_NO, AMT1, AMT2)
values ('1', 10, 0);
insert into t2 (DTL_NO, AMT1, AMT2)
values ('1', 10, 10);
insert into t2 (DTL_NO, AMT1, AMT2)
values ('1', 0, 10);
insert into t2 (DTL_NO, AMT1, AMT2)
values ('2', 1, 0);
insert into t2 (DTL_NO, AMT1, AMT2)
values ('2', 1, 0);
insert into t2 (DTL_NO, AMT1, AMT2)
values ('2', 0, 2);
commit;
The question is...
With these values, I am going to get nothing as below.
SQL> select dtl_no from t2 having sum(amt1) <> sum(amt2) group by dtl_no;
DTL_NO
--------------------
Nothing selected. This is OK.
SQL> insert into t2(dtl_no, amt1, amt2) values (3, 0, 1);
1 row inserted
SQL> commit;
Here, I like to raise an error because of integrity sum(amt1) = sum(amt2) (group by dtl_no) is broken.
I have read serveral documents about mutating table and so on. but I can't make this work.
please help me.
Thanks.
and Tom said...
Here is one approach:
ops$tkyte%ORA10GR2> create materialized view log on t2 with rowid (dtl_no,amt1,amt2) including new values;
Materialized view log created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view mv
2 refresh fast on commit
3 as
4 select dtl_no, sum(amt1) sum_amt1, sum(amt2) sum_amt2
5 from t2
6 group by dtl_no
7 /
Materialized view created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table mv add constraint "SUM(amt1) = SUM(amt2)" check (sum_amt1 = sum_amt2 OR (sum_amt1 is null and sum_amt2 is null) );
Table altered.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t2(dtl_no, amt1, amt2) values (3, 0, 1);
1 row created.
ops$tkyte%ORA10GR2> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (OPS$TKYTE.SUM(amt1) = SUM(amt2)) violated
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t2(dtl_no, amt1, amt2) values (3, 0, 1);
1 row created.
ops$tkyte%ORA10GR2> insert into t2(dtl_no, amt1, amt2) values (3, 0, 1);
1 row created.
ops$tkyte%ORA10GR2> insert into t2(dtl_no, amt1, amt2) values (3, 1, 1);
1 row created.
ops$tkyte%ORA10GR2> insert into t2(dtl_no, amt1, amt2) values (3, 2, 0);
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
You do not want to do this in triggers - if you do this in triggers - you have to use the LOCK TABLE COMMAND pretty much (serialize access). That is because reads do not block writes and writes do not block reads. Session 1 cannot see the uncommitted work of Session 2 and Session 2 cannot see Session 1. Hence they will make conflicting changes - each thinking "all is well" but when the last one commits the data is corrupt.
The above will serialize AT THE DTL_NO level (that is, only if two sessions modify the same DTL_NO will they contend with each other) and only at the time of COMMIT - not at the time of modification - so this is highly concurrent and implements the rule.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment