I have details on the mutating table issue at
</code>
http://asktom.oracle.com/~tkyte/Mutate/index.html <code>
It deals with the >1 row being updated/inserted at a time by using plsql tables. You should read through that paper to see why I do some things I do below...
This would be easier if you didn't have that 3 row rule, is it really necessary? (it would be easier from a programming perspective with or without the mutating table issue)...
Here is what I would code in 2 different ways. The first way follows your "3 row" rule. The second is the way I would do it -- i would just track the netsales for the last 3 regardless of whether they had 1, 2, 3, or more records. Its shows how simple the logic is then (regardless of mutating table or not -- the 3 row rule makes this *hard*). If you can, I would give serious consideration to dropping that requirement and just tracking it for everyone. It would make the process of inserting/updating much faster.
ops$tkyte@8i> create table t
2 ( custid int,
3 custname varchar2(25),
4 statement date,
5 netsales int
6 )
7 /
Table created.
ops$tkyte@8i>
ops$tkyte@8i> create table t2
2 ( custid int primary key,
3 netsales_1 int,
4 netsales_2 int,
5 netsales_3 int
6 )
7 /
Table created.
ops$tkyte@8i> create or replace package state_pkg
2 as
3 type myArray is table of number
4 index by binary_integer;
5
6 empty myArray;
7 newOnes myArray;
8 end;
9 /
Package created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_biu
2 before insert or update on t
3 begin
4 state_pkg.newOnes := state_pkg.empty;
5 end;
6 /
Trigger created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_aiuFEF
2 after insert or update on t for each row
3 begin
4 state_pkg.newOnes(:new.custid) := 1;
5 end;
6 /
Trigger created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_aiu
2 after insert or update on t
3 declare
4 l_custid number;
5 l_vals state_pkg.myArray;
6 begin
7 l_custid := state_pkg.newOnes.FIRST;
8 loop
9 l_vals := state_pkg.empty;
10 for x in ( select netsales
11 from t
12 where custid = l_custid
13 order by statement desc )
14 loop
15 l_vals( l_vals.count+1 ) := x.netsales;
16 exit when ( l_vals.count = 3 );
17 end loop;
18 if ( l_vals.count = 3 )
19 then
20 update t2
21 set netsales_1 = l_vals(1),
22 netsales_2 = l_vals(2),
23 netsales_3 = l_vals(3)
24 where custid = l_custid;
25
26 if (sql%rowcount = 0)
27 then
28 insert into t2
29 ( custid, netsales_1,
30 netsales_2, netsales_3 )
31 values
32 ( l_custid, l_vals(1),
33 l_vals(2), l_vals(3) );
34 end if;
35 end if;
36
37 exit when ( l_custid = state_pkg.newOnes.last );
38 l_custid := state_pkg.newOnes.NEXT(l_custid);
39 end loop;
40 state_pkg.newOnes := state_pkg.empty;
41 exception
42 when others then state_pkg.newOnes := state_pkg.empty;
43 end;
44 /
Trigger created.
ops$tkyte@8i>
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-5, 100 );
1 row created.
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-4, 200 );
1 row created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
ops$tkyte@8i> select * from t2;
no rows selected
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-3, 250 );
1 row created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
1 one 05-JUN-00 250
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 250 200 100
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-2, 300 );
1 row created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
1 one 05-JUN-00 250
1 one 06-JUN-00 300
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
ops$tkyte@8i>
ops$tkyte@8i> insert into t
2 select 2, 'two', statement, netsales*10
3 from t
4 /
4 rows created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
2 two 03-JUN-00 1000
1 one 04-JUN-00 200
2 two 04-JUN-00 2000
1 one 05-JUN-00 250
2 two 05-JUN-00 2500
1 one 06-JUN-00 300
2 two 06-JUN-00 3000
8 rows selected.
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
2 3000 2500 2000
ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> insert into t
2 select custid+2, custid+2, statement-10, netsales*10
3 from t
4 /
8 rows created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
3 3 24-MAY-00 1000
4 4 24-MAY-00 10000
3 3 25-MAY-00 2000
4 4 25-MAY-00 20000
3 3 26-MAY-00 2500
4 4 26-MAY-00 25000
3 3 27-MAY-00 3000
4 4 27-MAY-00 30000
1 one 03-JUN-00 100
2 two 03-JUN-00 1000
1 one 04-JUN-00 200
2 two 04-JUN-00 2000
1 one 05-JUN-00 250
2 two 05-JUN-00 2500
1 one 06-JUN-00 300
2 two 06-JUN-00 3000
16 rows selected.
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
2 3000 2500 2000
3 3000 2500 2000
4 30000 25000 20000
ops$tkyte@8i>
ops$tkyte@8i>
Now, here is the simple way I would do it. It'll perform better and have less code to debug/maintainops$tkyte@8i> create table t
2 ( custid int,
3 custname varchar2(25),
4 statement date,
5 netsales int
6 )
7 /
Table created.
ops$tkyte@8i>
ops$tkyte@8i> create table t2
2 ( custid int primary key,
3 netsales_1 int,
4 netsales_2 int,
5 netsales_3 int
6 )
7 /
Table created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_aiuFER
2 after insert or update on t
3 for each row
4 begin
5 update t2
6 set netsales_2 = netsales_1,
7 netsales_3 = netsales_2,
8 netsales_1 = :new.netsales
9 where custid = :new.custid;
10
11 if (sql%rowcount=0)
12 then
13 insert into t2
14 ( custid, netsales_1 )
15 values
16 ( :new.custid, :new.netsales );
17 end if;
18 end;
19 /
Trigger created.
ops$tkyte@8i>
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-5, 100 );
1 row created.
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-4, 200 );
1 row created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 200 100
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-3, 250 );
1 row created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
1 one 05-JUN-00 250
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 250 200 100
ops$tkyte@8i> insert into t values ( 1, 'one', sysdate-2, 300 );
1 row created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
1 one 04-JUN-00 200
1 one 05-JUN-00 250
1 one 06-JUN-00 300
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
ops$tkyte@8i>
ops$tkyte@8i> insert into t
2 select 2, 'two', statement, netsales*10
3 from t
4 /
4 rows created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
1 one 03-JUN-00 100
2 two 03-JUN-00 1000
1 one 04-JUN-00 200
2 two 04-JUN-00 2000
1 one 05-JUN-00 250
2 two 05-JUN-00 2500
1 one 06-JUN-00 300
2 two 06-JUN-00 3000
8 rows selected.
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
2 3000 2500 2000
ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> insert into t
2 select custid+2, custid+2, statement-10, netsales*10
3 from t
4 /
8 rows created.
ops$tkyte@8i> select * from t order by statement;
CUSTID CUSTNAME STATEMENT NETSALES
---------- ------------------------- --------- ----------
3 3 24-MAY-00 1000
4 4 24-MAY-00 10000
3 3 25-MAY-00 2000
4 4 25-MAY-00 20000
3 3 26-MAY-00 2500
4 4 26-MAY-00 25000
3 3 27-MAY-00 3000
4 4 27-MAY-00 30000
1 one 03-JUN-00 100
2 two 03-JUN-00 1000
1 one 04-JUN-00 200
2 two 04-JUN-00 2000
1 one 05-JUN-00 250
2 two 05-JUN-00 2500
1 one 06-JUN-00 300
2 two 06-JUN-00 3000
16 rows selected.
ops$tkyte@8i> select * from t2;
CUSTID NETSALES_1 NETSALES_2 NETSALES_3
---------- ---------- ---------- ----------
1 300 250 200
2 3000 2500 2000
3 3000 2500 2000
4 30000 25000 20000