pl sql: trigger for insert data from another table

Question

There is the table OLD and a similar one, NEW. I want to insert in the existing process that fills the table OLD a trigger event that for each new inserted row, this event will insert the newly inserted row to table NEW, as well. Inside the body of trigger, i need to include the query BELOW which aggregates values of OLD before inserted in NEW:

insert into NEW  
select (select a.id,a.name,a.address,b.jitter,a.packet,a.compo,b.rtd,a.dur from OLD a,
        select address,packet,compo, avg(jitter) as jitter, avg(rtd) as rtd from OLD
        group by address,packet,compo ) b
        where a.address=b.address and a.packet=b.packet and a.compo=b.compo;

can you correct any possible mistakes or suggest other trigger syntax on the statement below?

CREATE OR REPLACE TRIGGER insertion after update on OLD for each row begin MY select query above end;

Rene Rene · Accepted Answer · 2011-02-10T10:19:21

In a for each row trigger you cannot query the table itself. You will get a mutating table error message if you do. I recommend only to use triggers for the most basic functionality such as handing out ID numbers and very basic checks. If you do use triggers for more complex tasks you may very easily end up with a system that's very hard to debug and maintain because of all kinds of actions that appear out of knowhere.

Look at this question for another approach: getting rid of Insert trigger

pl sql: trigger for insert data from another table

2 Answers