Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 28, 2001 - 3:13 pm UTC

Last updated: March 28, 2001 - 3:13 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

I have two tables: table1 and table2, and i want to create a trigger

like:
create or replace trigger trig_1
after insert on table1
....
update table2 set col1 = ....(based on sum(col2 of table1))
where key_condition
....
Within this trigger i want to update table2. This update is based on
column1 in table1(basically on summation of column1 for table1), if i do "select sum(col1) from table1" ---- they must be mutating.

BTW, there is not foreign key relationship and the col1 in table2, col2 in table1 are not keys in both tables.

Can i solve that by:

0). create package pck1 as
s1 NUMBER;
key1 VARCHAR2(3);
first_date DATE;
....;

1). create or replace t_1
before insert on table1 for each row
pck1.key1 := :old.key1;
...;

2). create or replace t_1
before insert on table1
pck1.s1 := select sum(col1) from table1 where key1 = pck1.key1;
pck1.first_date := select min(col_date) from table1 where key1 = pck1.key1;
...;

3). create or replace t_1
after insert on table1 for each row
update table2 set col1 = pck1.s1 + :new.col1,
col_date = pck1.first_date
where key1 = pck1.key1;
....

Is that a possible solution or are there other more efficient solution?

Thanks

and Tom said...

Might be time for a materialized view:

ops$tkyte@ORA8I.WORLD> create table t1 ( a int,
2 b int,
3 y number,
4 primary key(a,b) )
5 /

Table created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> create materialized view log on t1
2 with rowid ( a, y ) including new values
3 /

Materialized view log created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> create materialized view t2
2 build immediate
3 refresh on commit
4 as
5 select a, count(*), count(y), sum(y) from t1 group by a
6 /

Materialized view created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> begin
2 for i in 1 .. 10
3 loop
4 for j in 1 .. 10
5 loop
6 insert into t1 values ( i, j, dbms_random.random );
7 end loop;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> commit;

Commit complete.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select * from t2;

A COUNT(*) COUNT(Y) SUM(Y)
---------- ---------- ---------- ----------
1 10 10 -4.236E+09
2 10 10 -7.931E+09
3 10 10 2307803256
4 10 10 1408579220
5 10 10 -1.380E+09
6 10 10 -1.474E+09
7 10 10 -2.940E+09
8 10 10 -1.749E+09
9 10 10 4978605153
10 10 10 -67988546

10 rows selected.

ops$tkyte@ORA8I.WORLD> select a, sum(y) from t1 group by a;

A SUM(Y)
---------- ----------
1 -4.236E+09
2 -7.931E+09
3 2307803256
4 1408579220
5 -1.380E+09
6 -1.474E+09
7 -2.940E+09
8 -1.749E+09
9 4978605153
10 -67988546

10 rows selected.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> update t1 set y = -y;

100 rows updated.

ops$tkyte@ORA8I.WORLD> commit;

Commit complete.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select * from t2;

A COUNT(*) COUNT(Y) SUM(Y)
---------- ---------- ---------- ----------
1 10 10 4235894392
2 10 10 7930633525
3 10 10 -2.308E+09
4 10 10 -1.409E+09
5 10 10 1380031060
6 10 10 1474167088
7 10 10 2940467473
8 10 10 1749008288
9 10 10 -4.979E+09
10 10 10 67988546

10 rows selected.

ops$tkyte@ORA8I.WORLD> select a, sum(y) from t1 group by a;

A SUM(Y)
---------- ----------
1 4235894392
2 7930633525
3 -2.308E+09
4 -1.409E+09
5 1380031060
6 1474167088
7 2940467473
8 1749008288
9 -4.979E+09
10 67988546

10 rows selected.

ops$tkyte@ORA8I.WORLD>


If that doesn't work for you, see

</code> http://asktom.oracle.com/~tkyte/Mutate/index.html <code>

for the cookbook on how you can avoid the mutant tables



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library