Mutating tables

[cefeo]

Hello,

I'm in trouble with this case:

There is a table named points, with this fields (primary key showed as *):
*product
*initial_date
final_date
points

Where a record means that a product has n points from initial_date to final_date (if final_date is null that means that is the last point assignement for the product).
I need to create a trigger on update points column, so if I change the value, trigger must update also final_date (null to sysdate-1), and create a new record for the same product with sysdate as initial and the new value for points.

I've tried different ways in order to avoid mutating tables, but surely I'm doing something wrong. Can anyone help me? Thanks!

Rod.

 

[sem]

The general approach is to defer "unsafe" changes untill after statement-level trigger fires. You may store updates in some package variable (pl/sql table) or temporary table (of course not connected to one being updated) and then apply them in that trigger. Don't forget to reset some flag to avoid recursion. There's a lot of articles on this account.

Regards, Dima

 

[sygeate]

Hi,

I had this kind of issue. The solution I found was to use a view with a 'Instead of' trigger (available from Oracle 8) on it.

e.g:

--table
create table SY_T1 (
	c1 number,
	c2 varchar2(100)
);
-- view
create view SY_V_T1
as
select * from SY_T1;
--trigger on view
create or replace trigger CV_TRIG_SY_T1_UPD
instead of UPDATE on SY_V_T1
for each row
begin
 -- do what ever you want
 update SY_T1 set c1 = :new.c1, c2 = :new.c2 where c1 = :old.c1; 
 insert into SY_T1 values (:new.c1 * 10, 'new value '||:new.c2);
end;
/

The result is:

-- insert
insert into SY_T1 values (1, 'hello world' );

C1     C2
---    ----
1      hello world

--update
update SY_V_T1 set c2 = 'is there' where c1 = 1;

C1     C2
---    ----
1      is there       
10     new value is there

Regards


Friendly yours, $yg@(h)